Results 1 to 3 of 3

Please help with DLookup with string and integer criteria

  1. #1
    Fishes is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    2

    Please help with DLookup with string and integer criteria

    Here is my vba code I am struggling with...the red DLookup text is where I get a Runtime error 94: invalid use of null. varAgency is a string and varYear is an integer. There is no data for 2019 in the query to start thus I used If varFishCount = "" Then EndNum =1...



    I think my issue is with the quotes, singles quotes or AND or all of the above in the DLookup string. Any able to tell me where I'm wrong here?


    Dim varYear As Integer
    Dim varAgency As String
    Dim varFishCount As Integer
    Dim EndNum As Integer
    varYear = [Forms]![frmFishObservations2]![txtYear]
    varAgency = [Forms]![frmFishObservations2]![cmbAgency2]
    varFishCount = DLookup("[CountOfFishID]", "qryFishCount", "[AgencyCode] = '" & varAgency & "'" & " And [CollectionYear]=" & varYear)
    If Not (IsNull([DBFishID])) Then Exit Sub
    If varFishCount = "" Then
    EndNum = 1
    ElseIf Not (IsNull(varFishCount)) Then
    EndNum = varFishCount + 1
    End If
    [DBFishID] = varYear & "-" & varAgency & "-" & EndNum
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,688
    DLookup returns Null if no match. varFishCount is declared as integer so it cannot hold Null nor empty string. Only Variant type can hold Null.

    varFishCount = Nz(DLookup("CountOfFishID", "qryFishCount", "AgencyCode='" & varAgency & "' AND CollectionYear=" & varYear), 0)

    If varFishCount = 0 Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Fishes is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    2
    Wow. Many thanks. You just solved the issue I was struggling for a full day on!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-10-2019, 10:19 PM
  2. Convert string to integer
    By AmanKaur123 in forum Programming
    Replies: 2
    Last Post: 01-16-2017, 08:35 AM
  3. String criteria used in DLookup
    By joecamel9166 in forum Programming
    Replies: 9
    Last Post: 03-11-2016, 08:37 AM
  4. Dlookup() With Multiple Criteria (Specifically: a String, & Two dates)
    By QA_Compliance_Advisor in forum Programming
    Replies: 19
    Last Post: 07-09-2015, 04:26 PM
  5. How to use DLookup with string variable Criteria
    By JrMontgom in forum Programming
    Replies: 1
    Last Post: 03-09-2014, 06:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums