Converting a date to a numeric (1-7 number) day of the week

I am trying to take a date (e.g., 01/01/2008) and have Access tell me the 
numeric day of the week (1-7) that corresponds to each date in a table.  
Thanks for your help!
0
Utf
2/6/2008 4:00:03 PM
access 16762 articles. 3 followers. Follow

4 Replies
960 Views

Similar Articles

[PageSpeed] 26

On Wed, 6 Feb 2008 08:00:03 -0800, John_ wrote:

> I am trying to take a date (e.g., 01/01/2008) and have Access tell me the 
> numeric day of the week (1-7) that corresponds to each date in a table.  
> Thanks for your help!

Look up the DatePart and the Weekday functions in VBA help.
=DatePart("w", [DateField])
will return 4 if the day is a Wednesday.
or...
You can also use the Weekday function
=WeekDay([DateField]) 
will return a 4 if the day is Wednesday.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
2/6/2008 5:08:43 PM
On Wed, 6 Feb 2008 08:00:03 -0800, John_ <John@discussions.microsoft.com>
wrote:

>I am trying to take a date (e.g., 01/01/2008) and have Access tell me the 
>numeric day of the week (1-7) that corresponds to each date in a table.  
>Thanks for your help!

Put a calculated field in the query:

DayOfWeek: Weekday([datefield])

By default Sunday is 1, Saturday is 7; see the VBA Help for Weekday if you'ld
like to change that convention.

             John W. Vinson [MVP]
0
John
2/6/2008 5:17:31 PM
That worked great!  Thanks!

"fredg" wrote:

> On Wed, 6 Feb 2008 08:00:03 -0800, John_ wrote:
> 
> > I am trying to take a date (e.g., 01/01/2008) and have Access tell me the 
> > numeric day of the week (1-7) that corresponds to each date in a table.  
> > Thanks for your help!
> 
> Look up the DatePart and the Weekday functions in VBA help.
> =DatePart("w", [DateField])
> will return 4 if the day is a Wednesday.
> or...
> You can also use the Weekday function
> =WeekDay([DateField]) 
> will return a 4 if the day is Wednesday.
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> 
0
Utf
2/6/2008 7:58:04 PM
Thanks so much.  That was very helpful!

"John W. Vinson" wrote:

> On Wed, 6 Feb 2008 08:00:03 -0800, John_ <John@discussions.microsoft.com>
> wrote:
> 
> >I am trying to take a date (e.g., 01/01/2008) and have Access tell me the 
> >numeric day of the week (1-7) that corresponds to each date in a table.  
> >Thanks for your help!
> 
> Put a calculated field in the query:
> 
> DayOfWeek: Weekday([datefield])
> 
> By default Sunday is 1, Saturday is 7; see the VBA Help for Weekday if you'ld
> like to change that convention.
> 
>              John W. Vinson [MVP]
> 
0
Utf
2/6/2008 7:59:01 PM
Reply:

Similar Artilces:

Econnect 7.5 DocDate format?
I'm trying to import invoices as AR transactions using the RMTransactionType class. The DOCDATE is a 16 character long string according the the econnect help. I'm setting it to the format of 07/19/2006. i.e. gpTrans.DOCDATE = DateTime.Now.ToString("MM/dd/yyyy"); The transaction imports fine and I can do an inquiry on the imported transactions fine. However, when I try to post the batch that the invoice was a part of, it gives a posting error that "The date is missing or invalid". When I manually create a transaction it posts fine. When I compare my m...

Upgrade from 1.2 to 3 fails
This is what i receive during the install of CRM v3, any thoughts? Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. Invalid object name 'dbo.MSmerge_contents'. Could not use view or function 'ctsv_2DCF979A518D4783A9A133A2F26C946D' because of binding errors. Invalid object name 'dbo.MSmerge_contents'. Could not use view or function 'ctsv_2DCF979A518D4783A9A133A2F26C946D' because of binding errors. Invalid object name 'dbo.MSmerge_contents'. Could not use view or function 'ctsv_2DCF979A518D4783A9A133A2F26C946D' because of binding...

Sumproduct date dependant
Is there a way to have data added using SUMPRODUCT, but adding the requirement that it falls between certain dates? -frank =sumproduct((daterng>a1)*(daterng<a2)*sumrng) "frank" <mrfrank73@aol.com> wrote in message news:03f501c36c17$864e4570$a401280a@phx.gbl... > Is there a way to have data added using SUMPRODUCT, but > adding the requirement that it falls between certain dates? > > -frank ...

Display Last Saved Date Excel
Hi I want a cell in the top right corner of my sheet stating the date and time it was last saved as I have a rather unstable network where i work and it crashes. I know this is available in Word and it's a similar kinda thing i'm looking for. Thanks Hi, I can offer a VBA solution, I don't know Excel worksheet funtion for this purpose. Public Function timestamp(filepathandname) timestamp = FileDateTime(filepathandname) End Function Place this function in a normal module and use it as a UDF. Format the result cell as date/time! Regards, Stefi „JonniP” ezt írta: ...

