473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Field to Day of Week

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
7 10150
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
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
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
<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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: BlackFireNova | last post by:
I need to write a report in which one part shows a count of how many total records fall within the working days (Monday - Friday) inside of a (prompted) given date range, in a particular...
4
by: Mark | last post by:
Hi I have been trying to convert the week number to a range of dates that I can use. It should be fairly simple for you guru's out there but for us mere mortals it is beyond our grasp. I know...
9
by: PamelaDV | last post by:
I have two problems, actually. I am looking to see if there is a function that will return the day of the week (Monday, Tuesday, Wednesday, etc...) from a date. For instance 2/27/04 is a Friday....
5
by: Alicia | last post by:
Yes, but will that skip a week and group by the date for me? I basically wanted something that would do a count of the dates, then group them by their week name.. BEFORE: Resource Date ...
3
by: Matt | last post by:
Given a date, how to find the beginning date and ending date of that week please advise!
10
by: RoadRunner | last post by:
Hi, I have a employee vacation database that has a vacation table that has the employee name, pay week and date of vacation. I have another lookup table with pay week code and date range for the...
11
by: shankindc | last post by:
Hi, I have the following requirements In a form, there exists a date field which needs to be automatically populated. The date has to be the Friday of every week. Assume in a calendar week Feb...
1
by: Simon | last post by:
Dear reader, Is there a function or VBA code available to find the week number out of a date field. You can find the year with Year(date field) but now I need the week number out of a...
1
osward
by: osward | last post by:
Hi everyone, Background 1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >=...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.