Access Report-Can someone please Help! 1

[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 you help!

Phil

 

[PGM2]

I should have posted this in the Access reports section I suppose. Sorry about that. If you would still give any ideas I would appreciate it.
Phil

 

[Zion7]

It appears, CalcDate is a Date Type Variable, pound sign needed in WHERE expression...

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

???

 

[PGM2]

Thanks so much Zion7 that solved my problem. Can I get you to explain why I needed to place the #'s. I mean I understand somewhat but when I looked at the report filter when placing a user input box the value was the date but no #'s. When looking at the filter with original code I posted the date was there as well. The data type of the field in the table is as Date and the variable CalcDate was of Date type as well. WOuld I need extra symbols for any other data types in a similiar situation? I hope by you explaining I can catch this easy mistake in the future. Again thanks.
Phil

 

[Zion7]

Good question Phil, that was why I put "???", at the end of my post.
Off hand, I don't know how it could work otherwise, if it was exactly, the same statement. If you used Date(), it wouldn't require them?
Sorry Phil, I don't know why, (I've always used them?)

In case you're not aware, there are 3 different data types (that I'm aware of), that require different syntaxes.

Strings, requires either single quotes, or, 2 double quotes.

Integers, no quotes
Dates, "#"


Phil, it just occured to me, that filter code, stil shouldn't work. It should be

"[SerialNum] =" & Forms!SearchDevice![SerialNum]& " And [DateSent] = #" & CalcDate & "#"


Even more confused???

 

[PGM2]

I hear you Zion7 I just redid my code to see if I was mistaken with the code from the first post. It does indeed work somewhat of course without the desired results.

Code-->
DoCmd.OpenReport "Rpt_HistoryReport", ReportDest, , "[SerialNum] = Forms!SearchDevice![SerialNum] And [DateSent] >= " & CalcDate

Filter On Report Shows-->
([SerialNum] = Forms!SearchDevice![SerialNum] And [DateSent] >= 9/2/2004)

Oh well, no biggie as long as it works now, hopefully someone will be able to tell us why and now I know how it should be correctly sytaxed.
Phil