Date Parameter - No Records Returned

[hoppergs]

I have a report which is based upon a series of user selections in a form, the last of which is a listbox containing dates. I have a RUN REPORT button which takes all criteria into account (builds up a WHERE string) and then uses docmd.OpenReport and the WHERE string to display the report with the appropriate records.

The problem I'm having is that any time a date or dates are selected, the report returns nothing, despite there being records that match the criteria. After stepping through the VBA, the WHERE condition for the dates is something like this : "qryPerformance.dtmPerf = #15/04/03#" which to me looks correct.

Does anyone have any ideas what I can do to resolve this? I've tried various different syntaxes but the one above seemed to be accepted ok.

Thanks a lot.

Graham

 

[PaulBricker]

You say 'date or dates'. That could be part of the problem. If you have multiple dates then your Where clause construction has to be done differently than with a single date. Something like this should work

Where tblName.FName = "John" and tblName.myDate = #15/04/03#

Something like this won't

Where tblName.FName = "John" and tblName.myDate = #15/04/03# Or tblName.myDate = #22/04/03#

Something like this should

Where tblName.FName = "John" and tblName.myDate = #15/04/03# Or tblName.FName = "John" and tblName.myDate = #22/04/03#

It would probably help to see your Where clause and some examples of what doesn't work.

Paul

 

[hoppergs]

Thanks for your response Paul.

The overall WHERE is too large to put in here, but I've included the date part of it below. Basically I use 2 WHERE statements and concatenate them at the end. The first one is pretty big and is along the lines of:

"WHERE (tblTest = "x" and tblTest2="y&quot OR (tblTest = "r" and tblTest2="t&quot" and so on.

The date WHERE is below:

'if no dates are selected, return all entries
If ctlDate.ItemsSelected.Count = 0 Then
strDateWHERE = ""
'if only one date exists, set the WHERE command, otherwise loop through selection
ElseIf ctlDate.ItemsSelected.Count = 1 Then
For Each varDate In ctlDate.ItemsSelected
strDateWHERE = "qryPerformance.dtmPerfDate = " & "#" & Forms!frmPerformanceReport.lstPerfDate.ItemData(varDate) & "#"
Next varDate
Else
'build start of WHERE command and then append more as loop goes through all selected competitors
For Each varDate In ctlDate.ItemsSelected
If strDateWHERE = "" Then 'make start of statement if null
strDateWHERE = "qryPerformance.dtmPerfDate = " & "#" & Forms!frmPerformanceReport.lstPerfDate.ItemData(varDate) & "#"
Else 'otherwise append a series of "Ors" to it
strDateWHERE = strDateWHERE & " OR " & "qryPerformance.dtmPerfDate = " & "#" & ctlDate.ItemData(varDate) & "#"
End If
Next varDate
End If

Does this help to make it clearer at all??

 

[danvlas]

Just a thought...You're passing the date in dd/mm/yy format, which will always be wrongly evaluated.
You have to pass it in mm/dd/yy format, or even better, dd-mmm-yyyy (medium date).

For example:
#10/02/03# (10 February 2003) will be evaluated as October 02, 2003.

And this may be the reason for getting no rows...



HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PaulBricker]

I would assume that would depend on the International settings in their Control Panel. It should work just the way you have it (I'm assuming you are not in the continental U.S. and your setting are dd/mm/yyyy). Have you tried using the Where statement with just the date portion to see if it returns records by itself? That will tell you something. I don't see any problems with what you have. I did run it thru one of my test db's and it returned records just the way it was. Also, you could try appending your Where statement to an existing query to see if you can shake something out of the trees that way. I'm still guessing that it is a problem when used in combination with other where statements.

Paul

 

[hoppergs]

I tried isolating the date WHERE and it returns nothing. The date format thing is something I considered but I'm passing a value into the query in the same format as the records it's looking up to, so surely they would match, even if they were wrong?

Also, I'm not sure how I'd go about telling the query to use another date format - would this need to be done on the input mask of the field itself?

Thanks again

Graham

 

[danvlas]

Paul, when passing a date through VB...you MUST use US format.

Make the following experiment:
In the query grid, conditions row, enter a date in the format dd-mm-yyyy. Then switch to SQL view. Surprise!!! Your date has been converted to mm-dd-yyyy. Why? Ask Microsoft.

Graham, trust me...you have to pass it as Format(YourDate,"mm/dd/yyyy&quot

or as a medium date:
Format(YourDate, "dd-mmm-yyyy&quot

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PaulBricker]

I haven't tried it Dan but will take your word for it. I had no idea that VB required it to be in mm/dd/yy type format. Sorry for the confusion.

Paul

 

[hoppergs]

Had a crack at using this logic, but my syntax is wrong somewhere along the line - this is an example of what I'm using:

strDateWHERE = "qryPerformance.dtmPerfDate = " & "#" & Format("Forms!frmPerformanceReport.lstPerfDate.ItemData(varDate)", "mm/dd/yy&quot & "#"

To be honest I didn't think it looked right from the start but just thought I'd try anyway! I'll keep trying other variations but any pointers would be appreciated.

Thanks again.

Graham

 

[danvlas]

Lose some quotes and the ItemData thing. Itemdata is used if your listbox allows multiple selection and building the filter that way would be different:

strDateWHERE = "qryPerformance.dtmPerfDate = # & Format(Forms!frmPerformanceReport!lstPerfDate, "mm/dd/yy&quot & "#"

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[hoppergs]

Got it to work! Thanks to everyone who helped me with this.

Graham