Query by date criteria help

[mcmcd99]

Hello,

I have queries with criteria set to [Enter Date: MM/DD/YYYY] in order to have users search by date. If they don't type the date correctly, how do I ensure they must retype the date if it's incorrect? Or show all records instead of an empty report.

I was thinking an if statement where Iif(Not IsNull(), [Enter Date], "*")

But I'm sure I'm doing that wrong.. Help is greatly appreciated! Thanks

[ranman256]

you can use a form. on the form put a text box, txtDate
set it for date format. it will give an error if they dont enter a date correctly.

the query
select * from table where [date] = forms!myForm!txtDate

[mcmcd99]

That's not going to work for what I'm doing, although it's a good idea.

[June7]

Why won't a form work?

Inability to validate input in popup is 1 reason I NEVER use dynamic parameterized queries at all.

You are not correctly using the IsNull() function and really shouldn't use it in this context.

Also, wildcard is meaningless without LIKE operator.

WHERE [fieldname] LIKE Iif(Not [Enter Date] Is Null, [Enter Date], "*")

[mcmcd99]

Ahh I see, thank you

[mcmcd99]

So I've ended up getting it to work with that info.
This works great!:
[tsdate] LIKE Iif(NOT [Enter Date: MM/DD/YYYY] Is Null, [Enter Date: MM/DD/YYYY], "*")

Works great for selecting a single day!

I'm trying to do a date range now, and this doesn't seem to be working.
How would I do this in a way that would allow me to return an entire month's range?

I'm trying something like this, but it doesn't do the trick
[tsdate] LIKE >= Iif(NOT [Enter Start Date: MM/DD/YYYY] Is Null, [Enter Start Date: MM/DD/YYYY], "*") and [tsdate] LIKE <= Iif(NOT [Enter End Date: MM/DD/YYYY] Is Null, [Enter Start End: MM/DD/YYYY], "*")

[June7]

Can't mix LIKE with other (=, <, >, etc) operators.

Instead of LIKE and wildcard, provide an alternate date.

[tsdate] BETWEEN Iif(NOT [Enter Start Date: MM/DD/YYYY] Is Null, [Enter Start Date: MM/DD/YYYY], #1/1/1900#) AND Iif(NOT [Enter End Date: MM/DD/YYYY] Is Null, [Enter Start End: MM/DD/YYYY], #12/31/2199#)



Why can't you use a form?

[mcmcd99]

It looks liek that isn't quite doing it either. I'm trying something different than range.


This lets me select the month and it works great!
Year([tsdate])=Year(Now()) And Month([tsdate])=[Enter Month (numeric)]


How do I make it select year as well, since I wont always be looking for this year's reports?

I'm trying this, but it doesn't work.
Year([tsdate])=Year([Enter Year:]) And Month([tsdate])=Month([Enter Month:])

[mcmcd99]

Last question for the day, I promise lol.
Figuring that part out will solve a lot of issues I'm having.

[June7]

Year([tsdate])=[Enter Year:] And Month([tsdate])=[Enter Month Number:]

[mcmcd99]

You're my hero.