Formatting date to week

Hi All,
I'm trying to create a select query which have field date.
Now I want have a field which return the date value to
week format.How to create it.I want the week change
every monday at 7.30 am.How to make access achieve
it.Hope somebody like to help.


0
shiro
10/30/2007 7:13:12 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
552 Views

Similar Articles

[PageSpeed] 30

If by "week format" you mean the number of the week, something like below 
will work somewhat:

  TheWeekNumber: DatePart("ww", [TheDateField], vbMonday)

It does not take into account your need for it starting at 7:30am. If the 
date field has a time element, something like this may meet your needs:

  TheWeekNumber: DatePart("ww", [TheDateField] - 0.3125, vbMonday)
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"shiro" wrote:

> Hi All,
> I'm trying to create a select query which have field date.
> Now I want have a field which return the date value to
> week format.How to create it.I want the week change
> every monday at 7.30 am.How to make access achieve
> it.Hope somebody like to help.
0
Utf
10/30/2007 1:47:03 PM
Dear Jerry,
more explanation please.Can't we build it by using
exspression builder.Coding is terrible for me.


"Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message
news:61D41FFC-9959-4A9C-84FF-4C7C2D11B8F7@microsoft.com...
> If by "week format" you mean the number of the week, something like below
> will work somewhat:
>
>   TheWeekNumber: DatePart("ww", [TheDateField], vbMonday)
>
> It does not take into account your need for it starting at 7:30am. If the
> date field has a time element, something like this may meet your needs:
>
>   TheWeekNumber: DatePart("ww", [TheDateField] - 0.3125, vbMonday)
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "shiro" wrote:
>
> > Hi All,
> > I'm trying to create a select query which have field date.
> > Now I want have a field which return the date value to
> > week format.How to create it.I want the week change
> > every monday at 7.30 am.How to make access achieve
> > it.Hope somebody like to help.


0
shiro
10/31/2007 2:43:17 AM
Reply:

Similar Artilces:

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

new currency format
How can I add a new currency format? I want to have CCN appear before the amount. select your cell(s) format|cells|number tab|Custom category "CCN"0.00 Maybe???? Poison wrote: > > How can I add a new currency format? I want to have CCN appear before the > amount. -- Dave Peterson <Format> <Cells> <Number> tab, Click on "Custom", and select one of the currency formats that you like. Then, just select the currency sign in the "Type" window, and change it to CNN -- HTH, RD ---------------------------------------------------...

Entering Todays date in an excel spreadseet
This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a need to automatically save the current date to a cell in an = excel spreadsheet every time the sheet is saved. Today() always returns the current date, so once I open the sheet on a = new day I can no longer see the date the information was last updated, = i.e. saved. Any help would be appreciated. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/html; charset="iso-8859-1&q...

Time without date
I'd like the current system time displayed in a cell when I press 'enter' Only command I know is =NOW() which displays the date and time in a cell. How do you display only the time? AM PM Doesn't matter. I've tried looking it up in help but can't but can't seem to find the answer to my question. TIA =Mod(NOW(),1) and format as hh:mm:ss -- HTH Bob Phillips "Mickey Mouse" <.> wrote in message news:428af6f8$0$5178$afc38c87@news.optusnet.com.au... > I'd like the current system time displayed in a cell when I press 'enter' &...

date formatting 03-22-10
I have an excel sheet which has a date in yyyy/mm/dd format saved on the internal network. other users who open the file do not see this date as the same format as the saved file. could anyone provide as to why this is happening and what can be done to correct it. thanks vandy Sounds like you have not specifically formatted the cell as yyyy/mm/dd. When you don't specifically format a date cell, Excel will look at your Windows default setting and use that, so you will see a date as yyyy/mm/dd and someone else will see it as mm/dd/yy depending on their windows setting. T...

