Setting criteria for field representing time of day. 1

[monkeysee]

I have a table that represents times of day for an appointment data base. The field is a number field with data entered as: 06:01 AM, etc.
I also have a query based on that table. I would like to set the criteria in the query to only show times between 07:00 AM and 06:00 PM.
I first tried the >07:00 AM, but that returned an error.
Anyone with suggestions will be greatly appreciated!
Thank you for all those that give advice and help on this forum! It is definitely appreciated by us rookies

 

[SkipVought]

Hi,

... between CDate(“07:00 AM”) and CDate(“06:00 PM”)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[monkeysee]

Thank you for your help, when using I got the following error: "The expression is typed incorrectly or it 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.

Here is the original sql before using your suggestion, hope this helps.

SELECT IIf([forms]![fmnuMainMenu]![chkShowTwentyFourHour]=True,[TimeAlt],[Time]) AS [Time of Day], tlkTime.TimeID, IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]) AS Expr1
FROM tlkTime
WHERE (((IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]))=0))
ORDER BY tlkTime.TimeID;


Thanks!

 

[SkipVought]

What did you try?


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[monkeysee]

SELECT IIf([forms]![fmnuMainMenu]![chkShowTwentyFourHour]=True,[TimeAlt],[Time]) AS [Time of Day], tlkTime.TimeID, IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]) AS Expr1
FROM tlkTime
WHERE (((tlkTime.TimeID) Between CDate("“07:00 AM”") And CDate("“06:00 PM”")) AND ((IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]))=0))
ORDER BY tlkTime.TimeID;

 

[SkipVought]

Try ' or only one set of "

WHERE (((tlkTime.TimeID) Between CDate('07:00 AM') And CDate('06:00 PM')) AND ((IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]))=0))

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[monkeysee]

Thanks Skip,
This is what I now used (as per your last post)
SELECT IIf([forms]![fmnuMainMenu]![chkShowTwentyFourHour]=True,[TimeAlt],[Time]) AS [Time of Day], tlkTime.TimeID, IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]) AS Expr1
FROM tlkTime
WHERE (((tlkTime.TimeID) Between CDate('07:00 AM') And CDate('06:00 PM')) AND ((IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]))=0))

same error message.

 

[SkipVought]

What happens with...

SELECT IIf([forms]![fmnuMainMenu]![chkShowTwentyFourHour]=True,[TimeAlt],[Time]) AS [Time of Day], tlkTime.TimeID, IIf([forms]![fmnuMainMenu]![txtDivisor]=0,0,[MinuteCount] Mod [forms]![fmnuMainMenu]![txtDivisor]) AS Expr1
FROM tlkTime
WHERE (tlkTime.TimeID) Between CDate('07:00 AM') And CDate('06:00 PM');

This is an Access table, yes?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dhookom]

What is the data type of TimeID? You stated numeric but you are comparing it to everything but numeric although internally dates and times are floating point numbers.

If you copy the TimeID representing 7:00 AM and paste it into Excel, what value do you see?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Duane, CDate() converts to a Date/Time numeric value.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dhookom]

I am aware of the function which converts. I am concerned the fields and data types in the table tlkTime. It looks like there are fields like Time, MinuteCount, and TimeAlt. "Time" is a horrible name for a field since it is a function name. I do feel the TimeID is probably an autonumber and the [Time] field contains the actual time value like #07:00#. I could be wrong but would bet you a cold beverage of your choice ;-)


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

You’re probably right about TimeID and Time.

I was assuming that the OP knew what his table was all about, but I’ll bet nothing on that assumption.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[monkeysee]

Hi Skip and Duane,
Got it to work by putting the criteria on the TimeID field using the Between & And
Thank you for all your thought process and insights!!

 

[SkipVought]

@Duane, I’ll have a virtual Diet Peach Snapple on the rocks. Best stuff on earth!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dhookom]

Skip, I got this round. I'm a peach fan also but my beverage of choice might be a bit stronger ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016