Re: reg SQL query

argusy wrote:
Ramkumar wrote:
> Ya i tried with the Mike's coding.
> But it's giving error " SUB OR FUNCTION NOT DEFINED " and it shows error near to monthName().
> I tried with that coding before posting this problem.
> that's why i posted this problem again.
> I AM USING VISUAL BASIC 5.0 TO MY PROJECT.
> IT WON'T SUPPORT MonthName() function.
> can u give some idea reg my problem.
>
>
> Thanks and Regards,
>
> Ram
>
>



Sorry, my post wasn't that clear, when I read it back to myself

What I was really trying to do, was to get you to take a look at Mike's reasoning (and code) to use IN THE QUERY, and also his tips for error trapping.

as for monthname(), sure it's VB6, so what's wrong with going back to your VB5 code to populate combo1?

Graham


>> Ramkumar wrote:
>>> Hi
>>>
>>> During the FORM LOAD i'm loading the months and the current year in the combobox and text box respectively. The coding for month and year is given below.
>>>
>>> For MONTH:
>>>
>>> Dim i As Integer
>>> For i = 1 To 12
>>> combo1.AddItem Format(DateSerial(Year(Date), i, 1), "MMMM")
>>> Next
>>>
>>> For YEAR:
>>>
>>> Dim i As Integer
>>> Dim currentYear As Integer
>>> currentYear = Year(Now)
>>> txtYear.Text = currentYear
>>>
>>> I need to connect the both to an SQL query.
>>>
>>> I connected the YEAR to SQL query as
>>>
>>> curYear = txtYear.text
>>>
>>> But i don't know to connect the month with the SQL query.
>>>
>>> The SQL query is given below
>>>
>>> SELECT CONVERT(CHAR(25),TravelDate,103) as [Journey Date],EID
>>> ,Surname,Forename,' ' + RTRIM(FromAd) AS [From Adr], FromPostCode, ' '+
>>> RTRIM(ToAd) AS [To Adr],ToPostCode FROM Journey
>>> WHERE (MONTH(Journey.TravelDate) = " ? ")
>>> AND (YEAR(Journey.TravelDate) = " & curYear & ")
>>> AND (MileageInward IS Null OR MileageInward=0 OR
>>> (ReturnJny=1 AND (MileageOutward IS Null OR MileageOutward=0)))
>>> ORDER BY TravelDate, CallSignIn, AppTime, FromPostCode
>>>
>>> I AM USING VISUAL BASIC 5.0. SO ANYONE CAN HELP TO GET RID OF THIS PROBLEM....
>>> Reply ASAP.
>>> Thanks in advance.
>>>
>>> Regards,
>>>
>>> Ram
>>
>> Try reading MikeD's response to your previous post - again
>>
>> Even if the combobox is different between VB5 and VB6, (and I'm pretty sure they're the same) at least try his code before posting another request for assistance.
>>
>> At the least, you should see where you _should_ be going with your query
>>
>> Argusy

So you're saying the following doesn't work?

' the next is in formLoad

Dim i As Integer
For i = 1 To 12
combo1.AddItem Format(DateSerial(Year(Date), i, 1), "MMMM")
Next

' some code here, I guess

Dim curYear As Integer ' taking out one variable here
txtYear.Text = Year(now)
curYear = Year(now)

'End of formLoad

' following code is in another sub-routine

SELECT CONVERT(CHAR(25),TravelDate,103) as [Journey Date],EID
,Surname,Forename,' ' + RTRIM(FromAd) AS [From Adr], FromPostCode, ' '+
RTRIM(ToAd) AS [To Adr],ToPostCode FROM Journey
WHERE (MONTH(Journey.TravelDate) = " & Combo1.ItemData(Combo1.ListIndex) & ")"
AND (YEAR(Journey.TravelDate) = " & curYear & ")
AND (MileageInward IS Null OR MileageInward=0 OR
(ReturnJny=1 AND (MileageOutward IS Null OR MileageOutward=0)))
ORDER BY TravelDate, CallSignIn, AppTime, FromPostCode



Note the WHERE statement. As Mike pointed out, you could set a variable in one of the combo1's events eg
curMonth = Combo1.ItemData(Combo1.ListIndex) & ")"

' curMonth defined as a global or form variable, so it can be used in the query

and in the SQL query

.......
WHERE (MONTH(Journey.TravelDate) = " & curMonth & ")"
.......

BTW there's no mention of MonthName(), so I don't see your argument that it's affecting your code


Graham



.

Relevant Pages

  • Re: reg SQL query
    ... > I tried with that coding before posting this problem. ... >>> Dim i As Integer ... >>> I need to connect the both to an SQL query. ... >>> Ram ...
    (microsoft.public.vb.controls)
  • RE: NOVICE: SQL Query on a Timer, Growing Memory Size
    ... achieved by a sql query running in timed increments, ... Private Sub Timer1_Tick(ByVal sender As System.Object, ... Dim adapter3 As New SqlDataAdapter ... Dim ds3 As New Data.DataSet ...
    (microsoft.public.dotnet.languages.vb)
  • NOVICE: SQL Query on a Timer, Growing Memory Size
    ... achieved by a sql query running in timed increments, ... Private Sub Timer1_Tick(ByVal sender As System.Object, ... Dim adapter3 As New SqlDataAdapter ... Dim ds3 As New Data.DataSet ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Retrieving the SQL query of the external data range
    ... Sub Query_Info ... Dim qryTable As QueryTable ... Dim wks As Worksheet ... > given to me to work though the associated SQL query remotely. ...
    (microsoft.public.excel.programming)
  • Re: export a query to excel, but seperate fields by pages
    ... I used the sql query you had in the original post and it gave me a syntax ... >> 'Assemble the SQL query to export one agency ... >> Dim rsAgencies As DAO.Recordset ...
    (microsoft.public.access.externaldata)