Using Various form criteria to Generate Reports

[simeybt]

I know this might have been asked a hundred times in this forum, and I must have looked at a fair few of them posts today, but I just can get this to work. Here is the problem. I have a report that I want to generate depending on the values the user has selected on a form. So far I’ve tried to pass the criteria in as a where clause with the

 DoCmd.OpenReport "rptFormAnnualLeave", acViewPreview, "qryGetAbsence", strWhere

option but I get no results back, I have also created a query that uses the values of the form items to generate the report but still I get a blank report (The query I wrote does work when I put hard coded strings into the criteria section
) , can someone tell me which is the best way to run the reports using from criteria and how it is possible to use the criteria on a form to filter a recordset, one thing to add I have just though of is that, the form is in modal mode. I don’t know if this makes a difference.
Any help would be greatly.

Simon

 

[MaxEd]

You can just use some conditional statements containing the form data to filter out the data you need. See if that works. That's what I use most of the time.

-Laughter works miracles.

 

[PHV]

Can you post the code building the strWhere string ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya simeybt . . . . .

I agree with [blue]PHV[/blue]. We have no way of knowing if [blue]strWhere[/blue] is in proper format for parsing by the Microsoft Jet.

You need to post the string build code!

See Ya! . . . . . .

 

[simeybt]

ok all I was a away for the weekend so if wee can pick this up again. From the start. here is the code that builds the string.

strWhere = strWhere & "Absence_Start <= #" & Me.txtDateStart & "# AND Absence_End <= #" & Me.txtDateEnd & "#"

 

[PHV]

Are the Me.txtDateXXX in m/d/yyyy format ?
Try something like this:
strWhere = "Absence_Start <= #" & Format(Me.txtDateStart, "m/d/yyyy") & "# AND Absence_End <= #" & Format(Me.txtDateEnd, "m/d/yyyy") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[simeybt]

It was the date thing that causing the problem i got is sorted this morning, Thanks for the posts anyway, Just a quick follow up question about this topic, is this the best way to run the reports using form to open reports or is there a better/faster way it can be performed.

Simon