I would like to create a query that would detect the most recent month and would list those records as well as records with dates from the previous 2 months. All dates are end-of-month values. 01/31/07, 12/31/06, 11/30/06, 10/31/06 etc.
List records with recent month and previous 2
4 posts
• Page 1 of 1
- ms windows
- Win32 Programming
- WINDOWS SERVER
- Windows XP
- windows live messenger
- ada
- visual objects
- python
- MS WORD
- Network Protocols
Jump to:
- 1. How do I detect #NUM! in a Query?
I'm linking to an Excel spreadsheet which has values that don't match the field format e.g. a date field type with value "new". When I link this spreadsheet it has a NULL value, but I see #NUM! in the field. I would like to be able to detect this error and ignore this in my Access query, am I able to? - 2. How do I put in date criteria to search for birthdays within a pa.
I am a novice. Please help. What wildcard is used for the year in a date field? I am trying to query for birthdays in a particular month for the employees. Thanks - 3. Help with calculations in query!
I am creating a report based on a query...part of the report is to find the total average for all years of study by each med school applicant. For example: Applicant 1 Start Yr - 2000 # of courses - 10 Average - 85 Start Yr - 2001 # of courses - 8 Average - 90 and so on... So now I am trying to use these fields to... 3) total the # of yrs of study per applicant (2 yrs in this case) 1) get the total # of courses taken for all study years. (18 for this applicant) 2) get the overall average for all study years combined. (87.5) Not sure if there are any Null fields...so would want to eliminate those as well. Thanks! - 4. Each month for a query
I have a database with month,day,year. I want to run a query using only one month at a time. for example, November month. What do I put in my criteria? - 5. Trincated query field
I know similar questions have been posted before but none seem to help with my problem. I have a query: "SELECT t_customer.produce_letter_date, combine_text([t_customer].[customer_no]) AS letter_text FROM t_customer WHERE (((t_customer.produce_letter_date)=get_letter_date()));" where "get_letter_date()" is a function that determines a date from a form and "combine_text()" is a function that returns text from various other queries. I know the "combine_text()" function returns the text in full when run on its own but when I run it within the query it truncates to 255 characters. Can anyone suggest why or how to fix this. Thanks
Next
List records with recent month and previous 2
by QUJN » Sun, 25 Mar 2007 00:49:29 GMT
RE: List records with recent month and previous 2
by SmVycnkgV2hpdHRsZQ » Sun, 25 Mar 2007 01:31:03 GMT
Put something like the following in the criteria of the date field. Replace
both "ex_date" with the field name and "tblDates" with the table name, You
might also need to change the -2 to -3.
Between DMax("ex_date","tblDates") And
DateAdd("m",-2,DMax("ex_date","tblDates"))
If you have a lot of records, like in the tens of thousands, the DMax
function could be rather slow. I would help if your date field is indexed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Re: List records with recent month and previous 2
by John Spencer » Sun, 25 Mar 2007 01:50:25 GMT
Field: YourDateField Criteria: Between DateSerial(Year(Date()),Month(Date())-2,0) And DateSerial(Year(Date()),Month(Date())+1,0) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .
Re: List records with recent month and previous 2
by Michael Gramelspacher » Sun, 25 Mar 2007 06:38:49 GMT
In article <65E2B6A1-09E9-435C-9D98-
XXXX@XXXXX.COM >, XXXX@XXXXX.COM
says...
maybe something like this would also work (Northwind Query):
PARAMETERS [Enter Date:] DateTime;
SELECT *
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=
[Order Subtotals].OrderID
WHERE Orders.ShippedDate Is Not Null And datediff("m",
0,Orders.ShippedDate) between DATEDIFF("m",0,[Enter Date:])-2
And DATEDIFF("m",0,[Enter Date:])
ORDER BY Orders.ShippedDate;
Similar Threads:
1.Show previous months reading on new months record
Good afternoon, I have been trying to figure out for the last three days now, how I can show the previous months fuel readings on the next months record. I don't know if I've been looking at it too long or what but I've hit a brick wall in trying to get this to work. My tables are: tblFuelTracking FuelId - PK FuelDate Location (10 different locations in all) tblFuelDips DipId - PK FuelId - FK DipMeterDate EndingDipReading EndingDipMeter I've tried subqueries, DLookups and anything else I could think of, but I'm really unclear how I can have my July 31st readings show up when I enter the August 31st readings for the same location. Could someone please give me some quidance on this before I commit myself? Any help would be greatly appreciated. Thanks kindly
2.Sum of Most Recent to Most Recent-12 months
I have been searching around for a week now and I still can't wrap my head around this problem, so any help is greatly appreciated! I have an attendence database, where employees get points when they aren't at work. I am trying to make a query/report that will find the most recent occurance of a point, and then sum the points for the employee from that most recent occurance to 12 months prior. My table looks something like: employee pointdate pointvalue Example Data Set: John 12/05/05 1 John 10/18/05 1 John 08/20/04 1 Desired Result: John's current points = 2 Can anything give me a starting point on how I should approach this? Thanks in advance!
3.Previous 12 months not corrent month.
4.deducting the previous month from the current month
I have a table with the following data month reading Jan 100 Feb 200 mar 300 I need to take the value from a current month and deduct the previous month. This needs to be done for all of the records within the field
5.Display records from previous month
I am trying to create a report that uses records from the previous month. The
approach I am attempting is to base the report on a query that has been built
to return the records for the previous month. The field name containing the
date is Due Date. In the query, I have used DateAdd("m",-1,Date()) as my
criteria under the "Due Date" field, but it returns nothing, even though
there are two records that should be displayed. So, my questions are:
1) Am I using an incorrect formula? I have tried a few combinations of
DateSerial functions, to no success. What would the correct formula be?
2) Am I taking the wrong approach? Should I build that filter directly into
the report?
Thanks,
JL
6. Filter for Previous Month's Records
4 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 15 guest