variable query statement to query database

[Bebe]

I can't get this code to query the database with the results from this code i get select from Master where Date_Opened between #01/12/99 and #xx/xx/xx; numbers dependent on what you select in cbo box

Please help!!!
Private Sub Command1_Click()
Dim strStartDate As String
Dim strStartDateMonth As String
Dim strEndDate As String
Dim sql As String
Dim A As String

If cboMonth = "January" Then
cboMonth = "1"

If cboMonth = "February" Then
cboMonth = "2"

If cboMonth = "March" Then
cboMonth = "3"

If cboMonth = "April" Then
cboMonth = "4"

If cboMonth = "May" Then
cboMonth = "5"

If cboMonth = "June" Then
cboMonth = "6"

If cboMonth = "July" Then
cboMonth = "7"

If cboMonth = "August" Then
cboMonth = "8"

If cboMonth = "September" Then
cboMonth = "9"

If cboMonth = "October" Then
cboMonth = "10"

If cboMonth = "November" Then
cboMonth = "11"

If cboMonth = "December" Then
cboMonth = "12"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

A = Mid(cboYear.Text, 3, 2) 'get 94 from 1994
strStartDate = Format("10/4/94", "dd/mm/yy")
strEndDate = Format("01 " & cboMonth & " " & cboYear, "dd/mm/yy")
If CDate(strStartDate) < CDate(strEndDate) Then
' txtDate.Text = "Select * " & "From Master " & "WHERE DATE_OPENED BETWEEN '" & strStartDate & "' and '" & strEndDate
'Data1.RecordSource = "select from Master where DATE_OPENED '" & strStartDate & "' and '" & strEndDate
'Data1.RecordSource = txtDate.Text
' Data1.RecordSource = "SELECT (*) from MASTER WHERE DATE_OPENED BETWEEN '" & strStartDate & "' and '" & strEndDate
'txtDate.Text = sql
'Data1.RecordSource = sql
'Data1.Recordset.MoveFirst
'Else
' txtDate.Text = "Records do not go back to: " & strEndDate
'End If

'If Data1.Recordset.NoMatch Then
'MsgBox "No such Auto Response Number in the PSR Database", vbOKOnly, "PSR Record Not Found"
Data1.Refresh
Data1.RecordSource = "SELECT from Master where DATE_OPENED between '" & "#" & strStartDate & "#" & "' and '" & "#" & strEndDate & "#"
Data1.Recordset.MoveNext

' If Data1.Recordset.NoMatch Then
' MsgBox "No such Auto Response Number in the PSR Database", vbOKOnly, "PSR Record Not Found"
Form2.MSFlexGrid1 = Data1.RecordSource
'Form3.Show
End If
End Sub

[Ianpbaker]

Hi bebe Ive looked through you code and it looks fine apart from one thing. Where you set the data1 record set you have got

Data1.RecordSource = "select from Master where DATE_OPENED '" & strStartDate & "' and '" & strEndDate
'Data1.RecordSource = txtDate.Text

I think what you actually want is

sql = "SELECT * FROM Master where DATE_OPENED BETWEEN '" & strStartDate & "' and '" & strEndDate & "'"

Data1.recordsource = sql

It looks like you missed out the closing pip at the end of strEnd date. Also if you are using access try replacing The ' with a #

Hope this helps

Ian

[Edited by Ianpbaker on 04-20-2000 at 09:26 AM]

[Bebe]

Im getting true and false responses in my grid and when I use a msgbox I get the same

[Elias]

Not sure about everything else, but, you might want to try this instead of your long if statement.

private enum eMonth
January = 1
February = 2
March = 3
April = 4
May = 5
June = 6
July = 7
August = 8
September = 9
October = 10
November = 11
December = 12
end enum

private xMonth as eMonth

xMonth = May

Now, if you said debug.print xMonth, you would get 3.

Hope that helps some.

[Glenn]

Are you trying to figure out the last day of a particular month ?

If so, determine the first day of the next month, subtract 1 day, which will get you to the last day of the previous month.

[Bebe]

I'm totally confused do you have some example code?

Well isn't it always February ends on the 29th, etc the end of the months are set; right?

[Glenn]

Try this

dim FirstDayOfMonth as date
dim LastDayofMonth as date

FirstDayOfMonth = "3/1/2000"   ' 1st day of the next month
LastDayofMonth = DateAdd("d", -1, FirstDayOfMonth)

The date add function allows you to add (or subtract) an interval from the specified day.

I tried this from March 1st of this year and it correctly came up with 2/29/2000