Show Records By Week Per Month

Feb 20, 2007

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!


Sheila

View Replies


ADVERTISEMENT

Show Records Only Where Date Is Within The Next Week?

May 26, 2006

Dear All, I have a field named: “reminder date” in a query and would like to show only records where the reminder date is within the next week whenever the query is run (ie if todays date is 26th may 2006 and I run the query now It would only show records that include the following dates 27,28,29,30,31, july and 1,2 June)

Ive tried the below bit of code but this shows to many records beyond one week.
>=DateAdd("d",+7,Date())

Anybody have any suggestions?

Regards

Jim

View 8 Replies View Related

Queries :: Sorting - Show Records From This Week Only

Apr 23, 2013

I'm doing a query and I want it to show records from this week only.

Is there a formula I can use in criteria?

View 3 Replies View Related

Reports :: Inserting Page Break To Show Week By Week Pages

Feb 11, 2015

I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.

The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.

I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.

View 8 Replies View Related

Week Of The Month Formula

Mar 3, 2006

I need a formula that will return the nth full week of the month that specified date falls in.

Example:
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 3 Replies View Related

Week Number Of The Current Month

May 24, 2005

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.

Thanks,

Edwin

View 2 Replies View Related

How Can I Convert A Week Number To A Month?

Sep 29, 2005

Help!!
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 1 Replies View Related

Modules & VBA :: Calculating Week Of A Month For Given Date

Mar 3, 2014

I have found multiple ways of calculating the week of a month for a given date. Now, I want to reverse it, i.e. given a month and week and day of week calculate the date.

Note that in week 1 and last week, there will often be days with no value.

View 2 Replies View Related

Modules & VBA :: Month And Week Criteria In A Module

Sep 23, 2013

I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].

Here are the criteria I am using:

Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())

Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1

Based on example, I expanded upon that and came up with this:

Code:
Function FundedPeriod(FD)
' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria
Dim ret As Boolean
ret=CurrentWeek

[Code] ....

Am I in the ballpark w/ this in order to return:

CurrentWeek or CurrentMonth or PreviousMonth

so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])

View 5 Replies View Related

UDF To Count Days And Convert To Month / Week / Day

Jul 18, 2014

Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.

Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.

I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.

Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.

View 1 Replies View Related

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

May 31, 2007

Hi

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

Thanks
Daniel

View 6 Replies View Related

Queries :: Comparison To Previous Month Or Week Depending On The Report

Dec 31, 2014

I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"

I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.

Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:

This month we did 250 units
Last month we did 300 units
so we did 15% less
"Yes my math is not exact"

They don't need to see last months data but i need a way to query the previous record to compare the data

This is the criteria code i use in the query that "Form B" is based on.

Code:
[Forms]![Form A]![Report_Start_Date]

I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.

How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.

View 3 Replies View Related

General :: How To Get Day Of Week To Show Up On Form

Aug 29, 2014

How do I get the day of the week to show up on form? when I set the default in the table, I want it to read Fri. 8/29/14 not just (8/29/14) or what ever day that field has focus.

View 1 Replies View Related

Show Date Range Instead Of Week Number

May 1, 2008

When using the Format:'yyyy mm dd' in access queries.
When trying to show a week is there a way to show the
date range instead of week number.

Week 01 = Jan1-7. If the results are only week numbers
that makes it hard for people to understand when it
actually is. Bottom line is: I would like to convert
Week number to actual date range. Can anyone help with
this?

View 9 Replies View Related

Reports :: Formatting Date To Show Day Of The Week

Feb 10, 2015

I'm setting up a query which will eventually run as a report to show sales within a specific date period and so on.

In my table "tblJobs" I have a field named "JobInputDate" - this will act as my date source, everything else included within the query works fine, i.e. costs etc, etc.

The format of the date field is Short Date and the default value is the date the entry was made to the table, this obviously uses the =Date() expressing to generate the date.

Now, when I come to building my query I want the dates to be shown as a day of the week, so that when I eventually build my report, I can group them by day.

