get value from one string for another string

[spnz]

Hi,

I have a string that I am trying to get working so that I can use the value from it and place the result into another string.

strLastRecord = "SELECT (Max(ExportDate)) AS TheLast FROM tblExportInformation "

It basically gets the last value in the tblExportInformation.

How is it possible for me to get the value from the SELECT query and and place it into a WHERE string for example....

 strWHERE = " WHERE tblPersonalInformation.DateModified LIKE '" & strLastRecord & "' "

As you can see I have no idea what Im doing here so any advise would be great.


THanks for your time.

 

[FoFa]

strLastRecord = DMAX("ExportDate","tblExportInformation")
Instead of the select.
I assume the second one is building a dynamic SQL statement, it appears correct except you usually use * or % for wildcard characters with the LIKE

 

[nateobot]

In order to set your strLastRecord variable you need to assign it to a recordset. As you have it there, it assigns it to the sql text, but doesn't give it a value.

One method would be to run code like below to assign

Public Function getLastRecord() As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT (Max(ExportDate)) AS TheLast FROM tblExportInformation")
    If IsNull(rs("TheLast")) Then
        'we have no data
        getLastRecord = "NOTHING"
    Else
        getLastRecord = rs("TheLast").Value
    End If
    Set rs = Nothing
    Set db = Nothing
End Function