Get one before last date with vba

[Auto]

hi, i am having a table with transaction details in it, every transaction has a date, now i want from vba to get the date before the last date,
i know that dmax returns the last date, but how can i get one date before that?



can anyone give me any tip?

[ItsMe]

I didn't test it but maybe

Dim dtFind as Date

dtFind =DMax("Something", "Something") -1

Then you can use dtFind as a filter or Where Criteria or .....

[Auto]

no, -1 gives back 1 day back from last date, not going back one record, its going back one date
i need it should go back one record

[ItsMe]

That does not make much sense. If you sort the order by date then you will get a random record if the max date has more than one record.

Perhaps you should try building a query and sort by date then by PK or something.

[Auto]

duplicate dates is not allowed, so thats not an issue

[ItsMe]

There is more than one way to go about it. Why don't you give some info on where this info is relative to when you need it. For instance, are you searching for a record within a form's recordset? Are you trying to open a report that is the second to last record? Is there a form open and now you want to find information in a table outside of the form's recordset nut relative to an ID or another field within the recordsert?

You can use SQL or DAO or a combination of the two to sort the order and find the record you want. Since there are no duplicates, you could SELECT TOP 2 with an SQL string

[Auto]

ok, i know of the SQL TOP 2, thats bringing results, but i cant use SQL, since i need it should return a value, and then set it to variable, so thats why i asked for a vba idea

now in terms of forms, yes its a populated form that needs to get the info when form is getting populated, so it needs to lookup that date, and get the value set that to a variable, and then manipulate with it in vba, in terms of ID, there is no ID in that table.

[ItsMe]

Maybe something like this then. Where DateField = the name of your date field and tbl = the name of your table

Dim dtField As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT tbl.* " _
                         & "FROM tbl " _
                         & "ORDER BY DateField;", dbOpenDynaset)
rs.MoveLast
rs.MovePrevious
If Not IsNull(rs![DateField]) Then
dtField = rs![DateField]
End If
rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print "DateField = " & dtField

[orange]

SQL query along this format should work

SELECT max(birthdate) from yourTable 'This done last and picks the second latest date
where birthdate
NOT IN
(select max(birthdate) from yourTable) 'This is done first and selects latest date

Adjust the fields and table name to match your data.

[ItsMe]

SQL query along this format should work......

The way I understand it, OP does not want the MAX value but the neighboring record to the Max value.

[orange]

As I understand the OP he wants the second latest Date.

EG Consider this list of Dates
21/3/1944
31/12/1955
12/6/1967
17/9/2001

I think the OP wants 12/6/1967--and that's what my example produces. But the OP has not given an explicit example of his expectations.

[ItsMe]

@ Orange

I see it now. Are the single set of parentheses in the SELECT within the WHERE enough? I am not great with SQL and asking because you have parentheses around birthdate in the first line of your code too.

[orange]

SELECT max(birthdate) from yourTable 'This done last and picks the second latest date
where birthdate
NOT IN
(select max(birthdate) from yourTable) 'This is done first and selects latest date

ItsMe:

max is a function that takes a parameter. The parameter is birthdate. This is a standard vba syntax function(parameter) .

There are 2 SELECTs in this query --- a compound query if you will that contains a subquery

The subquery
(select max(birthdate) from yourTable) is executed first. It selects the max(birthdate) -- in this case 17/9/2001

The base query then runs and SELECTs the max(birthdate) that is NOT IN the result set of the subquery.

In other words it is Max(birthdate) that is not 17/9/2001.

[ItsMe]

Thanks, I was curious about the parentheses.

[Auto]

ok, again, orange you understood exactly what i want, but as i said before i cant use SQL, i know SQL can give me that with MAX and also with MIN in TOP 2 query, but as i stated above SQL will not return a value that u can then assign to a variable, it will produce an error.
but using DMAX u can get a value out of it, and assign to a variable, but the question is how i can play with DMAX for that purpose.