DLast function not working

[deanvilar]

I tried using DLast function but its not working below is the code:

Me.txtReturnedYear = Nz(DLast("returnedYear", "documentLog", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")

as I understood, DLast function takes the last record of the table.

[Seth Schrock]

Starting with the criteria section of the DLast function, make it be

"FileCode LIKE '" & Me.txtFileCode & "' & *"

[deanvilar]

will it be like this sir?

Me.txtReturnedYear = Nz(DLast("fileCode LIKE '" & Me.txtFileCode & "*'", "returnedYear", "documentLog", ), "")

[Seth Schrock]

You were closer your first try. Try this

Me.txtReturnedYear = Nz(DLast("returnedYear", "documentLog", "FileCode LIKE '" & Me.txtFileCode & "' & *"), "")

[deanvilar]

ok sir i will try now.

[deanvilar]

sir, I got an error ... SYNTAX ERROR (MISSING OPERATOR) IN QUERY EXPRESSION 'fileCode LIKE 'me.txtFileCode(value)'&*'

[TheSmileyCoder]

Hi Deanvilar and welcome to Bytes.
A few pointers which might be helpful in the future.

"Not working" is actually not a great way to describe a problem, since "Not working" can actually cover a whole lot of scenarios. You need to be more specific, such as "This causes an error 9 and the error message is "Index out of bounds"

Or the code is running, but I am not getting the results I expected, or nothing seems to be happening at all. Trust me when I state that time you spend on making a good question comes back in time saved on answering followup-questions.


Now, you seem to have it wrong, in believing that Dlast will always return the last record. It is however a quite common mis-conception. It will often return the last record, but that cannot be guaranteed. In fact the Access help on Dlast states:

Access 2010 help on Dlast
You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field.

And a bit furhter down it also states:

Access 2010 help on Dlast
If you want to return the first or last record in a set of records (a domain (domain: A set of records that is defined by a table, a query, or an SQL expression. Domain aggregate functions return statistical information about a specific domain or set of records.)), you should create a query sorted as either ascending or descending and set the TopValues property to 1. From Visual Basic, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.

[Seth Schrock]

Thanks for that Smiley. I had never heard of the DLast function until I looked up this question and was just going off the syntax, not what the function actually did.

@deanvilar Lets try this:

Dim strCriteria as String
strCriteria = "FileCode LIKE '" & Me.txtFileCode & "' & *"
MsgBox strCriteria
Me.txtReturnedYear = Nz(DLast("returnedYear", "documentLog", strCriteria), "")

Please post what the message box displays without any additional quotes or changes of values. This will allow us to see what the database engine is working with.

[TheSmileyCoder]

I will admit that I have never understood why they choose that name for the function. It does seem quite mis-leading.

[NeoPa]

I'm afraid Seth's advice on this occasion is not correct. In fact your first code was more correct.

However, 'Last' as a concept only makes sense when you properly understand that a table holds a set of records, and not a set of records in any particular order. There may be a default order but you need to understand what that is before relying on it. Alternatively, work from a recordset where the order is specific.

Me.txtReturnedYear = Nz(DLast("returnedYear", "???", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")

is the correct format for the call.

[deanvilar]

sir Smiley sorry for not providing specific problem of the code, anyway it's working but as they told the sense of returning the last record of DLAST is not giving me the proper results I need ... next time I would specify each detail of the problem .... thanks for the reply sir ....

@seth and @neopa am going to try your codes now ... i hope it'll solve my problem =)

[deanvilar]

@seth, it gave me same error as the previous one ....

[deanvilar]

@neopa, thanks for the advice ... am using the DLAST function to check another table as I already have a recordset to check a table for other records ... sir @seth and @neopa here is my
code

Dim logNo As String
Dim user As String
Dim fileNo As String
Set db = CurrentDb
Set rst = db.OpenRecordset("mainTableArchive", dbOpenTable)
 
rst.Index = "Primarykey"
rst.Seek "=", Me.txtFileCode
 
If rst.NoMatch Then
    MsgBox "Record not Found!"
 
    Me.txtFileCode.Value = ""
    Me.txtDocType.Value = ""
    Me.txtDocNo.Value = ""
    Me.txtDocTitle.Value = ""
    Me.txtAssetNo.Value = ""
    Me.txtEquipType.Value = ""
    Me.txtEquipModel.Value = ""
    Me.txtAssetOwner.Value = ""
 
    Me.txtFileCode.SetFocus
Else
    'returns value to txtReturnedYear
    Me.txtReturnedYear = Nz(DLast("returnedYear", "documentLog", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")
    'returns value to string fileNo
    fileNo = Nz(DLast("fileCode", "documentLog", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")
    'returns value to string LogNo
    logNo = Nz(DLast("docLogNo", "documentLog", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")
        If Me.txtReturnedYear = "" And Me.txtFileCode = fileNo Then
        user = Nz(DLast("fullName", "documentLog", "fileCode LIKE '" & Me.txtFileCode & "*'"), "")
             MsgBox "File was taken out by " & user
                Me.txtFileCode.SetFocus
                Me.txtFileCode.Value = ""
                Me.txtDocType.Value = ""
                Me.txtDocNo.Value = ""
                Me.txtDocTitle.Value = ""
                Me.txtAssetNo.Value = ""
                Me.txtEquipType.Value = ""
                Me.txtEquipModel.Value = ""
                Me.txtAssetOwner.Value = ""
        Else
                Me.txtDocType = rst.docType
                Me.txtDocNo = rst.docNo
                Me.txtDocTitle = rst.docTitle
                Me.txtAssetNo = rst.assetNo
                Me.txtEquipType = rst.equipType
                Me.txtEquipModel = rst.equipModel
                Me.txtAssetOwner = rst.assetOwner
 
                Me.cmbTakeOutDay.SetFocus
                Me.txtReturnedYear.Value = ""
        End If
    End If
rst.Close

[NeoPa]

I hope that means you resolved your issue. Your code isn't easy to read and I don't want to go through it all just to determine if it is or not. Unless told otherwise, I'll assume it's all done.

[deanvilar]

@NeoPa, unfortunately no its not resolved yet =(

actually on the code... is to search field fileCode in table mainArchive, if found .. verify about the last record according to fileCode in table documentLog ...

[deanvilar]

now it's ok, I used DMax on PK and everything is fine... thanks for all your suggestions and replies ... @seth and @neopa..

[NeoPa]

Glad you managed to find a solution anyway :-)

[deanvilar]

yes ... thank you sir neopa ...