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 Replies
ADVERTISEMENT
Jan 24, 2014
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
View 2 Replies
View Related
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
Mar 5, 2014
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 Related
Jun 14, 2013
Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
View 1 Replies
View Related
Aug 8, 2005
Hi,
I'm looking for a module wich converts a date to a week.
Ex: i type 08/02/2005 and i obtain Week #2
(Some years have 52 and other 53 weeks, depend on the date of the 1st week of the year, an other problem is that some people say the week begins the monday, other say it begins a sunday)
Maybe a built in fonction exists.
Thanks in advance for help.
VINCENT
View 9 Replies
View Related
May 27, 2014
I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?
I have two fields - MobDate and DeMobDate
I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.
View 14 Replies
View Related
Feb 7, 2013
The client db is not Normalized. I understand their requirements and the current table structure has to remain in tact. The DATA ENTRY staff need to enter cost for Jan1 to Jun30. One of the many calculations that needs to be updated daily is the SUMPRODUCT from STARTDATE to ENDDATE - which can be anywhere between Jan1 to Jun30 at any time. The example scenario below needs to calculate the sumproduct of COST between any two fields (days of week).
For example, let's say a Purchase Order has been created, and the initial entry looks like this:
StartDate = Jan30
End Date = Jun3
Jan1 thr Jan29 Cost = 0
Jan30 Jun3 Cost = 500 (average daily burn rate)
Jun4 thr Jun30 Cost = 0
... and the staff receives and update to the daily spread. The Cost Spread between Jan1 to Jun30 NOW looks like this:
StartDate = Jan15
End Date = Jun3
Jan1 thr Jan14 Cost = 0
Jan15 thr Mar29 Cost = 1000
Mar30 thr Jun2 Cost = 3000
Jun3 thr Jun3 Cost = 500
Jun4 thr Jun30 Cost = 0
Here is the code I am using to calc the SumProduct upon initial entry WHERE the 'average daily burn rate' for the entire spread is the same every day (FYI, we need to calculate the YearTo-Date (YTD) and Remaining Balance from YTD for both Commitment and Incurred cost. For this example I am only snipping the code here to reflect Incurred)
'Initial Calculations
If CurrentDate <= frmStartDate Then
SUMPRODUCTYTDIncurred = ZeroValue
Else
SUMPRODUCTYTDIncurred = Round((DailyBurnRate + DailyBurnRate * NoDays), 2)
End If
If CurrentDate <= frmStartDate Then
SUMPRODUCTRemainingIncurred = ZeroValue
Else
SUMPRODUCTRemainingIncurred = Round(DailyBurnRate + DailyBurnRate * (DateDiff("d", CurrentDate, frmEndDate)), 2)
End If
In my UPDATE SCRIPT (not shown - cause its TMI):
- I am using the For Each statement to search an ARRAY to filter for the db.TableDefs("MYTABLE").Fields I need to update.
- I am also using a Do While True statement to catch the cost for the found field.
How to catch all the cost between Jan1 to Jun30 before updating the SUMPRODUCT field and then .MoveNext until EOF (remembering that the For Each statement will stop when field Jun30 is found)?
View 1 Replies
View Related
Jul 26, 2014
I created a database for an organization I volunteer for but I am running into design problems. I want the database to be able to assign preferred days of the week (that the volunteers want to serve) with the flexibility to change them.
The problem is in order to be able to keep my reporting options flexible. I will have to write a record (on a separate table) for each volunteer for every day they want to serve. A one to many relationship between the volunteer table and preferred days table.
View 5 Replies
View Related
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
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
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 6 Replies
View Related
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
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
Dec 23, 2014
I have a query result for example = 2.19 days worked. The result of cumulated hours divided by 8 (hours in the workday). What I want to do is take the .19 and multiply it by 8 (hours in the workday) and I would have the result in actual hours which is what I need. Or of course if there is a way to display that into 2 days and 1.52 hours Rounded up to quarters would be even better. But with spending hours looking through forums I didn't find a way to just work with the decimals right of the dot. Trim, format, Len didn't work for me, they all give me funny result.
View 7 Replies
View Related
Feb 16, 2006
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
View 5 Replies
View Related
Sep 7, 2005
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?
View 8 Replies
View Related
Jul 26, 2006
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.
View 1 Replies
View Related
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
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
Aug 2, 2007
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.
View 1 Replies
View Related
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
Dec 4, 2006
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
View 2 Replies
View Related
Apr 9, 2008
Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:
MonthName(Month([CompletionDate])) = [Enter Month]
but
MonthName(Month([CompletionDate])) <= [Enter Month]
does not work.
Thanks!
View 1 Replies
View Related
Feb 4, 2008
Hi
I am struggling to get a number to change to a month name.
When the user chooses a month from a drop-down:
Value List
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"
I use the month number to run the query, ie: 1, 2, 3 etc.,
But I want the Month Name to use in the subsequent report.
I have no other data in the query other than a month number to use so I thought about an invisible text box that get's the month number and I could reference it from the report, but I just can't get the text box to get the month name from the drop-down.
=Month([Month4Query].[Column](2))
Maybe there is a better, easier way?
Martin
View 3 Replies
View Related
Sep 15, 2013
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.
View 3 Replies
View Related
