The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.
I have done this before many years ago but now have a mental block and cant figure it out.
Any help with this would be greatly appreciated
Good morning all,
I currently have the following expression in a query, to go back 6 months from the current date.
Between DateAdd("m",-6,Date()) And Date()
Does anyone know of a way from the current date, go back to the end of the previous month ( 30th, or 31st) then go back 6 months?
hope that made sense.
Thanks
Kevin
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
Hey
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
I want to convert my dates to months so I added the statement below in the Field section of my query. It is not working. What am doing wrong?
Month:IIf([dateActivity]>=7/1/2004,IIf([dateActivity]>=8/1/2004,IIf([dateActivity]>=9/1/2004,IIf([dateActivity]>=10/1/2004,IIf([dateActivity]>=11/1/2004,IIf([dateActivity]>=12/1/2004,IIf([dateActivity]>=1/1/2005,IIf([dateActivity]>=2/1/2005,IIf([dateActivity]>=3/1/2005,IIf([dateActivity]>=4/1/2005,IIf([dateActivity]>=5/1/2005,IIf([dateActivity]<=6/30/2005,”June”,”May”),”April”),”March”),”February”),” January”),”December”),”November”),”October”),”Sept ember”),”August”),”July”))
Thank you.
Hello to everybody.
I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads
http://www.access-programmers.co.uk/forums/showthread.php?t=67472
http://www.access-programmers.co.uk/forums/showthread.php?t=89051&highlight=calculating+number+months
which either method would suit me as the accurate answer is what I am after.
My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.
Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.
I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.
Many thanks in advance
Ian
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
Find the difference between two dates is simple if you're simply looking for the number of days between them. However, if you're looking to express the difference in a bit more user-friendly way, you can use the following code to calculate the difference in years, months, and days. The code accounts for zero values, plurals and the Oxford comma. Further, it automatically sets the later of the two dates to the End date, so the dates can be used interchangeably.
Examples:
June 4, 2010, July 3, 2012 returns "2 Years and 29 Days"
June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day"
June 4, 2010, June 5, 2010 returns "1 Day"
Code:
Function DiffOfTwoDates(dtmDate1 As Date, dtmDate2 As Date) As String
' Written by Will Knapp, Freelance Access Developer, 2013
Dim dtmStart As Date, dtmEnd As Date
Dim strDiff As String ' Resulting String
Dim yDiff As Integer ' Year Difference
Dim mDiff As Integer ' Month Difference
Dim dDiff As Integer ' Day Difference
Dim CommaLoc As Integer
[code]....
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.
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]))
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
Hello mates, :cool:
I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P
Hi,
I am trying to create a query that will show data in a rolling four month period. Example: For September, show June through September; for October, show July through October.
I am using a general date format in the field. I can use >DATE() - 120 to get a 120 day period, but that obviously does not give me data for the actual Calendar dates. Is there a better way?
Any and all suggestions will be greatly appreciated.
Thanks in advance,
Jake
Can this be done?
I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?
For example, total savings is $12k and effective date is 2/1/2006. I need to show $1k in each month field in my query from 2/1/2006 through the month of 1/1/2007. I've tried DateSerial and some other methods but I can only get it to give me the divided amount in the effective date column and nothing else. Is DateSerial the way to go?
Thanks,
Toni
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
How do I write an expression to pull data greater than six months from a user entered date.
View 3 Replies View RelatedSearched and can't find the specific answer to my age/month question so I hope you will bear with me.
on my form I need to calculate someones age and show it in years & months, then display the age (years & months) in the format 7.5 ( i.e. 7 years 6 months displays as 7.5 ).
I also need to round that figure down to the nearest 0.5 of a year so 7.4 would display as 7 and 7.9 would display as 7.5.
Any help would be very gratefully received.
Cheers
RussG
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
I need to (accurately) calculate a rental period to fractions of a month.
ie) 1/1/2006 to 2/14/2006 would equal 1.5 months.
DateDiff("m",[Start],[End]) returns whole months.
DateDiff("d",[Start],[End])/30 is very close , but not precise.
Is there a way to accurately calculate fractions of a month?
Thanks,
Sup
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
Bowes
I have been through this board looking for an answer, I've come close but not quite getting it.:confused:
I (obviously) need to count how many records each user has completed (easy)
I need to count how many they did in a months time. I need to report multiple months in one report.
A little back ground on me...I only know the Query Design view and it's pretty limited. I have tried to move into the SQL view and alter the information there but it's not working in my favor. I also tried changing the format in the date properties to mm/yy but it's still sorting by the full date and time. :mad:
Any help would be awesome. Here's a copy of the SQL statement if it's easier to just change it there. Thanks in advance for the help.
SELECT Count(qry_QuotesIssued.Quote_Number) AS CountOfQuote_Number, qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date
FROM qry_QuotesIssued
GROUP BY qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date;
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...
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN