Query between 2 different time references

[maGemme]

Hi, I thought this would be a very simple matter but I keep searching to no avail. I did find one post that where the person was looking for the exact same thing as me but there were no answers .

Basically I have a table recording questions that we get. When we log them a time is written in a field.

What I need is to have on a form 2 drop down lists with hours in them (24 hours format, 0-23). Basically one box would be "start time" and the other "end time". I need the proper query criteria to get all the questions asked between those 2 times.

I figured I would use something similar to dates like : Between hour(timefield) and (anothertimefield) but that returned errors.

I am now stuck.

Please help!

 

[MSAccessRookie]

Hi, I thought this would be a very simple matter but I keep searching to no avail. I did find one post that where the person was looking for the exact same thing as me but there were no answers .

Basically I have a table recording questions that we get. When we log them a time is written in a field.

What I need is to have on a form 2 drop down lists with hours in them (24 hours format, 0-23). Basically one box would be "start time" and the other "end time". I need the proper query criteria to get all the questions asked between those 2 times.

I figured I would use something similar to dates like : Between hour(timefield) and (anothertimefield) but that returned errors.

I am now stuck.

Please help!

One thing that I noticed is that you have selected an MS Access Reserved word (hour) for the name of one of your Columns. You need to avoid this because of unpredictable results. Consider renaming this Column.

Another thing that I noticed is that your range of hours is evaluated as if it were two digits (characters?) instead of time Format. If the Column definition is not a Date/Time, then the query that you are using could also have unpredictable results. The same applies to your other time Field.

If this does not change anything, please get back to us.

 

[maGemme]

The word hour refered to what I thought was the function to call time in the query criteria.

The time field I use does not use reserved words (qstTime)

 

[MSAccessRookie]

The word hour refered to what I thought was the function to call time in the query criteria.

The time field I use does not use reserved words (qstTime)

the Function hour() returns the hour part of the current time (in 24 hour format) For instance, As it is 5:55 PM here where I am, the Function Hour would currently return a value of 17.

 

[maGemme]

Ok thank you, but that doesn't resolve my problem

 

[vbaInet]

Things you should check:

1. Is your field a true Date/Time field? Is Date/Time the field's datatype or is it Text?
2. Are your times saved in the 24 hour format?

 

[maGemme]

I have 2 seperate fields in my table, one for the date and one for the time, QstDate & QstTime.

The time field is a "short time" format with 24 hours.

 

[vbaInet]

And if you remove the Format what do you get?

 

[maGemme]

Ok I figured it out!

I just didn't write my function properly. I took an example from a date I used (betweeen xxx and xxx) and applied it to my time query...

Your advice helped, changing the format didn't actually change anything but it gave me an idea so thank you!!

 

[vbaInet]

Glad you solved it.

Formatting should be avoided at all costs when applied at table level because it hides what the "real" data is. It's better you format in queries, forms or reports.

Good luck!