Date Field to Day of Week

[sheri]

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.

[Allen Browne]

The best solution would be to use a field of type Date/Time instead of this
number (or text?) field. You can then show the day of the week in your form
by adding another textbox bound to the same field, and set its Format
property to:
ddd

You do not want another field text in your table for the day of the week.
That would introduce the possibility of bad data (i.e. where the date and
day don't match.) This article explains more about this design principle:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

BTW, don't name the field Date. That's a reserved word in JET and in VBA
(for the system date), so Access may misunderstand what you mean.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sh***@mbproducts.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...

>I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.

[OldPro]

On Oct 3, 6:47 am, [e-mail] wrote:

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.

You haven't given enough information to solve this puzzle. Would
11022 be Oct 22, 2001 or Feb 2, 2011?

[sheri]

On Oct 3, 2:52 pm, OldPro <[e-mail]:

On Oct 3, 6:47 am, [e-mail] wrote:

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.

You haven't given enough information to solve this puzzle. Would
11022 be Oct 22, 2001 or Feb 2, 2011?

11022 would be Oct 22, 2001 and 110202 would be Feb 2, 2011

[Larry Linson]

<sh***@mbproducts.comwrote

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Do you have a compelling reason to not use the Access date/time field and
variable types to store your information? There are ample formatting
options to display it any way you wish when it is time to use it. I
strongly emphasize and "second" Allen's recommendation to never name a Field
or Variable "Date" because that is an Access reserved word and can be
confusing -- even worse, it can introduce "subtle bugs" that are difficult
to debug and correct.

Larry Linson
Microsoft Access MVP

[sheri]

On Oct 3, 9:09 am, "Allen Browne" <[e-mail]:

The best solution would be to use a field of type Date/Time instead of this
number (or text?) field. You can then show the day of the week in your form
by adding another textbox bound to the same field, and set its Format
property to:
ddd

You do not want another field text in your table for the day of the week.
That would introduce the possibility of bad data (i.e. where the date and
day don't match.) This article explains more about this design principle:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

BTW, don't name the field Date. That's a reserved word in JET and in VBA
(for the system date), so Access may misunderstand what you mean.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<[e-mail] in message

news:11**********************@k79g2000hse.googlegr oups.com...

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.- Hide quoted text -

- Show quoted text -

I need the day field for a crosstab query. I want to average sales by
day for the past 6 weeks.

[Allen Browne]

Too easy:

In the crosstab query (design view), assuming that MyDate is a real
date/time view, type an expression into the Field row like this:
Weekday([MyDate])

You will also add MyDate to the design grid again, chosing Where, and
specifying criteria such as:

>= Date() - 42

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sh***@mbproducts.comwrote in message
news:11**********************@g4g2000hsf.googlegro ups.com...

On Oct 3, 9:09 am, "Allen Browne" <[e-mail]:

>The best solution would be to use a field of type Date/Time instead of
this
number (or text?) field. You can then show the day of the week in your
form
by adding another textbox bound to the same field, and set its Format
property to:
ddd

You do not want another field text in your table for the day of the week.
That would introduce the possibility of bad data (i.e. where the date and
day don't match.) This article explains more about this design principle:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

BTW, don't name the field Date. That's a reserved word in JET and in VBA
(for the system date), so Access may misunderstand what you mean.

<[e-mail] in message

news:11**********************@k79g2000hse.googleg roups.com...

>I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.- Hide quoted text -

- Show quoted text -

I need the day field for a crosstab query. I want to average sales by
day for the past 6 weeks.

[OldPro]

On Oct 3, 5:47 pm, [e-mail] wrote:

On Oct 3, 2:52 pm, OldPro <[e-mail]:

On Oct 3, 6:47 am, [e-mail] wrote:

I have a field called date, the date is in the field like this 70925
(which means Sept. 25, 2007).
I have another field called day, it is a text field. How do I write a
query to populate the day field from the date field?

Thanks for your help.

You haven't given enough information to solve this puzzle. Would
11022 be Oct 22, 2001 or Feb 2, 2011?

11022 would be Oct 22, 2001 and 110202 would be Feb 2, 2011

DayOfMonth=val(Right(someDate,2))