Pull day out of now()

[speakers_86]

I need someone more knowledgeable than me to check my logic. I have this:

Like Left(DateAdd("h",[forms]![frmSettings]![timezoneadjustment],Now()),8) & "*"

It SEEMS to work just fine. But let me explain what it does.

I have a table with entries, one of the fields is date, and it stores now(), minus the time zone adjustment.

Now on to my query where the above code is. I am trying to query all entries where the DAY is equal to now() minus the time zone adjustment. So if now()-TimeZoneAdjustment = 10/11/2010 13:35, then I want to return all records that have dates of 10/11/2010, regardless of the time of day.

Like I said my code works, but I am not sure if I should rely on it. For instance, what happens on when the month and day are only one digit long? Would the code break all of a sudden, since the left function suddenly includes the hour of the day?


edit- Is it really as simple as changing the format? If so, I feel really dumb...

 

[John Big Booty]

You should be able to do it using the Format(with Dates) Function,

 

[speakers_86]

Well I was just trying to make that hard, wasn't I? Thanks for the quick response to. Heres what I did.

Like Format(DateAdd("h",[forms]![frmSettings]![timezoneadjustment],Now()),"mm/dd/yyyy") & "*"

That is the criteria for the date in my query. Seems like it works, and I am much more comfortable with that than with the left function.

 

[John Big Booty]

Glad I could help

 

[Brianwarnock]

I am either missing the point or am about to display ignorance, but why not use Date() rather than Now(), then no times to worry about.

Brian

 

[speakers_86]

I can't use date() because then I have no way to subtract the TimeZoneAdjustment, or so I thought. Now that you bring it up, does date() also store the time, just formatted so that it does not show up? Maybe I'll go check.

edit- I created a field in a table that stores =date(), then queried that field, and set format to medium time. All records showed 12:00.

 

[CBrighton]

The DateAdd is adding the appropriate number of hours to Now() for whatever timezone is involved.

If Date() were used then it would assume the time is 00:00:00 which would mean it doesn't always return the correct date (e.g. 19th Sep 2011 00:00:00 - 8 hours = 18th Sep 2011 whereas 19th Sep 2011 12:00:00 - 8 hours is 19th Sep 2011).

 

[speakers_86]

Thats exactly it CBrighton.

 

[Brianwarnock]

Thanks guys thought it was ignorance about timezones and I wasn't wrong

Brian