Date input mask
I have a form that has several date input mask: 99/99/0000;0;_ . I have them auto tab to the next field. Lately after putting in all my data I copy and paste into an excel spread sheet. Then I find out that some of the dates have 208 instead of 2008. I don't know how this can happen since I have to put in 4 digits in the year. I did do some testing and once in one of the fields it did put in 208 in the year part of the year. I think I put in 01/25/2008. If I try to only put in 3 digits I get an error message and it won't let me go on. Any idea? I didn't have this pr...

Data format
I am trying to run a query between 2 tables based on an account number. The problem I am having is that in one table, the account number is listed as ###-####, where as in the other, the account number is simple a string of 7 numbers. How do I either remove the dash in the first table or run a query that will allow me to match the 2 account numbers even though they are in different forms. I also need to match 2 other tables on account numbers. The problem here is that one field reads, for example, "C 123 4567 2.50 01/10/2007" where as the other reads 1234567. I need to ma...

[Entourage 2004] Show me the date!
Hi, How can I force Entourage 2004 (SP2) to sort mails by received and at the same time show me the receive date instead of weekday and time? It doesn't work to disable 'Use relative dates in lists (Today, Yesterday)' in General Preferences > General. It still shows me weekday and time instead of the date. -- Regards Madsen Under the "View" menu, set "Preview Pane" to "Below List" or "None". If the Preview Pane is set so that it is "On Right", the message list will always use relative dates. I agree that it is an unfo...

Report data needs to conform to db format
Each month I receive a report, say 200 lines (rows) 8 columns The information is grouped and separated as follows: Date Name Code Div Cost 05/15/04 Jim Bc West 20.00 05/20/04 John CB East 15.00 06/15/04 Judy BC South 35.00 Grp1 70.00 The sample above is a block of 3 the next block might be a block of 1, then 3rd a block of 5 or 7 and so on I'm currently manually Inserting a new column A:A and entering into A2:A4 the "Grp1" whic...

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

How to have a date range equal a particular month
What is the expression for converting multiple dates mm/dd/yyyy to the coinsiding month? It is according to how you are going to use the data but try this -- Format([YourField], "yyyymm") This will give you 200711 for this month. Format([YourField], "mmmm yyyy") This will give you November 2007 for this month. -- KARL DEWEY Build a little - Test a little "tvillare" wrote: > What is the expression for converting multiple dates mm/dd/yyyy to the > coinsiding month? Format(DateField, "yyyy-mm") as stri...

Creating a calculated field using dates in a form
I am trying to use dates from 2 fields in a form (HireDate-DOB) to calculate age. Then, using the age, I want to display a message if the age is less than 18. I think I have written a conditional statement correctly, but am unsure where to place it so that the message displays when the condition is met. I am totally new to Access 2007 and VB. You did not say what the content of your 'conditional statement' was and how you plan on using it. What has HireDate to do with it? Is it you want to see if their age is less than 18 on the date of hiring? Then this will c...

Conditional Formatting Problems HELP!
I used conditional formatting for the first time to display colors i cells based on the value of the cell. Everything works fine excep when I try to save I get the message "Excel could not save all the dat and formatting you recently added to nnnn.xls". Microsofts kb has an article that says you cannot exceed 2,050 row (Q215783), but I certainly don't have nearly that many rows. I do hav a pretty large number of cells with conditional formatting and wonde if there is some other limitation that I can't find. Everything els about this sheet is very plain stuff. Can anyone ...

NAICS Code
Hello, I have a list of NAICS codes formatted as numbers that I would like to format as hierarchical text. Current List 10 12 101 112 Desired List 10 101 112 12 Does anyone know how to change the cell format so that the "Current List" becomes the "Desired List"? Thank you in advance for your help. It looks like you could preformat that column as text and enter your values as text. Then you could sort the data as text. Some versions of excel will ask if you want to sort values that look like numbers as numbers. You should say no to that prompt. And just changing t...

