Change date according to time: Minus week-ends

[Dököll]

Greetings and salutations!

Ran a quick search here a found this: http://www.thescripts.com/forum/thre...inus+date.html Though it is very helpful when dealing with dates alone, I cannot tweak it to fit my demands.

I decided to try it in query:

The idea is to look two fields through a query (TodayTime and TodayDate), and change my dates to previous date, according to Time, and the time must be specific, > than 3:30 PM, while minusing the week-ends:

The query is not ready yet, will post in a bit.

Any help you can provide is surely appreciated:-)

[Dököll]

Greetings and salutations!

Ran a quick search here a found this: http://www.thescripts.com/forum/thre...inus+date.html Though it is very helpful when dealing with dates alone, I cannot tweak it to fit my demands.

I decided to try it in query:

The idea is to look two fields through a query (TodayTime and TodayDate), and change my dates to previous date, according to Time, and the time must be specific, > than 3:30 PM, while minusing the week-ends:

The query is not ready yet, will post in a bit.

Any help you can provide is surely appreciated:-)

Now the query works just fine:

 
SELECT ThisTableData.TodayDate, ThisTableData.TodayTime, DateAdd("d",-1,[ThisTableData.TodayTime]) AS DateOO
FROM ThisTableData
WHERE (((ThisTableData.TodayDate) Between #9/1/2007# And Now()) AND ((ThisTableData.TodayTime) Not Like "*AM*") AND ((DateAdd("d",-1,[ThisTableData.TodayTime])) Not Like "*12:*"));
 
 

TodayTime hold the time I need to fool around with, as mentioned above. My problem now is figuring out how to disregard 12 Noon Through 3:29 PM hours:

 
Not Like "*12:*"
 
 

Works, but when I plug in:

 
Not Like "*1:*"
 
 

Access thinks I mean 1:01:00 PM

What do you make of it?

[Scott Price]

What happens if you use 24 hour time instead of the 12 hour variety? i.e. 13:00 instead of 1:00pm...

Regards,
Scott

[Dököll]

Thanks much, Scott, I did not think of of that:-)

Have a good week!

[Dököll]

What happens if you use 24 hour time instead of the 12 hour variety? i.e. 13:00 instead of 1:00pm...

Regards,
Scott

Actually, Scott, I almost forgot my manners. You are recent Mod, correct! I do not believe to have welcomed you, Welcome!!!

Dököll

[FishVal]

Hi, Dokol.

So, why not to use Hour() function?

[Scott Price]

Actually, Scott, I almost forgot my manners. You are recent Mod, correct! I do not believe to have welcomed you, Welcome!!!

Dököll

Thanks, Dokoll! I'm getting adjusted to this new level of responsibility :-)

The 24 hour format will not work, obviously, if you are comparing against existing data that is contained in 12 hour format. In which case, using the Hour() function may be the best way to go.

Regards,
Scott

[Dököll]

Hi, Dokol.

So, why not to use Hour() function?

Great minds think alike. I was coming over to report it not working. In I attempted to convert to 13:00 it did not like. I also still have Now(), rather than Time(), in my table for the TodayTime field. I must have kept it there because it gave me the time anyway through the form. Will try your examples:-)

take care good buddies, and thanks!