Passing WHERE statement to Report. Any Ideas??

[PGM2]

I have a dialog form which passes a filter to a report.The reports control source is a table. When I try to pass a filter with an 'And' in the where statement of the 'Docmd.Openreport' it doesnt filter right. I want the filter to query all devices with the serial num and the date provided. Cant seem to figure out my issue.
Ex.

Case 1
...
Case 2
CalcDate = DateAdd("d", -30, Date)
DoCmd.OpenReport "Rpt_HistoryReport", reportDest, , "[SerialNum] = Forms!SearchDevice![SerialNum] And [DateSent] = " & CalcDate
etc.

I have tried changing my statement a number of ways,using ( ) and such, but cant seem to get it right for the desired result. I dont want to use a inputbox so thats out but if I did the filter does seem to work. Can someone tell me what I am doing wrong.Thanks in advance for your help!
Phil

 

[avarga82]

I'm assuming that the datatypes are as follows: Number for "SerialNum" and Date/Time for "DateSent". Assuming that, you need to include special characters for the date. Your DoCmd line should read as follows...

DoCmd.OpenReport "Rpt_HistoryReport", reportDest, , "[SerialNum] = Forms!SearchDevice![SerialNum] And [DateSent] = #" & CalcDate & "#"

Try this and see where it takes you.

PS. If you were filtering with strings, you'd place it between single quotes. Ex ([Comment] = '" & Me.txtComment & "'")

 

[PGM2]

Hey avarga82 can you tell me why the filter would run, although not with the desired results, with the syntax I originally posted? From my understanding it shouldnt have ran in the first place and why if the variable is a date data type and the tables field is date type then why need the special signs in where statement. Also thanks for your response!
Phil

 

[avarga82]

PGM2...I forgot to explain a couple things. When you build a SQL (Structured Query Language) via code, the SQL string built by an Access query is no longer valid. You have to actually build it. So you putting "[SerialNum] = Forms!SearchDevice![SerialNum]" all in one quote will not work. You have to something like..


DoCmd.OpenReport "Rpt_HistoryReport", reportDest, , "[SerialNum] = " & Forms!SearchDevice![SerialNum] & " And [DateSent] = #" & CalcDate & "#"

I'm assuming you're calling this from the form named SearchDevice. If that's the case, you could just use the ME keyword. Example...


DoCmd.OpenReport "Rpt_HistoryReport", reportDest, , "[SerialNum] = " & Me.SerialNum & " And [DateSent] = #" & CalcDate & "#"

Let me know how this works out for you.

 

[PGM2]

Avarga82..thanks a lot. I actually came up with:

CalcDate = DateAdd("d", -30, Date)
DoCmd.OpenReport "Rpt_HistoryReport", ReportDest, , "[SerialNum] =" & Forms!SearchDevice![SerialNum] & "And [DateSent] >= #" & CalcDate & "#"

This works with my desired results but notice I that I never used the quotes surrounding the reportname. Should I be doing this to prevent any possible issues in the future or is either acceptable?
Also I am not actually calling the statement from SearchDevice form I have dialog form which is called from the SearchDevice and the reason is I couldnt get the SerialNum to update on the dialog form after the user would close the report and returned to the SearchDevice. Everytime, unless I closed the dialog form, the SerialNum captured/passed from the SearchSerial into a textbox, which really is just for infomational purposes, wouldnt update. I would like to keep it within the dialog form just for easier reading and understanding. Any ideas on that one?

 

[avarga82]

Yes, you always need to include your report name in quotes. Or assign a variable to it.

Dim rptString as string
rptString = "Rpt_HistoryReport"
DoCmd.OpenReport rptString , etc, etc, etc...

I'm not 100% sure what you need with the dialog forms... You said you need to "get the SerialNum to update on the dialog form after the user would close the report and returned to the SearchDevice". Why would you need to update the dialog after the user closes the report? I'm just not clear as to what you need...