Update field based on time stamp

[Nortonsapple]

Hello, I have a date field that is using now() as default. If that time stamp is after or before closing time I need another field adjusted with a new time. I have worked out how to adjust time but not how to get the query to check the time portion of the field with the now() time date stamp as criteria.



I'm not sure if I am saying what I need correctly and your help and direction is most appreciated!

[andy49]

Datepart function sounds like what you need.


Sent from my iPhone using Tapatalk

[Nortonsapple]

I've tried it but I don't think I'm doing right. I used :
>datepart(hh,[my date field],#6:00:00#)

In the criteria section for the date time stamp field.
Which did not work. I'm just missing it in a major way. I need it to find time stamps in that field that came in before 8:30am and after 6:00 pm. Variations that I have come up with pull all times or none with a error. Thank you for helping me!

[Bob Fitz]

I think you will need something like:

a: IIf(TimeValue(DatePart("h",[YourDate]) & ":" & DatePart("n",[YourDate]))>"08:30","LaterCalc","EarlierCalc")

[Nortonsapple]

Thanks! I'll try it as soon as I get in. My VMware connection isn't playing fair this morning. I hadn't even considered a iff then statement. I haven't used them in access before. Would i place it in the update field or the criteria field of a update query or would this be the criteria in a select query? I apologize for my "newbness". I'm learning as I go.

[Nortonsapple]

I'm thinking the update cell of "new time" field, right?

[Micron]

I have worked out how to adjust time but not how to get the query to check the time portion of the field with the now() time date stamp as criteria

Not sure I understand where the IIF part comes in. If you already know how to make the adjustment, isn't the issue about how to get the hour part of date/time?

WHERE ((DatePart("h",[aTime]))=7)

[Nortonsapple]

WHERE ((DatePart("h",[aTime]))=7) works but how do I account for pm versus am? I tried using military time but it didn't do as expected.

[andy49]

WHERE ((DatePart("h",[aTime]))=7)

Id suggest playing around with "or 19"

[Nortonsapple]

Or 19 got a data type mismatch. When I tried using >18 it did pull up those after 6pm but still had a few AMs too.

[andy49]

how about

WHERE ((DatePart("h",[time])=7)) OR ((DatePart("h",[time])=19));

[Micron]

Or 19 got a data type mismatch. When I tried using >18 it did pull up those after 6pm but still had a few AMs too.

Since there was no OR in my example, posting your attempt would have been helpful. This this is what I get using DatePart("h",[RequestDate])=14

DeptID	RequestDate
1	12/11/15 2:13:39 PM