Retriving Specific records that contains time

[isihalin]

Hi

I have created a MS access table which captures the attendance for the employee. In the table , the field IOTime carries the time of the associate entry.
I have written an SQL query to fetch the data for the current date . but i am unable to get the records for specific time.

Can anyone help me with the syntax:

here is my code:

SELECT IOData.CardNo, IOData.IODate, IOData.IOTime, IOData.IOStatus
FROM IOData
WHERE IODate=Date()

The above code get the record that contains the IODate value as current date. Same way i need to get the IOTime for current time.

Thanks in advance
isihalin

 

[Husan]

if you set the default value of IOData.IODate to =Now() this captures the date & time
then in yr query you could use the criteria:
WHERE IODate=Now()

 

[axle124]

You would have to have a variant. The date() returns todays date only.

try this:
SELECT IOData.CardNo, IOData.IODate, IOData.IOTime, IOData.IOStatus, date1 as variant
FROM IOData
WHERE date1=iodate

brandon

 

[Brianwarnock]

You do not say how your fields are populated.
I would always have a Date-time field as you can get the separate values if required using Datevalue(field) and Timevalue(field)

Brian

 

[isihalin]

Thx all for your replies,

I think you guys did not get my questions right. the IOTime field carries only the time(09:02:27) and i need to fetch the records for the condition IODate --> current date and IOTime either the current time

Thx once again for your replies.

 

[Rabbie]

Thx all for your replies,

I think you guys did not get my questions right. the IOTime field carries only the time(09:02:27) and i need to fetch the records for the condition IODate --> current date and IOTime either the current time

Thx once again for your replies.

Because Access stores Times as a decimal fraction it can be hard to get an exact match so that may well be why your query is not returning anything for a specific time.

What exactly are you trying to achieve here?

 

[isihalin]

Hi

I am trying to export the table for records for specific date and specific time using macro, now that i am able to get the record for current date

code for getting record for current date:

SELECT IOData.CardNo, IOData.IODate, IOData.IOTime, IOData.IOStatus
FROM IOData
WHERE IODate=Date()

Now, i need to get records for both current date and time (for the last one hour).. so that my application will load the export file and calcuate the attendance for the employee for that hour...

I am unable to fetch records for the time, the IOTime field contains the time of entry and exit of a employee.

Thanks
isihalin

 

[raskew]

Hi -

Now() returns date and time
x = now()
? x
3/17/2009 6:59:36 AM

And here is how Access stores it:
? cdbl(x)
39889.2913888889

It should be obvious that the odds that
someone is going to have that precise
date/time is somewhere between slim and none.

As was previously pointed out, it's to your
advantage to store date & time together rather
than in separate fields.

Having said that, please define (using the above
example) what date/times would meet your criteria

i need to get records for both current date and time (for the last one hour)

Bob

 

[gemma-the-husky]

try these

current date only is date()
current time only is now() - int(now)

unfortunately i cant see an intrinsic function similar to int() that returns the decimal part of a number.
so i have used the expression number - int(number) to obtain it

 

[Brianwarnock]

He doesn't want the current time he wants a time range within a date.
If he had date and time in one field as I suggested he could use Between... And...
Depending on what he wanted that could be
Between Date() + #11:00:00# And Date() + #!2:00:00# to obtain everything for that hour today, if he tries to use Now() he will be shooting at a moving target, but maybe he would be happy with
Between Dateadd("h",-1,Now()) and Now()

Brian

 

[isihalin]

Brian,

Thanks again, now i tried the following code with your inputs

SELECT IOData.CardNo, IOData.IODate, IOData.IOTime , IOData.IOStatus
FROM IOData
WHERE IODate=date() and IOTime Between Dateadd("h",0,Now()) and now()

I have a record for current date and IOtime has 11:22:00 . now when i try the above code by replacing the "h" to 12 i am not getting the record. am i using your code correctly?

Also , I forgot to tell you that the IOTime is a Text Data type and not date/time.

Thanks
isihalin

 

[gemma-the-husky]

Dateadd("h",0,Now()) and now()

this is the problem - dateadd 0 is adding 0 hours to the date, so you are searching for times between now and now - which obviously will find no matches

instead you need

Dateadd("h",-1,Now()) and now()

to search between now, and an hour before now

 

[Brianwarnock]

isihalin

Please read posts carefully. I asked how you were populating the fields in my first post, its a bit late to tell us that it is a text field.
Also i wrote

but maybe he would be happy with
Between Dateadd("h",-1,Now()) and Now()

In my last post.
We cannot help you if you do not read our posts carefully.

Brian

 

[Brianwarnock]

Ok I've calmed down.
If you insist on keeping separate date and time fields with a text time field you have some converting to do.
Assuming that you time format is a normal time format although text then Cdate will convert it to a date/time for the comparison. Now() has a date as well as a time element and you only require the time element so

IOTime Between Dateadd("h",0,Now()) and now()

ends up as

CDate(IOTime) Between Timevalue(Dateadd("h",0,Now())) and Timevalue(now())

If Cdate cannot convert your texttime to a proper time then you will need to play with TimeSerial.

The moral of this story is to use just one true date/time field
AND of course there are NO checks on a text field for valid date/time.

Brian

 

[isihalin]

Hi Brian,

I was really confused myself for the last two days on this issue, and i truly respect your options were not properly gone through . I have found the solution for retriving specific time value.

find my code below, hope this will end all my queries now.

SELECT IOData.CardNo, IOData.IODate, IOData.IOTime, IOData.IOStatus
FROM IOData
WHERE IOdate = date() and IOTime between time()- #1:00:00# and time() - #2:00:00#;

where i am trying to fetch records for the specific time between the hours.

Thanks you for supporting me through.