INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Setting criteria for field representing time of day.

Setting criteria for field representing time of day.

(OP)
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

RE: Setting criteria for field representing time of day.

Hi,

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

(OP)
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!

RE: Setting criteria for field representing time of day.

What did you try?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

(OP)
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;

RE: Setting criteria for field representing time of day.

Try ' or only one set of "

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

(OP)
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.

RE: Setting criteria for field representing time of day.

What happens with...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

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

RE: Setting criteria for field representing time of day.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

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 winky smile

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

RE: Setting criteria for field representing time of day.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

(OP)
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!!

RE: Setting criteria for field representing time of day.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Setting criteria for field representing time of day.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close