When refreshed, some Pivot Table columns retain formatting, some d
I have not had trouble with retaining column widths and column heading formats (font size, word wrap, alignment) in Pivot Tabl. When refreshed, they retain formatting. Here's the weird part, when I add or insert a new data column into the Pivot Table by checking from field list, that new data column does not retain column heading formats (font size, word wrap and alignment settign) like the original columns do. It does however preserver its column width and number formatting. Why is that? How do I tell Excel to preserve column heading formats for newly added ones? I...

How do I save to Microsoft Word Document format?
There are three machines in our office with Outlook 98 or 2000 on them. The 98 and 2000 machines I use have Word set up as the email editor, and I can use "Save as" to save an email in Microsoft Word Document format, including the attachments. The other Outlook 2000 machine was set up to use Rich Text format as the email editor, and only had the choice of saving as .html, Rich text, or Text. There was no option to save as Microsoft Word Document format. I changed the default email editor to Microsoft Word. I went into Tools, options, Mail format, and selected the check box for Mic...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

iFrame and dates
Hi Guys, I'm trying to fill in a crmForm that has a date type field, from an iFrame. In the codebehind of the iFrame's aspx I'm using "parent.crmForm.all.<fieldname>.value=data where data is a CrmDateTime value, but the field does not appear. Please help, Thanks, Paulo Fonseca ...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

Creating Formatted Report and Emailing
I was wondering if anyone has any idea of how I might send out nicely formatted reports, where the report content is in the body of the email? I have seen a number of threads posted about sending attachments, and that will likely be my "fallback" approach. Trying to avoid attachments, however, as this required folks, who are already in email jail, to take an extra step, which results in them not seeing the report (my reports are communicating significant issues in ongoing projects, and important that they at least "See" the red flags in the report). T...

I type a date (1/05) in a cell, press enter, it reads ####.Why?
I'm entering data inMicrosoft Excel and am fairly new to Excel. I'm entering dates in cells in a worksheet and typred in a date and pressed enter and all it will do is read #####. Why does it do this and how do I get the computer to recognise the actual numbers of the date I entered? erg Probably the cell is not wide enough. -- Kind regards, Niek Otten Microsoft MVP - Excel "KIWI" <KIWI@discussions.microsoft.com> wrote in message news:D1F07478-F035-4DDF-8F77-5FC5A070155F@microsoft.com... | I'm entering data inMicrosoft Excel and am fairly new to Excel. I...

How can I add the current date to the Inventory Received Report?
I'm trying to add the current date to the Inventory Received Report that you can print out when you receive in a purchase order. The specific file I'm working on is "PORcv.xml". I'm able to edit text in the report, but I don't know how to capture the system date and get it to print out on the report. Is there a way to do this? TIA mbehm@hutchtel.net Does anybody have any suggestions for me? "Mike B." wrote: > I'm trying to add the current date to the Inventory Received Report that you > can print out when you receive in a purchase orde...

Date Time Stamp Formula
Hello, I am trying to create a formula that will insert the date and time into a new cell only when another cell is "0". In other words, if the specific cell hits 0, then the current date and time when the cell hits 0 is recorded in the other cell. If the cell is anything except 0, then the other cell is blank or says "not finished". I tried the if and now formulas to no avail and my macro is returning a VALUE error, so I'm completely lost. Any help would be much appreciated. Thanks, Katie Katie, It is a lot simpler with VBA This tests A1 and puts the date and t...

format date time combo on a form
Hi all. Firstly thanks to all those that offer advise on these forums. I am linking an access database to a csv file (that I pull out of contact manager because linking the two directly doesn't show all fileds). Anyway, I get a date value such as "dd/mm/yyyy hh:mm:ss AM" for one of the fields. Now i would like to put just the date into a report "dd\mm\yyyy" but no matter what i do, I always get the whole kit and kaboodle (i try mask etc). because the original data is linked I cant modify it so I have to figure out a way to do it on the report. any Ideas? thanks!...

year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...