Solved Get the last value from a query using VBA

[zelarra821]

Good afternoon.

I need to get the last value as of today in a this query:



I try like this and it doesn't work:

Nz(DLookup("Registro", "CProximasCitas", "Fecha>=#" & Format(Date, "mm-dd-yyyy") & "#"), 0)

It returns the value of today, that is, "Pesada", and it would have to return "Médico de cabecera".

I have tried in various ways, but none satisfactory.

Can you help me? Thanks.

 

[theDBguy]

Hi. You'll need a nested expression. For example:

DLookup("Registro","CProximasCitas","Fecha=#" & Format(DMax("Fecha","CProximasCitas","Fecha<=Date()"),"yyyy-mm-dd") & "#")

(untested)
Hope that helps...

 

[zelarra821]

If I put what you tell me, this part

Format(DMax("Fecha", "CProximasCitas", "Fecha<=Date()"), "yyyy-mm-dd")

does not take value and gives error.

 

[zelarra821]

In the end I have opted for another solution, because that way it did not give the value I wanted.

Function RegistroProximaCitaEnElMenu() As String
Dim rst As DAO.Recordset
Dim StrQuery As String
    StrQuery = "SELECT Fecha, Registro" _
            & " FROM CProximasCitas" _
            & " WHERE Fecha>Date()" _
            & " ORDER BY Fecha, Registro"
    Set rst = CurrentDb.OpenRecordset(StrQuery)
    If Not (rst.EOF And rst.BOF) Then
         RegistroProximaCitaEnElMenu = rst("Registro")
    End If
    rst.Close
    Set rst = Nothing
End Function

Thanks for the help.

 

[cheekybuddha]

You can optimise a bit:

' ...
    StrQuery = "SELECT TOP 1 Fecha, Registro" _
' ...

Also, are you sure you don't want:

' ...
            & " ORDER BY Fecha DESC, Registro"
' ...

That should help if you have more than one entry on the same date at different times.

hth,

d

 

[zelarra821]

& " ORDER BY Fecha DESC, Registro"

I want the earliest most recent date to appear, not the latest, so you can't use DESC.

Putting TOP 1 did not know it, so thank you very much for the contribution.

 

[cheekybuddha]

I want the earliest most recent date to appear, not the latest, so you can't use DESC.

No problem! As long as you know what you want.

I just suggested that because in your original post you said:

I need to get the last value as of today

Perhaps you were closer originally than you thought.
You could probably also use:
Nz(DLookup("Registro", "CProximasCitas", "Fecha>=#" & Format(Date, "yyyy-mm-dd") & "#"), 0)
(Notice the date must be in unambiguous format)

 

[zelarra821]

So, sorry that I didn't express myself well. It works as I want.

 

[Pat Hartman]

FYI when you format a date, you turn it into a string. Strings are sorted left to right character by character. Therefore:
04/10/2021 is LESS THAN 05/10/2020 because 04 is LESS than 05.

NEVER format a date that you will use to sort or compare UNLESS you format it in year, month, day order (with or without separators) That is why your original expression did not work.

Also, if all you want to store is the date, do NOT use Now() to populate the field. Use Date(). Once you get some dates with times and some without, you will always have trouble with your criteria unless you account for the possible time values.

 

[cheekybuddha]

NEVER format a date that you will use to sort or compare UNLESS you format it in year, month, day order (with or without separators) That is why your original expression did not work.

This is so wrong

 

[Pat Hartman]

Exactly HOW is it wrong?