Get the latest Date value

[Alhakeem1977]

Hi All,
I need to set my vba code to get the latest Date entered in one of two tables in a feild called (txtStartDate), but I do not know how to achieve it.
to clarify it more, if the last date entered in tblHolArchived [HolidayDate] field then take it else if it's in the other table Holiday [HolidayDate] take the value.
By the way the [DeletedDate] and [CreatedDate] are DateTime " Now() "

If DLookup("[HolidayDate]", "tblHolArchived", "[DeletedDate]= date()") Then
Me.txtStartDate = DLookup("[HolidayDate]", "tblHolArchived", "[DeletedDate]= Date()")
Me.txtDF = Me.txtStartDate
End If

If DLookup("HolidayDate", "Holiday", "[CreatedDate]= Date()") Then
Me.txtStartDate = DLookup("HolidayDate", "Holiday", "[CreatedDate]= Date()")
Me.txtDF = Me.txtStartDate
End If

The earliest response would be highly appreciated.

Thanks a lot

 

[MarkK]

Try DMax(), not DLookup().
hth
Mark

 

[The_Doc_Man]

Alhakeem, use DMax to get the latest date and use the criteria argument to filter the input table to assure that the date is greater than 0 (which would imply a date of 31-Dec-1899).

However, your first paragraph is a slight bit confusing. You suggest there is a decision to be made but it is not clear as to what is the basis for same.

Your sample code uses DLookup twice on the same value. Create variables for the two dates, look up each one ONCE, and then do your comparisons against the variables or whatever else is required to see which one you wanted to keep (and put into Me.txtDF).

Further, if you execute that code, you might get nothing back from either call because in one element you are using Date(), but you tell us that the other values came from Now(). Using Now() includes time of day but using Date() always gives you midnight of the date. So unless you ran Now() EXACTLY TO THE SECOND at midnight, ... well, the odds are 86,400 to 1 against getting a match.

Don't take this wrong, it is not meant as a put-down, but your question seemed very disorganized. I respectfully suggest that if you can't clearly explain your question, part of your problem might be confusion of exactly what you are trying to achieve.

 

[arnelgp]

Dim var1 as variant
dim var2 as variant

var1=DMax("[HolidayDate]", "tblHolArchived", "[DeletedDate]>=date()")
var2=DMax("HolidayDate", "Holiday", "[CreatedDate]>=Date()")
if val(var1 & "") > val(var2 & "")
me.txtStartDate= var1
else
me.txtStartDate= var2
End If
Me.txtDF = Me.txtStartDate

 

[Orthodox Dave]

Sorry arnelgp but that doesn't work with the Val function.
When I tested your code I found that

val(var1 & "")

where var1 is a variant taking data from a date, the Val function takes the first numeric portion of the date and turns it to a number, ignoring the rest. In my case 10/04/18 became 10. I then checked with 27 days later (07/05/18) and this became 7. Regional settings are UK style, but the same applies to other regions.

I suggest your line

if val(var1 & "") > val(var2 & "")

is changed to

if var1 > var2 Then

 

[arnelgp]

Is that so then change to

If nz( var1,0) > nz(var2,0) then



Using var1 > var2 will cause runtime error when one varable is Null.

 

[Alhakeem1977]

Thanks a lot for your kind responses it works fine with the code you provided.