Adding X Months To A Date In Criteria Expression
Sep 4, 2006
Hi all,
I need to be able to add x months to a given date which I'm using in a criteria expression. I've figured out that I can just add y number of days, but the answers aren't quite accurate across different ranges of months.
I'm after something like
<[BeginDate] And >([BeginDate]+[3months])
but I haven't turned up anything useful in an hour of googling - finding it difficult to define what I want in search engine terms.
Cheers,
Alex
View Replies
ADVERTISEMENT
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Jul 1, 2005
Im looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
Forgot to post it:
>=Now()-Day(180)
View 1 Replies
View Related
Sep 8, 2014
I have a query I'm working with that finds data that changes, calculates the numbers, then prints a result in another column. The problem is that if I don't have any changes, then I have a blank result. Normally this wouldn't be a problem, but I'm making reports available to other users and would like this populated.
What seems simplest is to add an expression in the criteria field for the column that displays data after crunching numbers. Since this only has a display if something has changed, then I need an expression added if there is no data. Can I add something like an IsNull expression to display the results from another column in the same table? The data will end up being redundant, but I'm ok with that for now. the report should probably have the expression, but the column is already here in this table.
View 5 Replies
View Related
Nov 3, 2011
I am using the following expression in access 2007
=DSum("[jqty]","SHIP","[jprod#] = '" & [pn] & "'")
I need to add a date range criteria to this expression that looks at the field [expr1] and sums anything between the two selected dates.
View 5 Replies
View Related
May 11, 2015
I've been struggling with the following expression:
=DSum("[FieldName]","TableName","[FieldName]>=" & [Forms]![FormName]![textbox01]& " and " &[FieldName]<= " & [Forms]![FormName]![textbox02] & "")
I keep getting invalid syntax (Access 2007). I tried adding the # symbols but no success.
I also need to put an additional criteria for a Integer field in the same expression where the value is True.
View 5 Replies
View Related
Feb 8, 2015
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
View 11 Replies
View Related
Jul 31, 2013
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
View 3 Replies
View Related
Aug 24, 2015
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
View 2 Replies
View Related
Jan 27, 2015
I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
View 1 Replies
View Related
Jan 20, 2005
Not sure if this is actually done with a query (hell, im not sure if it can be done) as my Access knowledge is limited but I've got a list of Patient Appointment Dates and I need to calculate a list of 'Next Appointment' dates that are six months on from the appointment dates not including saturdays and sundays (as the health center is closed those days)
BTW this is for coursework, I don't really work for a health center :S
edit: I seem to have put this in the tables forum, not the queries forum. Apologies for that, I'm not an Idiot, Honest!
View 1 Replies
View Related
Nov 29, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 12 Replies
View Related
May 30, 2006
I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies
View Related
Apr 10, 2008
Hello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus
View 3 Replies
View Related
Sep 6, 2005
If I key in to the control source of a text box "=Date()-184" I will get the date 6 months ago but if I tried the same of 06/10/05 I will lose a day because of the 30/31 day months. Does anybody know another way around this?
View 6 Replies
View Related
Aug 6, 2014
We have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course. I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.
View 4 Replies
View Related
Jun 2, 2005
I Have a field that I need to manipulate the data in.
sample data follows [Field9]:
1 to 1 (cover page)
1 to 3 (no cover page)
etc..
this field identifies number of fax pages sent, eg
“1 to 1 (cover page)” is actually 2 pages, an attachment plus cover page
“1 to 3 (no cover page)” is actually 3 pages, 3 page attachment without cover page
What I want is just the total number of pages in this field.
I have written two expression that dissects the field in two, left hand side gives me a numeric number of attachments and right hand side a numeric value for the existence of a cover page or not.
Expr1: Mid([Field9],6,1)
Expr2: IIf(Right([Field9],15)="(no cover page)","0","1")
Each individual expression seems to do the right thing, but I have been unable to figure out how to add Expr1 and Expr2 to give me total pages.
Any help would be appreciated
J/
View 2 Replies
View Related
Jan 19, 2006
I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.
Thanks
View 14 Replies
View Related
Nov 30, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 2 Replies
View Related
Mar 14, 2006
I have a query that takes a date from a selection form. Is there a way to window my data by taking this date and going back 12 months to give me a block of data covering this 12 month window.
Example:
Date entered is 03/dd/06
Date window desired is 04/01/05 thru 03/14/06
View 3 Replies
View Related
Jan 14, 2007
Hello,
I have two fields in a table, both containing dates. I would like to write a query that returns the difference between the dates in an year, month, day format (i.e. 26y 2m 3d). If that isnt possible, can the difference be returned as years,days (i.e. 26y 63d).
Thankyou.
View 4 Replies
View Related
May 25, 2006
I am importing data to a program that requires the life of an item. This is in months such as 241 months. The valid format for this program is YYMM. 241 = 2001 or 20 years 01 month.
Another example: 16 months should read 0104 or one year 4 months.
Any idea how to convert this to yymm format based on the number of months?
thanks in advance.
View 4 Replies
View Related
Jul 21, 2013
in my daily roll call report i have 2 groups..."on program" and "graduates" these 2 groups are creating in the query...as u see in the 2nd pic... the expression as followed
Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated])
next you can see in my 3rd pic the report and the expression that gives the 2 groups there names...it is as followed
=IIf(IsNull([Date Out]),IIf(IsNull([Date Graduated]),"On Program","Graduates"),"Recent Departures")
i will clarify that i took out the names in the roll call but both groups are sorted by the date they came in going down the list..now i need to add another group "Staff Members" to my roll call.every way i have tried altering the query expression or the report expression result in a blank roll call.
View 14 Replies
View Related
Aug 28, 2013
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
View 6 Replies
View Related
Nov 27, 2012
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL
I want to return the month in <mmm yyyy> format.
View 2 Replies
View Related
Feb 15, 2006
I am running a query which returns daily sales numbers a also calculates a percentage increase. The query works fine until I try entering criteria to give any percentages that are >150 or <50, when running this no results are returned. Any help on this would be greatly appreciated.
Thanks.
View 1 Replies
View Related
