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.
Does any one know how to calculate how many working days there are in a month
IE
Total number of days in a month minus Saturdays and Sundays
All contributions gratefully received
Thanks
I have a query setup:
SELECT Format([RepDate],"mmmm yyyy") AS [Date Reported],
inc_Incident.Category, Count(inc_Incident.Category)
AS CountOfCategory
FROM inc_Incident
GROUP BY Format([RepDate],"mmmm yyyy"), inc_Incident.Category,
inc_Incident.Status
HAVING (((inc_Incident.Status)<>"Cancelled"));
I'm trying to figure out the best way to get the query to return category counts per month (formatted MMMM YYYY) for only the last 3 full months (not including the current month).
If it matters Repdate is formated Long Date.
Any help people can offer?
Greetings,
I need to calculate with a query how long in days per month when someone was booked off sick.
e.g. booked off 12/05/06 till 17/07/06.
total days = 66
I need this broken up into months.
e.g. 05/06 = 19 days
06/06 = 30 days
07/06 = 17 days
Total = 66 days
Please help.
Thanks.
a request came accross my desk to find holidays sales in relation to the same days of the week for other weeks. For example, Thanksgivings sales compared to each of the 2 thursdays prior and the 2 thursdays after. Anyone know of a way of pulling this data so I have it all in one query - Thanksgiving, Thanksgiving+1, +2 and -1 and -2 (the numbers representing weeks from holiday
Thanks.
Brian.
Solution to parse the strings below and get number of months
1) 10 years 5 months 5 days - Should give 5
2) 6 years 10 months 22 days - Should give 10
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.
I am building a report and need to know the coding in the query (or in the report) to achieve the following.
Currently, in my query - 3 fields that work fine:
AppDate - dates of set appointments (Appt)
MonthNo: DatePart("m",[App_Date]) - get Month value
YearNo: DatePart("yyyy",[App_Date]) - get Year value
What I need is : EXACT coding to get these counts to show in my report:
January 2013
12 appointment (there were a count of 12 appts in January)
over 23 days (there were 23 days where appts were booked in January)
of 31 days (total number of days in the month of January)
February 2013
18 appts (there were a count of 12 appts in February )
over 19 days (there were 23 days where appts were booked in January)
of 28 days (total number of days in the month of February )
etc.
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)
in a text box, and it works fine.
But I don't know the syntax to get these numbers:
the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.
Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))
The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.
They are: the appointment date and its date parts - defined in numbers.
App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)
how to get my weekend work and not work total day count?
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
View 2 Replies View RelatedThis is fairly simple i think although i cant do it right i have a field in a query named; "shiftdates" which uses the format: DD/MM/YYYY, how can i set the criteria for this to only show records from the past 7 days?
i think i need something like: date()-7 (this doesnt work though), anyone help me out?, :o
I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?
To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.
If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.
I'm trying to make a label show if the date in my next service field is 10 days till its due
This is what I have on a on current form event this works great but would like the message to appear 10 days before the date is passed
Code:
If Me.NextService < Date Then
Me.overdue.Visible = True
Else
Me.overdue.Visible = False
End If
Also tried this with no joy
Code:
If Me.NextService < Date <= 10 Then
Me.overdue.Visible = True
Else
Me.overdue.Visible = False
End If
I have a query call courses and field name expiry date I have add additional field within the query to show how many day left and how many days over the expiry date
Code : DayLeft:[expirydate]-now()
but i just get a error when i run the query
I also would like to ask is this possible to be done within the table e.g adding another field dayleft and some how add formula to calculate number days left or over due .
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code:
=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")
D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
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');
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
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
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?
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
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
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.
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.
Is there a way to force the Date picker to show only Saturdays in any given month?
View 6 Replies View Related I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
Expr1: DateDiff("yyyy",[TBL_EmployeeDetails]![D_O_B],Date())
and in months but I would like to know how to return the difference in years and months.
I want to create a simple query from a list of orders dating back over 12 months.
The fields I have is Ord_Date, Qty
I need to show the orders by month for the last 12 months.
The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.
I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.