I need to find the LATEST date in field Recdate that is 45 days or older in a query. I have tried <Date()-45, etc. but it returns all dates not just the last one. Does this require a range of dates to do this ? If not, how would I id the last date input ?
Thanks
I currently have a query set up which responds and filters by the date.
I use the current code in the criteria on the query for todays date less 7 days
Code:
DateAdd("d",-7,Date())
This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....
How would I go about doing that?
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
I've built an an update query that updates a Yes/No field "Expired?" to "Yes" if the expiration date has passed i.e. if the current date is newer than the expiration date. I thought this would translate into the current date being greater than the expiration date?
UPDATE Alert
SET Alert.[Expired?] = 'Yes'
WHERE Alert.Current_Date>Alert.Expiration_Date;
When I run this, it identifies the records to be updated, but then stops updating the fields due to "a conversion failure."
Querying a field for a number of days from today's date but not had a lot of luck finding how to query this from a date in the future.
Currently I have as criteria for the relevant field:
Code:
<[Period Start (dd/mm/yy - less 5 years from financial year end)]
which prompts for a parameter but using this I have to figure out the date 5 years from the future date first and then enter this.
Surely there must be code that allows me to simply enter the date, in the prompt and this then shows me data of 5 years or older equipment!?
I've run several searches and can't seem to find what I'm looking for, so here goes:
Have a table with several (maybe as many as 30) dates related to each employee, such as:
NAME-------CLASS 1-----CLASS 2----CLASS 3
J.Johnson.....10/16/05 ......12/17/05...12/25/05
S.Smith.........09/05/05......10/15/05
Not all employees will have entries in every class.
Need a query to result in most recent Date for each employee, such as:
--------------
J.Johnson......CLASS 3........12/25/05
S.Smith.........CLASS 2.......10/15/05
Any suggestions?
I have a table that shows me 3 fields:
PROCESS
EFFECTIVE_
OPERATION_
My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
This is what I need:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.
Thanks for any help.
I am trying to create a query or series of queries that will identify the latest record of a field called CheckInDate. The table will have multiple entries. For example.
Book#__________CheckOutDate_____________CheckInDat e
__1_____________01/01/2007_______________03/01/2007
__1_____________04/01/2007_______________05/01/2007
__1_____________01/01/2007_______________
__2_____________01/01/2007_______________02/01/2007
__2_____________02/05/2007_______________04/27/2007
__3_____________01/01/2007_______________03/01/2007
__3_____________05/01/2007_______________07/01/2007
I need the results to be:
#1(ALL) will not be listed, as there is a book still checked out
#2 will show the record with the 04/27/2007(ONLY), as I want the last time the book was checked out.
#3 will show the record with the 07/01/2007(ONLY), as I want the last time the book was checked out.
I currently have a somewhat flat table where this is concerned. Please advise on what I would need to do to create this. Thanks!
I am hoping someone may be able to help me with this one. I have a set of dates that I would like to query for the next latest date based on certain criteria. Not sure if I explain clearly, if more info is required, please advise. Thanks so much in advance for your help.
View 4 Replies View RelatedI have a simple table in Access 2003:
Case# (numeric)
CaseOpen (Date)
CaseClose (Date)
Item# (numeric)
ItemOpen (Date)
ItemClose (Date)
Some of the date fields may be null; If null then that date may NOT be selected
I am trying to write a query that returns:
Case#
Item#
Only the most recent date in any of the four date fields
The heading that matches the selected date field
ie:
Case# = 251
Item# = 4756
Most Recent Date = 7/8/2005
Matching Heading = "ItemOpen"
I have been searching and reading for two days-
Help, please?
TIA
I'm trying to sort dates by the latest date when the query returns multiple IDs with different results. Ex.
ID1 1/1/2006
1/8/2006
ID2 1/2/2006
1/9/2006
In this example I would want ID1 with the date of 1/8/2006 and ID2 with the date of 1/9/2006 since they are the latest date. I will have many IDs that I need to run a query on that will all return the latest date. TIA
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
Hi guys
I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.
What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.
I would also like to include a few pictures as URL links to the suppliers website ????
At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.
The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes
Someone please tell me how to display the full list of products but only the latest price/date
If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!
Thanks
Steve
I have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.
View 6 Replies View RelatedI would like for my report for default to the earliest and latest date ,should the user not select a start date and end date.
I have read about using MIN and MAX , but i am not sure how this would work.
Below is the code I have for a query. This query shows Processes, Operations, etc. Due to multiple Effective_ (Effective_ stands for Date) The Operations show multiple times. What I would like to do is have each Operation show only one time based on the latest Effective_ i.e. If Operation START-UP is listed three times due to three effective dates 2007-01-04, 2007-01-08 & 2007-01-17 I would only like to the the one associated with the 2007-01-17. Is there a way to accomplish this in my query?
SELECT ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
FROM ASSYROP INNER JOIN ASSYWC1 ON ASSYROP.OPERATION = ASSYWC1.OPERATION
GROUP BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
ORDER BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.EFFECTIVE_;
My table shows
ID ID_Clients EndDate
A particular value of ID_Clients can recur many times, but with different EndDate value each time.
I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate
Any help with this would be much appreciated.
-Thanks
I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.
I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.
I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.
Hey Guys & Gals, extreme newb here attempting to display the latest activity date in an SKU activity db in a query that spans 12 months with multiple activity dates on each of the 1,200 SKUS.
I have this so far and of course it only shows the last (DMAX) activity, not SKU related. How can I relate this result to each SKU to diplay the latest activity for each.
SELECT [Usage06-07].workorderDateClosed, [Usage06-07].qty, [Usage06-07].itemNo, [Usage06-07].Item
FROM [Usage06-07]
WHERE ((([Usage06-07].workorderDateClosed)=Dmax("workorderdateclosed","usage06-07","item")))
ORDER BY [Usage06-07].Item;
Thx in advance for your patience :)
Joe
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
I'm trying to create a query that has a calculated field that shows a maximum value from multiple fields.
As far as I can find, this is not built into Access, so I've used this code from a Microsoft page:
Code:
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
[code]....
The problem I'm having is, well this doesn't work for me.I'm a bit of an beginner VBA coder, but I understand this code and don't know why it won't work.It only displays the value from the field within the brackets.The values used are Dates, so I need to display the latest date from multiple fields.
How do I query from this table:
ORIGINAL_table
FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|
The output will be:
FName | END_Date|
----------|---------------|
John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
The query table(output) will just have to be latest date("END_Date") per "FName"
I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )
I try using max but still the result is not what i am looking for...
I am trying to make an on-click event that would open a form showing the last inspection done on a site.
Unfortunately, I cannot even first create a dlookup function to use, so I haven't even attempted the rest!
The data needed to reference is in one table, and I just...can't... quite get it.
Here is my last attempt (which at this point probably isn't my best )
Code:
=DLookUp("InspectionID","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "' AND InspectionDate = #" & DMax("InspectionDate","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "'") & "#")
After breaking it apart I'm pretty sure the DMax function (and using date?) is the culprit, but I can't figure out why.
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).