By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Date Field to Day of Week

P: n/a
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.

Oct 3 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.
Oct 3 '07 #2

P: n/a
On Oct 3, 6:47 am, sh...@mbproducts.com 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?

Oct 3 '07 #3

P: n/a
On Oct 3, 2:52 pm, OldPro <rrossk...@sbcglobal.netwrote:
On Oct 3, 6:47 am, sh...@mbproducts.com 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

Oct 3 '07 #4

P: n/a
<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
Oct 3 '07 #5

P: n/a
On Oct 3, 9:09 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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.- 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.

Oct 4 '07 #6

P: n/a
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" <AllenBro...@SeeSig.Invalidwrote:
>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.

<sh...@mbproducts.comwrote 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.
Oct 4 '07 #7

P: n/a
On Oct 3, 5:47 pm, sh...@mbproducts.com wrote:
On Oct 3, 2:52 pm, OldPro <rrossk...@sbcglobal.netwrote:
On Oct 3, 6:47 am, sh...@mbproducts.com 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))

Oct 4 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.