Printing only >60 day statements
Going to 'Routines | Sales | Statements' is there an easy way to print *only* >60 days? The only option I find is Past Due which is not what we are looking for. TIA -- Roy Sinnamond The only way I can think is to either use Report Modifier, modify the report and use VBA to access and print field on the report or use Crystal Report. In any case; the RM_Open (RM20101) holds the aging period (AGNGBUKT). AGNGBUKT>2 is >60 days past due amount. "Roy Sinnamond" wrote: > Going to 'Routines | Sales | Statements' is there an easy way to print > *only...

Office 2008 shuts down when I try to convert docx. to doc.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I put together a rather large document (40 pages with four pictures) as a docx. Now I'm trying to save it as a doc., but Word shuts down everytime I try. Is there anything I can do? thanks, chris Hi Chris - First confirm that you're up-to-date on OS X (10.5.4), Office (12.1.2), and that you've repaired disk permissions. Those are the primary causes of problems like this. If that doesn't resolve the issue we'll need more information about the file. It isn't so much the size - actually, 40 pages...

I want to convert an Excel Chart to a .tif format file
I have tried copying to PowerPoint and then saving as a tif, but the quality is not as good. I suppose the easiest way would be for you to download a copy of the free ASAP utility. As one of its options, you can convert an Excel file into a ..tif... others too. BR "rlbell" <rlbell@discussions.microsoft.com> wrote in message news:8F7D39D4-2E42-4FD2-99DF-5D8BB188F4E9@microsoft.com... >I have tried copying to PowerPoint and then saving as a tif, but the >quality > is not as good. Hello, Brad! You wrote on Wed, 2 Aug 2006 13:50:48 -0500: B> I suppose t...

Required Numeric
Is there any way with a formula to make sure a cell is numeric and to post an error message if it is NOT numeric??? =IF(ISERROR(1*F12),"F12 is not numeric","") "RTP" <RTP@discussions.microsoft.com> wrote in message news:633AF3FB-A6B0-45A1-AA4A-35A1AEFCD846@microsoft.com... > Is there any way with a formula to make sure a cell is numeric and to post an > error message if it is NOT numeric??? Click on your cell and then go to the 'Data' tab at the top menu and click on Validation. You can then select 'decimal' for what's allo...

Order by date
I have columns B6 to F6, where users will enter dates in mm/dd/yyyy format. Cells could be empty. Column A6 will contain names. I want to do an automatic update of the list as the user will insert/update dates. The sort order is needed to be in an Ascending order by dates. To do this the routine has to find the smallest date in each row, and then order the rows according to these smallest dates (smallest of these found dates first). If in any row there is no date, then this row will come first. Pls. help. ------------------------------------------------ ~~ Message posted from http://www.Exc...

Excel 2007
Hi, weird question. I've got several thousand single-cell alphanumeric values (e.g., CUSA000040, CUSA000041). Basically they range from 01 to 10,000. However, some numbers are missing from the sequence. Is there a way to have Excel show the ranges of values that *do* exist in this long list? As in, 40-150; 151-200; 205-4000, etc., with or without the alpha prefix? Thanks very much! Hope this question makes sense! Assume your values are in column A on Sheet1. Insert a new Sheet2 and in A2 enter this value: CUSA000001 and put this in A3: CUSA000002 Then select those 2 cells and d...

Display Date that does not change everyday.
I want to display the date when money is received, and I do not want the date to change everyday. Ex: =IF(K24="Yes",TODAY(),IF(K24="No",TODAY(),"")) However, this formula gives today's date and changes each day. I don't want it to change. See http://www.mcgimpsey.com/excel/timestamp.html Regards, Peo Sjoblom "heater" wrote: > I want to display the date when money is received, and I do not want the date > to change everyday. > > Ex: =IF(K24="Yes",TODAY(),IF(K24="No",TODAY(),"")) > However, ...

Why no more "Chart of the day: Asset and Liability"?
It is disappeared for several versions. This is my favorite. Monitor how much I have is my habit :> ...

error C2719 with VC7.1
Hi everyone, VC6 compiles fine, but 7.1 flags an error. C:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\include\vector(507): error C2719: '_Val': formal parameter with __declspec(align('16')) won't be aligned This appears to be related to my usage of std::vector in my classes. Also, I have this at the top of one of my classes's header #define MYD3DXMATRIX D3DXMATRIXA16 My usage of D3DXMATRIXA16 is not related to std::vector I never declare a vector<D3DXMATRIXA16> my_matrices; or anything like that. For those who don't know, D3DXMATRIXA16 is mean...

Numbered Procedural Steps Alignment
I am a technical writer who uses Word a lot to write user documentation. I recently upgraded to Office 2007, and there are several things that I find are very unfriendly for writing procedures. The procedural template I use for one company uses Arial 11 pt font for the document content, and Arial 14 pt for the number of the steps, as well as call out on the screen images. The initial indent level for the procedures is to have the number at 0 left margin and the content that follows it indented to .25". This works great for 1 - 9, but when I get to double-digit numbers, it...

