**Query by Form Date Range**

[John M]

Hi All,

I am using a query form to get the criteria to run my report. I want the report for a certain date. My syntax is :
DoCmd.OpenReport "rptIssue", acViewPreview, , "[Date_ Raised] = " & Forms![IssueReportSearch]![Text5]

where [Text5] is the unbounded field to hold the date.

I find that it does not retrieve the information that I wanted. Could IT BE A DATE formating issue. It works for the literials i.e. DoCmd.OpenReport "rptIssue", acViewPreview, , "[Date_ Raised] = #1/18/2002#"

What am I doing wrong?

 

[DBL]

Why not put the criteria in the Date field in the query that the report is based on rather than use the SQL?

In the criteria field for the Date reference back to the form

Forms![FormName]![DateFieldName]

 

[John M]

Thanks.

That way my query is tied to the report. I want to make the query more flexible.

 

[DBL]

I'm in the UK and had a problem when I was trying to print a report based on a date range, it didn't recognise the UK date format, even though that's what the Regional Settings are are. Don't know if this is the same problem that you are getting. If it is, I can post how I got round it.

 

[John M]

Hi DBL,

In Hong Kong we use the same date format as the UK. I may have the same problem too. Would love to see you post your fix here!! Thanks.

 

[DBL]

I'll give you exactly what I have, courtesy of the great and good Mr Jack P Cowley, and you can adapt it from there. It came it two parts

In a module:

Function MakeUSDate(X As Variant)
On Error GoTo Errorhandler

If Not IsDate(X) Then Exit Function
MakeUSDate = "#" & Month(X) & "/" & Day(X) & "/" & Year(X) & "#"

errorhandlerexit:
Exit Function

Errorhandler:
MsgBox Err.Description
Resume errorhandlerexit

End Function

On the Print button of the form:

F = MakeUSDate([DateFrom])
T = MakeUSDate([DateTo])

DoCmd.OpenReport stDocName, acViewPreview, , "[WDate]" & " Between " & F & " AND " & T & ""


My code was to print reports within a date range. I'm sure you can adapt it to your needs. I think it's all there. Shout if you get stuck

 

[Rich]

DoCmd.OpenReport "rptIssue", acViewPreview, , "[Date_ Raised] = " & Format(Forms![IssueReportSearch]![Text5],"0")