Using Current System Time to Select Groups of Records

[Sighman]

Hi All

I have a set of data that includes a text field with phone number in
it. I would like to be able to select various numbers based on what
time of day the query is run.

For example, if the time is before 17:45, I would like phone numbers
that begin with 03 or 06 or 07 to be selected and if it is after
5:45pm, I'd like numbers that begin with 04 or 09.

I'm current using an Iif statement
IIf(Time()<#5:45:00 p.m.#,"033021842","095246060")
which works fine but only allows me to select one record.

If I modify the statement to be IIf(Time()<#5:45:00 p.m.#,Like "03*",
Like "09*"), I get no records at all when you would think it'd select
everything beginning like that. I've also tried IIf(Time()<#5:45:00
p.m.#,"Like '03*'", "Like '09*'") to no avail,

Ultimately, I'd like it to be something like IIf(Time()<#5:45:00
p.m.#,IN("03*","06*","07*"),IN("04*","09*"))

Can anyone help?

 

[kingston via AccessMonster.com]

In order to use Like with other operators in the criteria of a query, Like
must be outside of the expression:

Like IIF(Time()<#5:45:00 p.m.#,"03","XYZ") & "*" OR
Like IIF(Time()<#5:45:00 p.m.#,"06","XYZ") & "*" OR
Like IIF(Time()<#5:45:00 p.m.#,"07","XYZ") & "*" OR
Like IIF(Time()>#5:45:00 p.m.#,"04","XYZ") & "*" OR
Like IIF(Time()>#5:45:00 p.m.#,"09","XYZ") & "*"

"XYZ*" is something arbitrary that will never match with any data.

 

[John Spencer]

Slightly shorter version of the criteria would be

LIKE IIF(Time()<#5:45:00 p.m.#,"0[367]*","0[49]*")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sighman]

Slightly shorter version of the criteria would be

LIKE IIF(Time()<#5:45:00 p.m.#,"0[367]*","0[49]*")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

In order to use Like with other operators in the criteria of a query, Like
must be outside of the expression:

Like IIF(Time()<#5:45:00 p.m.#,"03","XYZ") & "*" OR
Like IIF(Time()<#5:45:00 p.m.#,"06","XYZ") & "*" OR
Like IIF(Time()<#5:45:00 p.m.#,"07","XYZ") & "*" OR
Like IIF(Time()>#5:45:00 p.m.#,"04","XYZ") & "*" OR
Like IIF(Time()>#5:45:00 p.m.#,"09","XYZ") & "*"

"XYZ*" is something arbitrary that will never match with any data.

Thanks for all your ideas! Everything worked perfectly! and I learnt
something as well.

Simon