Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.:D


View Replies !   View Related
Auto Number, Year, Month Number Combination
I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View Replies !   View Related
Week Number Of The Current Month
Hello everyone.

I would like to ask for help on my current problem.

I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.

for example:

May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1
May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2
May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3
May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4
May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5

Same goes for all months.
Is there a function to determine which week number of the current month the input date falls?

Please help.



View Replies !   View Related
How Can I Convert A Week Number To A Month?
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??

View Replies !   View Related
Year To Date Totals, Month To Date, Week To Date
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View Replies !   View Related
When The Last Week Of The Year And The First Week Of The Next, Are The Same!
I have the following expression which works nicely to group all of my dates into their respective weeks of the year, by week dates beginning Mondays:

WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.

Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.

How can I adjust my code to get one total group of dates for the week of 12/26/05?

Help greatly-needed!....

View Replies !   View Related
Leading Zero In Week Of Year
I am using the format below to give me Year-WkNo and the leading zeros are not being put in so when a report is ran the yyyy-ww is not sorted correctly. Is there a way to get Jan 2007 week 1 to come out 2007-01?

YearMonth: Format([F Jobs Stats_sub2].[MaxofWork_Date],"yyyy-ww")


View Replies !   View Related
Week Commencing Day From Week Number
I have a query that groups records by date into week numbers using the following expression.

WeekNumber: DatePart("ww",[Date],2)

I would prefer to display the result as the week commencing Monday date.


View Replies !   View Related
Sort Date Formatted As Year And Week
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.

How can I get Access to sort in ascending order correctly on the formatted date?


View Replies !   View Related
Week Of The Month Formula
I need a formula that will return the nth full week of the month that specified date falls in.

03/31/2006 would be "5" - the fifth full week of March
20/13/2006 would be "2 - the second full week of February

I've tried using a formula that subtracts the week number for the 1st of the month from the week number the date falls into, but it's just not working as I expect it to.

format([ETA],"ww",1) - format(date(format([ETA],"m",1) & "/01/" & format([ETA],"yy",1),"ww",1)

View Replies !   View Related
Calcute Week Numbers Starting From 1 July Of Current Year
Hi everyone,

I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.

The starting day of the week is Wednesday.

I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.

I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.

So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.

Not quite working yet. Here's what I've come up with so far.

WeekNumberCount: DatePart("ww",[DateCalled],4)

I use the following to calculate the "my" starting week of the year:

StartingWeekInYear: DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)

Then I add the two together together:

MyWeekNumberCount: DatePart("ww",[DateCalled],4)+DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)

I know it needs some work to be useful for all years. Any suggestions?

View Replies !   View Related
Show Records By Week Per Month
Good day!

I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)

GroupNo______Week1_____Week2_____Week3____Week4___ _Week5___MTD
GroupA________12_________2__________10________11__ _____15_____50
GroupB_________8_________5___________7_________1__ _____21_____42

I can't find a sample code for this particular date criteria. :-(

Thanks in advance!


View Replies !   View Related
Number Fields In Text To Number Field, Formula For Fiscal Year
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?

There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.

Thank you

View Replies !   View Related
Date - Month And Year Only
can i change date format that contain day, month, and year to month and year only..
i try change at fromat at porperties, but it change back into dd/mm/yy at combo box..
this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..


View Replies !   View Related
Month / Year Queries
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:

Format([date],"mmm - yy")

And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.

How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???

:confused: :confused:

View Replies !   View Related
Month End And Year End Reports
For the record, I have indeed searched the forum for help with this, but no luck so far, so here goes. (C++ programmer, little background in access).

I have a table that stores a person's name, gross amount spent, gst spent, pst spent, and total amount spent.

I need to be able to create a query or something that asks me to enter a year, and it will sum up the amounts, gst, and pst over the course of that year, and print it neatly on screen.

Also, I need to be able to create a similar query that asks for a year and a month (does access possess Java's ability to easily create a pop-up calendar applet-like instance that provides a GUI for selecting months/years, or does the data need to be entered through a command prompt?) which will create a month-end report in the same manner, but only spans a 1 month period?

Any information that could be provided to me, even a means of getting started on this problem, would be great. Thank you and cheers everyone!

View Replies !   View Related
Distinct Month AND Year
if i have a table like this:


i would like to extract only the distinct month and year date, so my query should return


can anyone plz help me with the sql query? thanks

View Replies !   View Related
Grouping By Month & Year
I have another...

How do I group the dates on my query to show only a certain month and year?

View Replies !   View Related
Return Month And Year
Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.

The report gets its information from a query. In that queries criteria field is the below expression that I have.

Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)

Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.

Your help is greatly appreciated.


View Replies !   View Related
Grouping Date By Year And Month In ADP+SQL
Hi all.

I have chart in ADP project/MS SQL Express and I want to see in chart table (date Field, data field) grouped by year and month: 2005.01, 2005.02, 2005.03....
I try this str = DATENAME(yyyy,date)+ DATENAME(m,date), SUM(data) FROM table GROUP BY DATENAME(yyyy,date)+ DATENAME(m,date) ORDER BY DATENAME(yyyy,date)+ DATENAME(m,date)

problem is with sorting: 2005April, 2005februar,2005,Januar - its sorting not by date, but alpabhetic.

Please, help with other grouping way!

View Replies !   View Related
How To Extract Month & Year Together From A Date
Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.

Any idea ...?


View Replies !   View Related
How To Get Year And Month Value From Date Field
hi friends,

I want to get year and month field of datetime field so that i can get data by comparing them with other table fields.

ex: date field column contains 1/28/2005 as data
how to get year(1/28/2005)
how to get month(1/28/2005)

expecting your help


View Replies !   View Related
How To Format Date From Year Month And Day?
I have a table a
year month day
2005 1 1
2005 3 1
2006 5 1
I want to generate the associateed
which funciton I can use to do that?


View Replies !   View Related
Display Month And Year On Form
I have a reports form where the user can enter enter a from date and to date for the report criteria. I have an unbound field on the report which i want to display the month and maybe the year in which this date criteria is in. This will be needed to display on the actual form. Say if the date range is between 01/01/05 and 31/01/05. I need the field to say January 05.

Hope this makes sense and can be done

View Replies !   View Related
Month & Year Drop Down Menu?
I have a field that is:

Current length of continuous allocation of Care Co-ordinator. ( years and months )

Ideally I would like to have two drop down menus - one with years and one with months.

Would I have to have two fields in my table - one for years and one for months or can I have one?

Whats the best way?


View Replies !   View Related
On My Form Day/month/year Has Switched
On my form day/month/year has switched to year/month/day. On a table there are three separate date cells. Order Date, Shipping Date, and Report Date. They are each formated to medium style. At first I also used an input mask then I found that Access would convert to the format I choose. I had missed one of the input masks during the removal and when I removed the last input mask Access now converts to yy/mmm/dd. What just happened and how do I reverse this? It is more convienant for some of the users to not have the input mask but the order has to be uniform.

View Replies !   View Related

Copyright 2005-08, All rights reserved