Results 1 to 4 of 4
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Dlookup in DAO.recordset


    How do I correctly use a DLookup statement in a DAO.Recordset?

    SRW1 = DLookup("Unit Name", "NBOI", "rU![BN (Owning)] = 'INF BN #' & rU![BN (Owning)= 'A?'")

    Could I use Unit in this instance as Unit=[Unit Name].NBOI

    I'm not sure I am using the wildcards correct either.

    In the field [BN (Owning)].NBOIP I have the following values:

    INF BN 1
    INF BN 2
    INF BN 3

    In the field [CO (Owning)].NBOIP I have the following values:
    A
    B
    C
    D
    E
    F
    G
    H
    J

    I want to to look up the [Unit Name].NBOI



    Code:
    Dim rU As DAO.Recordset, X As Integer, Z As Integer, Units As String, A As String, B As String, C As String, SRW1 As String
    
    Set rU = CurrentDb.OpenRecordset("NBOI")
    
    If rU.BOF And rU.EOF Then
                    rU.Close
                Else
                    rU.MoveLast
                    rU.MoveFirst
       
        Do While Not rU.EOF
        
        If Not rU![Co (Owning)] Like "HH*" Then
                B = rU![Co (Owning)] & " "
        ElseIf rU![Co (Owning)] Like "HH*" And rU![PLT] = "1" Or rU![PLT] = "2" Or rU![PLT] = "3" Or rU![PLT] = "4" Or rU![PLT] = "5" Then
                B = rU![Co (Owning)] & " "
        ElseIf rU![Co (Owning)] Like "HH*" Then
                B = ""
        End If
        If rU![PLT] = "1" Or rU![PLT] = "2" Or rU![PLT] = "3" Or rU![PLT] = "4" Or rU![PLT] = "5" Or rU![PLT] = "SCT" Then
                    A = rU![PLT] & " "
                ElseIf rU![PLT] = "MRT" Then
                    A = "MORT "
                Else
                    A = ""
        End If
                If IsNull(rU![BN (Owning)]) Then
                    Units = ""
                    Z = 0
            ElseIf rU![BN (Owning)] = "BDE" Then
                    Units = A & B & "2BDE101AB"
                    Z = 1 + X
            ElseIf rU![BN (Owning)] = "INF BN 1" Then
                    Units = A & B & "1BN502IN"
                    Z = 0
            ElseIf rU![BN (Owning)] = "INF BN 2" Then
                    Units = A & B & "2BN502IN"
                    Z = 0
            ElseIf rU![BN (Owning)] = "INF BN 3" Then
                    Units = A & B & "1BN26IN"
                    Z = 2 + X
            ElseIf rU![BN (Owning)] = "Fires" Then
                    Units = A & B & "1BN320FA"
                    Z = 8
            ElseIf rU![BN (Owning)] = "CAV" Then
                    Units = A & B & "1BN75CAV"
                    Z = 0
            ElseIf rU![BN (Owning)] = "BEB" Then
                    Units = A & B & "39BEB"
                    Z = 13
            ElseIf rU![BN (Owning)] = "BSB" Then
                    Units = A & B & "526BSB"
                    Z = 19
          End If
    
        If rU![SRW  1] = "BDE HHC" Then
                SRW1 = "2BDE101AB"
       ElseIf rU![SRW  1] Like "INF BN # A?" Then
                SRW1 = DLookup("Units", "NetBOI", "rU![BN (Owning)] = 'INF BN #' & B= A?'")
        ElseIf Not IsNull(rU![SRW  1]) Then
                SRW1 = Dlookup(rU![]
        ElseIf IsNull(rU![SRW  1]) Then
                SRW1 = ""
        End If
    
                rU.Edit
                rU!UnitIDs = Z
                rU![Unit Name] = Units
                rU![SRW1] = SRW1
                rU.Update
                rU.MoveNext
        Loop
    
    End If
           rU.Close
           Set rU = Nothing
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    simple answer is, you can't. you would use something like findfirst.

    But looking at the code, you aren't using dlookup to find a value in the dao.recordset, you are using it to lookup a value in a table or query called NB01.

    So presumably your issue is the criteria doesn't work.

    "rU![BN (Owning)] = 'INF BN #' & rU![BN (Owning)= 'A?'"

    you appear to be missing an AND and a square bracket so assuming the bits in square brackets are field names in NB01 try

    "[BN (Owning)] = 'INF BN #' AND [BN (Owning)]= 'A?'")

    but I have no idea how the same field can be both 'INF BN #' and 'A?' at the same time

    other issue is you have spaces in your names so "Unit Name" needs to be "[Unit Name]" and using brackets and other non alphanumeric characters will only cause unexpected and time consuming errors to resolve.

    You really ought to spend 10 minutes researching field and table naming conventions.

    with regards wildcards see this link

    https://support.office.com/en-gb/art...9-e18abaad12d1

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax,
    I am coding off from someone else's imported spreadsheet. I am not the data origin. I understand the naming conventions. No spaces or use an _ for it. I went another route to fix part of the problem above, although I would like to find a more generic way instead of tailoring to the current spreadsheet.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    glad you have a solution. good luck with your project

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

Similar Threads

  1. Replies: 4
    Last Post: 01-22-2015, 09:57 AM
  2. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  3. How to Select second Recordset from first recordset
    By FrustratedAlso in forum Programming
    Replies: 28
    Last Post: 05-10-2012, 05:45 PM
  4. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

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