Date query issue

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

I have a query that I need to pull the needs for a client for a specific date:

strSQL = "Select * From Needs " & _
"WHERE (((Needs.client_id)= " & dblClient_ID & ") And
((Needs.due_date)= #" & dtCalc_Date & "#)) ;"

The query is pulling all records for the client and seemingly ignoring the
date parameter. The date is in mm/dd/yyyy format.

This is what is visible in the object viewer for strSQL:
Select * From Needs WHERE (((Needs.client_id)= 2) And ((Needs.due_date)=
#2/1/2009#));
 
The string seems to be ok at first glance (you are using the # for comparing
a date), but I did notice the existance of the semicolon in your VBA version,
which I believe should be omitted.


So instead of
strSQL = "Select * From Needs " & _
"WHERE (((Needs.client_id)= " & dblClient_ID & ") And
((Needs.due_date)= #" & dtCalc_Date & "#)) ;"
Click to expand...

try
strSQL = "Select * From Needs " & _
"WHERE (((Needs.client_id)= " & dblClient_ID & ") And
((Needs.due_date)= #" & dtCalc_Date & "#))"

I'm not sure if that's the issue or not, but I thought I'd point it out.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
I have a query that I need to pull the needs for a client for a specific date:

strSQL = "Select * From Needs " & _
"WHERE (((Needs.client_id)= " & dblClient_ID & ") And
((Needs.due_date)= #" & dtCalc_Date & "#)) ;"

The query is pulling all records for the client and seemingly ignoring the
date parameter. The date is in mm/dd/yyyy format.

This is what is visible in the object viewer for strSQL:
Select * From Needs WHERE (((Needs.client_id)= 2) And ((Needs.due_date)=
#2/1/2009#));
Click to expand...

1) Try getting rid of the parenthesis. You don't need them here.
Sometimes they obscure the syntax.
strSQL = "Select * From Needs " & _
"WHERE client_id = " & dblClient_ID & " And
due_date = #" & dtCalc_Date & "#;"

2) Most likely however, is that your dtCalc_Date field includes a time
value.
2/1/2009 midnight is not the same as 2/1/2009 3:00 PM

See if you get records using:

..... " And due_date >= #" & dtCalc_Date & "# And due_Date < # " &
dtCalc_Date & " # + 1;"
 
Thanks. The issue isn't that I'm not getting any records, it appears as
though the date portion of the where clause is getting ignored because I am
getting all records for the client.
 
strSQL = "Select * From Needs " & _
" WHERE Needs.client_id= " & dblClient_ID & _
" And Needs.due_date= #" & dtCalc_Date & "#"
'Add the following to check the actual statement being generated.
Debug.Print StrSQL
'Add the following to check the values of dblClient_ID/dtCalc_Date
Debug.Print dblClient_ID, dtCalc_Date

Something is going on, since that should be returing one client on one date.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
The variables are displaying the correct values.

After running several more tests I found that the recordset.RecordCount is
correctly returning 0 for days where there are no records for the
client/date. However, if there is 1 or more records for the client/date the
recordset.RecordCount is returning the total count of all records for the
client in the needs table. The interesting thing is there really are only
the records for that client/date so when I do a recordset.MoveNext on a
client/date combination that I know only has 1 record for that date I get an
EOF condition. While this doesn’t present a logic problem because I’m
really only interested to know if anything for that client/date exists, it
does cause a significant performance issue. The query is inside a loop for
each day of the month and for large clients with “needs†on more than 1 or 2
days the performance is terrible.

If this is typical behavior for queries I may have to rework my logic and
put the query outside the loop and do the checks for each date while looping
through the recordset.


I am curious as to why the recordcount is returning a number different from
what the query is actually returning. (Access 2003)
 
Back
Top