Get one before last date with vba

[Auto]

i would use something like this

dim MyDate as date



MyDate = DMax("DateEnter","Trans", here i want the criteria not in Dmax("DateEnter","Trans","") as orange did in SQL
how can i get that?

[orange]

I reread your post

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

I have attached some vba that uses my table Ancestor (see top of attached jpg for existing records)
The sub is called SecondLast.
The result in variable "YourVariable" is shown in the immediate window.

[Auto]

thank you very much all of you, i was missing the rs! that's why it didn't worked.
again thank you orange for your kind help, and itsme for your advice, its now working.