I've looked through the various threads on here which all point to using the Format([YourDateField],"dddd") format. However when I run the query I get the "Data mismatch" error.

View 3 Replies View Related

Reports :: Query To Show Only Current Week

Dec 3, 2014

How can i make a conditional report that can give me some data from the table.

View 1 Replies View Related

Queries :: Show Total Ordered For Week By Fish For Single Customer?

Aug 19, 2014

I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.

View 2 Replies View Related

Show Month As Apr Instead Of 04

May 1, 2008

Below is the SQL for my query. The results will show month
year in a column that looks like: 03 2008. Is there
anyway for this to show as Mar 2008 instead. This
month is achieved by selecting a date range on a calendar
on the form. If the first date is 3/1/2008 and the last
date is 04/29/2008 then the query will show as:

03 2008
04 2008

I would like for it to show as:
Mar 2008
Apr 2008

Can anyone tell me how to do this?

SELECT
Format([TruckDate],'mm yyyy') AS [Month Year],
Count(*) AS TruckTotals
FROM TruckRegisterTbl
WHERE
(((TruckRegisterTbl.Company) Is Not Null)
AND ((TruckRegisterTbl.TruckDate) Between [Forms]![VisualInformationForm]![StartDateTxt] And [Forms]![VisualInformationForm]![EndDateTxt]))
GROUP BY
Format([TruckDate],'mm yyyy');

View 2 Replies View Related

Show This Month In Report

Feb 1, 2006

Hi There,

I tried the following to get this month/year automatically shown in my report (shown as the name i.e February 2006).

This is what I tried.
= MonthName(Month(Now()) -> did not return any value.

I tried with the expression builder:
Month («number») What do I need to fill in this string expression?

Thanks

View 2 Replies View Related

Datepart To Show Month

Sep 6, 2006

I know this must be simple…

How do you show the actual month from the results of using the datepart function in a query

Query xMonth:DatePart("m",[ContractStartDate],[MaturityDate])

I want to show Jan, Feb march not the integer value.

Cheers

View 1 Replies View Related

Show Last Month's Results

Jan 23, 2007

Hello All,

I am having problems getting the following lines to do what I need exactly. The formatting is kicking my butt basically.

Bob helped with these lines but I actually need the result to be last month. For instance it is now Jan. 07 and I need the query to total up the hours flown for Dec. 06. On Feb 1 07 I need it to now display Jan 07 totals and so on.

Query Field:
Format$([tblEntry].[tblDate],'mmmm yyyy')

Criteria:
Format(date(),"mmmm yyyy")

How can I change this to do what I stated above?

View 3 Replies View Related

Show All Dates In Any Given Month / Quarter

Aug 10, 2005

I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?

The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.

Thanks!
Tess

View 3 Replies View Related

How To Show All The Days In A Scpecific Month

Feb 12, 2008

Hi!

I want to show all the days in the specific month, e.g. there are 29 days in february 2008 and the records will display all the days that are available as below:
1/2/08
2/2/08
3/2/08
..
..
..
29/2/08

What is the syntax?

Thank you.

View 7 Replies View Related

Format Date To Show Month & Year Only

Oct 22, 2006

I have a need to enter dates so that they always appear as the first of the month. These dates are used in vlookup functions in Excel and need to be constantly recorded as eg 1 Nov 06. Currently I have to rely on notes and training to make sure users only insert the date in this way.

What I would like to do is get them to enter Nov 06 only (without a day) and have that stored as 1 nov 06. I have searched for date formatting functions and nothing addresses this. Perhaps it is something that could be achieved using VB if it is possible to do it.

Thanks for the help.

View 4 Replies View Related

Date Selection Criteria - Show Last Day Of The Every Month

Apr 16, 2015

I have a big list of data, with a row of data for every day for each client.

I need a selection criteria that will provide show the last day of the every month (historic data) for each client.

I've had a go but I'm not making much progress.

View 4 Replies View Related

Forms :: Date Picker - Show Saturdays Only In Any Given Month?

Nov 11, 2014

Is there a way to force the Date picker to show only Saturdays in any given month?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved