DatePart Help Please
Oct 15, 2006
If I can get any suggestions I would appreciate it. I need to run a report on total number of calls, daily and I need to sort it by week. I used the Sorting and Grouping options and I can sort them. what I need to accomplish is to have the week start on a Monday instead of the default Sunday. When the first data of the week does not start on a Monday, the heading date of that week will be the date of when it is first recorded. So, if i have no calls on Monday or Tuesday but there are records for Wednesday, it will have Wednesday date as the heading for that week. I went through the help online for Access and it mention I can create expressions to help using the Datepart heading. But, it didn't recognize the expression. Does anyone know how to solve this problem?
View Replies
ADVERTISEMENT
Dec 13, 2006
Have been unable to find any solutions for this in the forum or the help files in Access.
Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].
I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().
With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.
My query:
ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007
Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.
Has anyone had to do this before?
I have tried this in the criteria of my SavingsMonth field:
[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))
But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"
Does anyone know if this can be done?
Thanks,
Toni
View 3 Replies
View Related
Jan 19, 2007
Here's the deal, I have a table that has a flag in it. It's either a 1 or a 0.
Expr1: IIf([Is it?]=1,[REQ DATE],IIf([Is it?]=0,(DatePart("yyyy",[REQ DATE])),"ERROR"))
If it's a 1, it displays the full date. If it's zero, it's supposed to display the year of the date.
The first part works fine. If it's a 1, it shows the date. However, if it's a 0...it shows a date not even remotely close...and they're all the same. 06/28/1905.
View 2 Replies
View Related
Sep 5, 2007
Hi
Can anyone advise on the following query problem:
I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.
In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)
Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.
View 7 Replies
View Related
Jun 2, 2007
I am using a DatePart function to get the week number for various dates in my project (DatePart("ww",[date],vbsunday)). Is it possible to set the firstday setting of the function (vbsunday) by referring it to a field in another form. I have tried but get an error. I am hoping to achieve this because the database will be distributed to various agencies which have different first day of the week for their schedules. I want to avoid re-writing the code for each agency. Any help would be greatly appreciated.
View 3 Replies
View Related
Jun 27, 2005
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
View 12 Replies
View Related
Jan 19, 2006
Hi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date but to no avail.... can anyone help???
Many Thanks in advance
Jason
View 1 Replies
View Related
Jan 19, 2006
Hi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date]) but to no avail.... can anyone help???
Many Thanks in advance
Jason
View 2 Replies
View Related
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
Nov 1, 2005
Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???
View 7 Replies
View Related
Apr 28, 2006
This is my first thread so be gentle.
I curently have a query based on a payments table. In the payments table there is a field called date (when the payment was processed).
I want to be able to display, in my query, the quarter that this payment was made in. It is based on financial quarters so quarter 1 starts on April the 1st.
The default of firstweek seems to be January the 1st. Can I change it to April the 1st - if so how?
Many Thanks
View 3 Replies
View Related
Jun 2, 2005
When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.
Any ideas?
View 11 Replies
View Related
Apr 26, 2013
Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.
The below text box is in a form pulling the data from a query.
Text Box
=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")
View 6 Replies
View Related
