Between criteria or other help

[ragribben]

I have a field that represents time but it is formatted as numerical and has no colons. It follows military time but midnight is minus the first two digits leaving the hour code out.
I need to be able to read this as AM and PM and have the user be able to specify the time spain.
I tried to make 2 queires one that says in the criteria that if the data is between 0 (midnight) and 115959 (one second before noon) then place an AM after it and then a different QRY that says if the criteria is between 12000 (noon) and 235959 (one second before midnight) put a PM after it.
The AM query works great.
The PM query does not, I know (or at least I think I know) it is because of the zeros after the 12 what can I do to get it to read only the data that is between noon (120000) and 11:59:59 (235959) pm?
Or is there a better way to do this than how I am doing it? If I could just add the colons to all the data that would actually work better, would have to start adding them from the right for every two characters until there aren't 2 more characters to the left.

Thanks,
Becky

 

[Guus2005]

I am not sure why you use two queries when one should be sufficient:

select IIF(militarytime<120000;"AM","PM") AS Prefix, militarytime FROM Table1

Ofcourse i don't know the actual fieldnames.

If it is possible i'd use the Access notation for a timestamp which is a double and represents the number of days after 12-30-1899 and behind the decimal symbol a representation of the number of seconds in a day.

In your immediate window:

?cdbl(now())
40766.4124305556
?cdate(40766.4124305556)
11-8-2011 9:53:54

HTH

 

[ragribben]

Only using 2 because I hadn't been able to get it to work with one
Basically due to my lack of knowledge.

I will try this, Thank You!