Hi all,
I have a problem finding the last date entered in a table..The purpose is to find the record which entered last in a table. Is there any way to find the last record with date ? I am attaching a sample db with this. Please look that if my question is not clear here..
Thanks in advance
thanks
Hi
I have many records say I would like to view a record by someone's surname and the Most recent date they were entered into the system (I have a field called date).
How would I do the latter (view by last recorded date)?
Can anyone help?
Cheers
Bikeboardsurf
I am making a Invoice registration DB. In the Form I have made a entry “VAT =” calcultated as “ NetAmount + VAT%”. The result shows in the Form, “NetAmount” and “VAT%” which are manually entries are recorded in the table but “VAT” is not recorded in the Table.
What could I be doing wrong?
I have an after update event that populates, Date, Time and User into corresponding fields when the record status changes to closed.
However... Whilst the information is recorded on the form it does not record to the underlying table is there any reason for this, or anything that I am not doing correctly?
I have a table, tblVisits, holding patient's pre and post surgery visits:
Code:
PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...
And another table holding patient's surgeries (each patient will have only one surgery)
Code:
PatientID SurgeryDate
1 4/1/12
2 ...
I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:
Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...
I've tried various MIN and MAX approaches and can't seem to get it right.
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).
I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.
I have put a Foreign Field in my Sales table for the Price key field.
My dilemma is matching the Sales with the Prices.
When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.
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 RelatedI am trying to create a Special Recorded tracking database where a member of staff will log the tracker number into the database and then at a later date someone can come and update the data, what I want to do is scan the tracker barcode with a scan gun and its details appear for updating, I can do this part but when I make any amendments to the data it doesn't save the new data and I don't know why? I am using a combobox to display the tracker number and me.text1.value = me.combo4.column(1) and so on and so forth to display the data, when I change this data it doesn't save.
View 1 Replies View RelatedI've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
I have a query based on an SQL database that our company uses to document problems and resolutions. I need to run a report that will show all the reports that were completed in the last 30 day. The field that the approval date is documented is called dbo_approved. Sometimes a report is approved and then at a later date reopened to add something then approved again. The design of this database is such that the first approval is one record and the second approval is a different record. So, using a normal query I will find some records that were previously appproved which I don't care about. I only want a list of reports that were approved for the first time within the last 30 days. Is there a criteria I can use in my query to only look for the first approval date?
As always I appreciate the help.
Jim
I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.
File #
setupdate
transaction
100001
1/1/2014
1
100001
3/5/2014
2
100001
5/5/2014
3
I've got an invoices table in my database, and I've got a payment approved checkbox that is false by default and a decision date field.
I want to find all outstanding invoices, and thought the best way to do this would be to check if the decision date is null, but that doesn't work.
How do I check for a blank field?
I have a date/time format field.
"8/21/2007 10:02:34 PM" is what my column shows.
What would I add to the criteria to the query to find all records added today?
at the end of the day my user needs to run a query that will show everything from today. Thanks.
I have a database with 8 years of info on customers orders. I need to find out when each customer first used our services.
View 8 Replies View RelatedI 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'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".
Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.
How this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.
It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.
How do I write a query to find the last audit date?
Let's say the table looks like this (year,company,audit date):
2004, Company1, 6/3/2004
2005, Company2, 2/2/2005
2006, Company3, 3/7/2006
2008, Company4, 3/2/2008
Let's say I want to add a new record (in the year 2010) and pull up the last audit date. How do I do that?
I'd like to further automate our invoicing system and need a field which has the last day of the month an item was completed.
Currently we have a field in the table called [Date Done]. I'm planning on adding a further field [Tax_Point].
I'd like the field to select the [date done] value and enter the final day of that month, unless, the final date of that month is in the future. in which case it would need the current date.
We create invoices at sporadic times of the month, and in the next month for the previous month; hence the need for a system date check.
Memo field is called [Notes] and data is like this...
5/05/14 - Perry was on another call. LM 2/05/14 - Perry only at centre in the mornings, need to speak to him before sending samples. 13/06/13 - Perry in a meeting. lm 30/05/13 - See Little Hampton Early Learning - s/w Perry, has already received sample and info 29/05/13 - s/w Aspi, said to cb tomorrow and speak to Perry
I want to find each date in the Notes field so I can split the memo field data into another table where the memo field will become multiple records that hold date, text and customer/prospect ID fields. The customer table was easy because there was a <Div> tag before each date. However in the Prospects table there are no tags so how to change my vba code to search for each date before I split off the data.
Here is the part of the VBA code I used to find the <Div> tag in the customer notes field. How to find each date in the memo field? The date is in d/mm/yy format?
If Not rst.EOF Then
Do
StrSplit = Split(rst![Notes], "<Div>")
For x = 1 To UBound(StrSplit)
Hi all,
i'm working with Access 2003 in the following scenario:
tblCARS
#####
COD, CARNAME
tblACESSORIES
##########
COD, ACESSORIENAME
tblACESSORIES-CARS
##############
COD, CODCAR, CODACESSORIE
* In this MDB, the Relationships (1-n) are:
tblCARS.COD = tblACESSORIES-CARS.CODCAR
tblACESSORIES.COD = tblACESSORIES-CARS.CODACESSORIE
Table Data's
#########
tblCARS have 2 records in COD and CARNAME fields:
1, Ferrari
2, Hummer
tblACESSORIES have 3 records in COD and ACESSORIENAME fields:
1, Light
2, Mp3 Radio
3, GPS
tblACESSORIES-CARS have only one record in COD, CODCAR and CODACESSORIE fields:
1, 1, 2
* This mean that: Ferrari have a MP3 radio... ok...
Now, i would like to construct a SQL that show me what ACESSORIES still havent found on tblACESSORIES-CAR, in this case would be 'Light' and 'GPS' to FERRARI.
For Hummer, the SQL should show all three acessories: Lighr, GPS and Mp3 Radio because none of these are recorded in tblACESSORIES-CAR for Hummer.
The Microsoft Access 2003 have a query-wizard that search for 'no-coincident', I already try it that only show me WHAT acessories still not recorded at tblACESSORIES-CAR. But this query do not show what acessories are 'missing' for FERRARI for example! If I specify CODCAR it dont show anything more!! Why?
I need (by my asp code) list for the car (can be Ferrari or Hummer, for example) what acessories I still can add! How to 'fix' it??
tks
Renato Jr
jrduetto@yahoo.com.br
I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:
I want my query to find the cumulative forecast up until a best before date.
This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.
I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.
How would I go about this?
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
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).