Between And operator doesnt work in VBA code for date 1

[axslearnr]

i have a form where i have four date text boxes where i want to pull a report between those dates. my report doesnt pull anything i mean blank report. does it recognize between and and operator? whats wrong in my syntax in where condition.

psl let me know

thank you

stDocName = "RepassExpiryLots_Report"
DoCmd.OpenReport ReportName:=stDocName, View:=acPreview, Wherecondition:="( (( [Lot Numbers].[Expiry Date] Between " & txtExpiryDate1 & " AND " & txtExpiryDate2 & " ) AND (([Lot Numbers].[Lot Exhausted]) Is Null)) OR (( [Lot Numbers].[Repass Date] Between " & txtRepassDate1 & " AND " & txtRepassDate2 & " ) AND ( ([Lot Numbers].[Lot Exhausted]) Is Null) ))

 

[Golom]

You usually need to enclose dates in #...# characters
[blue][tt]
DoCmd.OpenReport ReportName:=stDocName, View:=acPreview, Wherecondition:= _
"( (( [Lot Numbers].[Expiry Date] Between #" & txtExpiryDate1 & "# AND #" & txtExpiryDate2 & "#) AND (([Lot Numbers].[Lot Exhausted]) Is Null)) OR " & _
"(( [Lot Numbers].[Repass Date] Between #" & txtRepassDate1 & "# AND #" & txtRepassDate2 & "#) AND ( ([Lot Numbers].[Lot Exhausted]) Is Null) ))"
[/tt][/blue]

 

[SkipVought]

Hi,

Your Textbox values are STRINGS and not DATES. Do as Golom suggested or conver the table date fields to STRING via a Format statement...

DoCmd.OpenReport ReportName:=stDocName, View:=acPreview, Wherecondition:= _
"( (( Format([Lot Numbers].[Expiry Date],"mm/dd/yyyy") Between '" & txtExpiryDate1 & "' AND '" & txtExpiryDate2 & "') AND (([Lot Numbers].[Lot Exhausted]) Is Null)) OR " & _
"(( Format([Lot Numbers].[Repass Date],"mm/dd/yyyy") Between '" & txtRepassDate1 & "' AND '" & txtRepassDate2 & "') AND ( ([Lot Numbers].[Lot Exhausted]) Is Null) ))"

assuming that "mm/dd/yyyy" is the validated format of you textbox.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Tranman]

axslearnr,
Some background on your problem:

In SQL, just like you have to put quotes around text values in order to get them to compare correctly, you have to put #'s around text dates in order to have them compare correctly.

For example, your date field, [Expiry Date] might be 4/4/2004. However, what is actually stored in the table is 38081. So, in order to compare a text field that contains "04/04/2004" to Expiry Date, and get them to be equal, you have to say "Where [Expiry Date] = #" & txtfield & "#" which tells the system to treat "04/04/2004" as if it is a date field(a numeric value) instead of a text field that looks like a date.

Hope that helps.
Tranman

 

[axslearnr]

hi
thank you for your help. it worked. i really appreciate it.