SQL string problem - pleaze hellp!

[plh]

The following actually creates a query but with nothing in it, even in cases
where I am sure there are patients with "DDate" in the moth specified by my
attempted restriction. That is strSQL ends up as:
SELECT * FROM tblPatients WHERE DDate >= 4/1/2005 AND DDate <= 4/30/2005;
It's Access97. Any help would be greatly appreciated!
-Paul H.


Select Case Me.cbxMonth.Value
Case 1, 3, 5, 7, 8, 10, 12
intLastDay = 31
Case 4, 6, 9, 11
intLastDay = 30
Case 2
If Me.txtYear.Value Mod 4 <> 0 Then
intLastDay = 28
ElseIf Me.txtYear.Value Mod 400 = 0 Then
intLastDay = 29
ElseIf Me.txtYear.Value Mod 100 = 0 Then
intLastDay = 28
End If
End Select

strSQL = "SELECT * FROM tblPatients WHERE DDate >= " & Me.cbxMonth.Value & "/1/"
& Me.txtYear.Value & _
" AND DDate <= " & Me.cbxMonth.Value & "/" & intLastDay & "/" & Me.txtYear.Value
& ";"

Set db = CurrentDb
With db
Set qdf = .CreateQueryDef("qryTest", strSQL)
End With

 

[Ken Snell [MVP]]

Delimit the concatenated date values from your form's controls using the #
delimiter:

strSQL = "SELECT * FROM tblPatients WHERE DDate >= #" & Me.cbxMonth.Value &
"/1/"
& Me.txtYear.Value & _
"# AND DDate <= #" & Me.cbxMonth.Value & "/" & intLastDay & "/" &
Me.txtYear.Value
& "#;"

 

[Van T. Dinh]

You don't need the Select Case statement at all. Try (without the Select
Case statement):

strSQL = "SELECT * FROM tblPatients WHERE ( DDate >= DateSerial(" & _
Me.txtYear.Value & ", " & Me.cbxMonth.Value & ",1) ) " & _
" AND ( DDate < DateSerial(" & _
Me.txtYear.Value & ", " & Me.cbxMonth.Value & " + 1, 1) )"

 

[Douglas J. Steele]

In addition to what Ken and Van have told you, be aware that if DDate
contains a time (such as it will if you use the Now function to populate
it), your query will NOT retrieve records for where the date component of
DDate is the last day of the month. This is because dates are actually
stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. Yesterday (30
April, 2005) would be represented as 38472. If the value for DDate is 8:00
AM on 30 April, 2005, it will actually be 38472.33333 (since 8:00 AM is one
third of the way through the day). Since 38472.33333 is not less than or
equal to 38472, it won't be retrieved.

For this reason, you may want to retrieve where DDate is >= the first day of
the month and DDate is less than the first day of the following month.

 

[plh]

That seems to work splendidly, Thank You!
-plh

You don't need the Select Case statement at all. Try (without the Select
Case statement):

strSQL = "SELECT * FROM tblPatients WHERE ( DDate >= DateSerial(" & _
Me.txtYear.Value & ", " & Me.cbxMonth.Value & ",1) ) " & _
" AND ( DDate < DateSerial(" & _
Me.txtYear.Value & ", " & Me.cbxMonth.Value & " + 1, 1) )"