3 letter Date format

[maw230]

i have a field in access that is a 3 letter day abbreviation (i.e. mon, tue, wed) called Day_of_Week. I would like to be able to manipulate this as if it were a date field. basically i need to subtract 1 or 2 from it to get the day or 2 before.
is there a format i can apply to it?

 

[vbaInet]

I don't fully understand what you mean. Please elaborate.

 

[maw230]

ok.
I need to do something like:

Day_of_week - 1

so if it were Mon it would give me Tue data.
the reason im doing it this way is because im trying to compare sales data for the same Week and Day of week for 2 years. So, for example, i want sales data for Monday of this week and Monday of this week last year.
the only field i can find to use is this 3-letter day_of_week field.

unless i could format my actual Date field to give me the Day and not the day number.

make sense?

 

[vbaInet]

Lookup these functions Format() and DateAdd() and see what you can do with them. If you get stuck just post back.

 

[gemma-the-husky]

there are others

an access date type field actually holds a date and time, and can be displayed in a variety of ways - there are also many functions for handling dates


format is an important one

format(mydate,"dd/mm/yyyy") - is a standard UK date presentation 12/01/2010
format(mydate,"mm/dd/yyyy") - is a US version 01/12/2010

using 3 letters as mmm,
format(mydate,"dd mmm yyyy") - 12 Jan 2010

using 4 letters as mmm,
format(mydate,"dd mmmm yyyy") - 12 January 2010

same principles with dd, ddd, dddd

and there are some "standard" masks

format(mydate,"medium date")

---------------
now you can also test for the day of the week - and vb also gives you easy to remember date constants

so -
if weekday(anydate) = vbsunday then etc

---------------
and many other date functions. Its not generally a good idea to try to roll your own date storage and functions - as they are already there waiting to be used.

 

[maw230]

thanks, i will just have to play around with it and see what i can come up with.

 

[vbaInet]

You do just that Great info from Dave though.