Calculating A Date Six Months In Advance Of A Date
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 Replies
ADVERTISEMENT
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
Aug 4, 2005
I have a very generic form I use to track all my To Do things on a daily basis. The form has the following fields: fldComplete, fldDueDate, and fldSubject.
The fldComplete is a check box and the fldDueDate is of course a date data type.
What I would like to create is a query that once you click the fldComplete signifying that the task is complete:
1. The record is removed from the form.
2. The fldDueDate advances either annually, monthly, quarterly etc. which automatically goes to the end of the form in sequential order.
Any help is much appreciated.
ChiefRetired
View 8 Replies
View Related
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
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 11, 2007
I have a query in which I have a long list of orders and their entry date and due dates. I would like to calculate the number of working days between the both in order to see if the supplier keeps his theoretical delivery term.
Can some one give me a way to achieve this in my query:
DUE DATE.........ORDER DATE.........DELIVERY
03-03-2007 -/- 24-10-2006 = working days
many thanks in advance
View 2 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
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
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
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
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 2 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
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
Sep 30, 2006
I have a table which lists rentals, all have start/end dates and a rental fee p.a.
I want to query this table to show rentals that are effective for any part of 2 dates, [Enter Start Date] and [Enter End Date]. The purpose of this query is to calculate rental income between these 2 selected dates.
Now I have this much working, but I want to group all the figures by month, in order to produce a graph to view the rental income by months.... this bit has me stuck....:confused:
How can I group all these records, when I only have 2 dates... for eg, start 01-01-06 and end 31-07-06...
I want to see figures for Jan06, Feb06, Mar06...July06etc... rather than just a single total figure for the entire period...
Any ideas?
Help! I'm completely stuck here and have spent hours searching the internet but haven't found anything suitable...
View 10 Replies
View Related
Jan 5, 2007
Hi,
i have a displayed(textbox) birthdate (txtDoB) and a textbox "input_date" (txtInputDate) inside form "form Two".
how do i calculate the years and months difference upon clicking the "OK" command button at "form Two"?
the result of the years and months will be displayed at a textbox (txtResult) at a form called "form Result".
thank you.
View 2 Replies
View Related
Jul 17, 2006
I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies
View Related
Feb 23, 2007
HI there,
I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.
I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.
Hoping someone can assist.
Many thanks
View 2 Replies
View Related
Oct 20, 2005
Hi all
I have a text box in a form which is displaying the date with a default value : =date()
Now what i want is another text box which will display for example 30 days after of the todays date.
Lats say today is 20/10/2005 the second text box will calculate 30 days after and will show 20/11/2005
Thank you.
View 1 Replies
View Related
Aug 19, 2007
Please help me calculate the age of a person at a particular date without having to go into the expression every year and change the cutoff date year.
A basketball player in our league must be a certain age as of Sept. 30 each year to play in leagues divided by age. I am using the following to calculate age:
age: Int(DateDiff("d",[bdate],#9/30/2007#)/365)
I want to get rid of the 2007 and replace it with an expression that will automatically change every year.
Thank you for your time and consideration
View 3 Replies
View Related
Dec 16, 2005
hi
i need a function to work out the first date of the month if i give it a date ...
e.g.
23/07/05
returns 01/07/05
im sure this is simple let me know!! thanks!!
View 1 Replies
View Related
