MS Access 2013 Dlookup String Criteria issue

Question

It seems this problem should have a rather simplistic solution. When trying to run the dlookup for ID, I return a null value when using the RequestNum string. RequestNum is simply an AutoNumber. RequestNum does write to the popup form that is opening up on its own. Also, if i replace RequestNum with the actual number in the field I get the desired returned result. The only help I've been able to find online is that most people did not use '" & stringhere & "' though I obviously am. Any ideas? I'll be happy to supply any additional details if needed. Thanks in advance!

Private Sub lst_AdminDate1_DblClick(Cancel As Integer)
    Dim IDx As String
    Dim RequestNum As String
    DoCmd.OpenForm "Administrative_LeaveCalendar_Detail"
    RequestNum = Me.lst_AdminDate1.Column(2)
    IDx = DLookup("[ID]", "TimeOffCalendar", "[RequestNumber] = '" & RequestNum & "'")
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_RN] = RequestNum
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_ID] = IDx
    End Sub

If RequestNum is an autonumber, then it's a long integer by default. Adjust the data type in your dimension and remove the single quotes from the Dlookup WHERE argument and it should work. If not, what does debug.print Me.lst_AdminDate1.Column(2) yield? If it's the correct ID, then try coercing to a long int with RequestNum = Clng(Me.lst_AdminDate1.Column(2). If it isn't the correct ID, are you referencing the correct column? .Column is zero-indexed, so try .Column(1). — MoondogsMaDawg
Removing single quotes and adding IDx as variant took care of the issue. Thanks for the input! — Matt

Gustav Gustav · Accepted Answer · 2017-02-09T11:46:19

What may confuse you is, that a listbox always returns strings even if the value was a number and is supposed to be used as such. Also, DLookup may return Null.

Thus, if [RequestNumber] is a Long (which is should be):

Private Sub lst_AdminDate1_DblClick(Cancel As Integer)

    Dim IDx As Variant
    Dim RequestNum As String

    DoCmd.OpenForm "Administrative_LeaveCalendar_Detail"
    RequestNum = Me.lst_AdminDate1.Column(2)
    IDx = DLookup("[ID]", "TimeOffCalendar", "[RequestNumber] = " & RequestNum & "")
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_RN] = RequestNum
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_ID] = IDx

End Sub

MS Access 2013 Dlookup String Criteria issue

1 Answers