SQL string problem - pleaze hellp!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

plh - 01 May 2005 03:08 GMT

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

Signature

I keep hitting "Esc" -- but I'm still here!

Reply to this Message

Ken Snell [MVP] - 01 May 2005 03:47 GMT

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
& "#;"

Signature

Ken Snell
<MS ACCESS MVP>

> The following actually creates a query but with nothing in it, even in
> cases
[quoted text clipped - 31 lines]
> Set qdf = .CreateQueryDef("qryTest", strSQL)
> End With

Reply to this Message

Van T. Dinh - 01 May 2005 09:22 GMT

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) )"

Signature

HTH
Van T. Dinh
MVP (Access)

> 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
[quoted text clipped - 27 lines]
> Set qdf = .CreateQueryDef("qryTest", strSQL)
> End With

Reply to this Message

plh - 04 May 2005 02:13 GMT

That seems to work splendidly, Thank You!
-plh

>You don't need the Select Case statement at all. Try (without the Select
>Case statement):
[quoted text clipped - 39 lines]
>> Set qdf = .CreateQueryDef("qryTest", strSQL)
>> End With

Signature

I keep hitting "Esc" -- but I'm still here!

Reply to this Message

Douglas J. Steele - 01 May 2005 12:30 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The following actually creates a query but with nothing in it, even in
> cases
[quoted text clipped - 31 lines]
> Set qdf = .CreateQueryDef("qryTest", strSQL)
> End With

Reply to this Message