473,574 Members | 2,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 10180
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***@mbproduc ts.comwrote in message
news:11******** **************@ k79g2000hse.goo glegroups.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...@mbproduct s.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...@sbcg lobal.netwrote:
On Oct 3, 6:47 am, sh...@mbproduct s.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***@mbproduc ts.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...@Se eSig.Invalidwro te:
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...@mbproduc ts.comwrote in message

news:11******** **************@ k79g2000hse.goo glegroups.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***@mbproduc ts.comwrote in message
news:11******** **************@ g4g2000hsf.goog legroups.com...
On Oct 3, 9:09 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
>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...@mbprodu cts.comwrote in message

news:11******* *************** @k79g2000hse.go oglegroups.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...@mbproduct s.com wrote:
On Oct 3, 2:52 pm, OldPro <rrossk...@sbcg lobal.netwrote:
On Oct 3, 6:47 am, sh...@mbproduct s.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
14883
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 geographical region. I have written a query which prompts the user for the start and end dates. It also filters for entries which pertain to the particular...
4
22658
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 that there are different start days of the week so I would presume any function would provide that facility. Hope you can help Mark
9
7272
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. Also is there a way to group dates by week? I have a user who wants a report to group by week. I know I could create a table and assign dates...
5
2386
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 Total number of times that date Appeared (Count) 4/3/03 4 4/4/03 2 (note 4/4/03 showed up twice)
3
5927
by: Matt | last post by:
Given a date, how to find the beginning date and ending date of that week please advise!
10
2231
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 week. The user would like to type in a date in the Date of Vacation field and have the Pay Week field automatically fill in. My dlookup code is...
11
2828
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 4th - Feb 11th, if the user opens a form for data entry anytime between Feb 4th(Sunday) - Feb 9th (Friday), the data field should populate with Feb 9th...
1
2738
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 date field.
1
2611
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 >= current date. Otherwise, user have to waste time to page up the pages to find the current date 2. I got a script of simple calendar from the web...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8258
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8118
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5321
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2254
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.