Select records from single date

[cdck]

I need to pull records from a table that are entered on a single date, supplied by the user, for a report. The data is stored in a SQL Server backend as Date/Time.

I have tried adding a filter to the report that states:
[tt] DateValue(InspEnd) LIKE DateValue([Date:])[/tt]

When I run it with this filter, I get an error that states:
[tt]The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.[/tt]

If I take out the second DateValue function so:
[tt]DateValue(InspEnd) LIKE [Date:][/tt]

I still get the same error. Likewise if I substitute "=" for the "LIKE". If I just leave it with
[tt]InspEnd = [Date:][/tt]

it pulls nothing even for confirmed dates with records attached to them; I assume this is because InspEnd holds not just the date, but also a time.

How do I get it to look at only the date in InspEnd and compare it to [Date:] (as supplied by the user)?

Cheryl dc Kern

 

[cdck]

Had a brainstorm just after I posted, and came up with this, which works:
[tt]DateDiff("m", InspEnd, [Date:])=0 AND DateDiff("y", InspEnd, [Date:])=0 AND DateDiff("d", InspEnd, [Date:])=0[/tt]

If anyone has a better way, though, I'd appreciate it.

Cheryl dc Kern

 

[PHV]

Perhaps this ?
Int(InspEnd) = Int([Date:])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdck]

When I used that in the filter, I got the error again. Thanks for the effort...

Cheryl dc Kern

 

[PHV]

Have you tried simply this ?
DateDiff("d", InspEnd, [Date:])=0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdck]

Won't that return the same day in different months and years?

Cheryl dc Kern

 

[cdck]

Sorry, just tried it, and it works!

My assumption was that when asked to look at a date part, it only compares that part - but it appears that it compares the whole date and then specifically that part - is this understanding correct to your knowledge?

Cheryl dc Kern

 

[PHV]

is this understanding correct to your knowledge
Yes, as clearly stated in the VBA help for the Datediff function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886