Help with date formating when export to Word
My reginal setting (PT-Portugal) in Windows for "date" is: dd/mm/yyyy, instead of US normal setting of mm/dd/yyyy. In Excel, dates appear OK, like my regional settings (ie: dd/mm/yyyy). However, if I do a mailmerge into Word, using the sourcedata from excel, the date comes like US setting (mm/dd/yyyy), which is no usefuk for me. How can I solve this problem ??? Tks in advance for any help. Try the options here: http://www.officearticles.com/word/merge_field_formatting_in_microsoft_word.htm ************ Anne Troy www.OfficeArticles.com "a0relento" <a0relento@discu...

Date Formats don't work
I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY format. When I click on Format, Cells and Choose Category of Date and set it to the YYYY-MM-DD format, nothing changes. In fact if I set it to any date format, the format of the cells do not change. If I enter a value of '05/01/2003' and choose date format of YYYY-MM-DD, it does not change the format. What gives My regional settings are US, but I do have my date format settings as 'YYYY-MM-DD' with the hyphen as the separator. The values are probably text -- Regards, Peo Sjoblom "TdyYrLove&quo...

Restricting number of recpients a message can be sent to
Hi Folks Is there a way to restrcit the maximum number of recipients a person can send a meesage to, based on windows group membership? Ie: Group 1 can send to 10 users Group 2 can send to 500 users I know you can set this on a per user basis, just wonderig if there was a better way to do this for multiple users. Ta SuperPlay SuperPlay <SuperPlay@discussions.microsoft.com> wrote: >Is there a way to restrcit the maximum number of recipients a person can >send a meesage to, based on windows group membership? Not that I know of. -- Rich Matheisen MCSE+I, Exchang...

page numbering
Hi I am using Word 2007 - and have looked on the boards for answers but those I have tried haven't worked! I am working on a dissertation and have created sections for Title page/ Contents/ List of Illustrations and want {Page} of {Numpages} to start at Section 4 - Introduction and continue through the rest of the sections/ chapters (so far up to 11 sections). The first page of the Introduction is currently page 4 but I want to start the numbering there with page 1. I seem either to get numbering on all pages - or on the first page of each section and not the rest of th...

PCI DSS Compliance Standards for RMS 1.3R and previous versions
RMS 2.0 is listed as a compliant software for the PCI DSS Compliance Standards that were released in September 2007. But what about RMS 1.3R and previous versions? If RMS 1.3R is not compliant, is the retail store considered to be compliant if they are using RMS 1.3 and PCCharge as their third party credit card processing. (using a version of PCCharge that is compliant). Is it required to upgrade to RMS 2.0 in all scenerios to be compliant. Or is it required to upgrade in the scenerio that you are not using a third party compliant software? Thanks in advance. - Andy Miller - RVP B...

Convert HTML to String
Does anybody know of a way to quickly convert HTML to a string? I'm running into characters like ( =2E ) that I would like translated to the ASCII value, in this case ( . ) a period. Here's an example string I'd like to translate: =0D=0A <img border=3D"0" width=3D"739" height=3D"71" nosend=3D"1"= My method below seems kind of crude. Is there an easier way? Here's what I've gotten so far: findString = "="; while ( startPos > 0 ) { startPos = strBody.Find( findString ); if ( startPos > 0 ) { //...

Distribution List Max Number of Recipients
Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5 Thanks. "L. Wagoner" <anonymous@discussions.microsoft.com> wrote: >Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5? It's somewhere in the vicinity of 5,000. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Group header page numbering
Hello All Access Guru out there Would like to have some guide / code / example on how to reset a page numbering to 1 of each group? ie Total pages = 6 Group Pages Site 1 1 / 1 Site 2 1 / 1 Site 3 1 / 2 Site 3 2 / 2 Site 4 1 / 1 Site 5 1 / 1 thaks in advance Hi, Try this (But it's in french) http://officesystemaccess.seneque.net/ex_pagination_groupe.htm TopJB PWYS a �crit : > Hello All Access Guru out there > > Would like to have some guide / code / example on how to reset a page > numbering to 1 of each group?...

Can't find file from Windows 7 on XP
I saved a word document on my flash drive from Windows 7. I do not have a working printer so I am at the library trying to print it out but I cannot find the file. Is this because I am on a diffferent OS? I am on XP now. I should be able to pull up any file I was told regardless of the OS. Assuming that the computers at the library have a version of Word that can read the file, you should be able to print it. -- Stefan Blom Microsoft Word MVP "mosakitty" <mosakitty@discussions.microsoft.com> wrote in message news:7C155E4F-BE70-4A59-9090-2213C0A02DC7...

Parameter Criteria Date Picker
I have some queries that give Start and End Dates in separate criteria text boxes to select a range of dates. Can these text boxes be replaced by Date Picker control or have the Date Picker appear in the text Box? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 If you are using Access 2007, there's a built in date picker that should fire if you set the Format property of the text box to General Date, and leave the Input Mask blank. For any version, there's a simple little Access form here: http://allen...

dbx.fils convert to 2003
Type your question in this big white space, that way you might get a resolution "khalid" <khalid@discussions.microsoft.com> wrote in message news:6CAE7857-AE11-4825-8F47-2E9E1CD9F071@microsoft.com... > ...