Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Dlookup

    I am running a test and get "MS Access can't find the field '|' referenced to in your expression" on the Bold



    It works fine in a query.


    If I change it to DLookup("[unique_id]","[Table1]") it will pass to the next step. what is wrong with:

    "[parent_equipment_item_id]='" &[unique_id] & "' And [Row Type]='MEQUIP'"


    Code:
    Private Sub Command0_Click()
    Dim BaseWChild As Integer, BaseWOChild As Integer
    Dim BaseEquip As String
    
    BaseEquip = DLookup("[unique_id]","[Table1]", "[parent_equipment_item_id]='" &[unique_id] & "' And [Row Type]='MEQUIP'")
    
        If BaseEquip<> "" Then
            BaseWChild= DCount("[unique_id]", "[Table1]", "[parent_equipment_item_id]='"& [platform_id] & "' AND [Equip LIN]='" & [Equip LIN]& "'AND ID<" & [ID] + 1)
        ElseIfBaseEquip = "" Then
            BaseWOChild= DCount("[unique_id]", "[Table1]","[parent_equipment_item_id]='" & [platform_id] & "' AND[Equip LIN]='" & [Equip LIN] & "'AND ID<" & [ID]+ 1)
        End If
    
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if UNIQUE ID is numeric, you cannot use the quotes. Quotes are for strings:

    DLookup("[unique_id]","[Table1]", "[parent_equipment_item_id]=" & me.[unique_id] & " And [Row Type]='MEQUIP'")

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ranman256

    unique_id,
    parent_equipment_item_id, platform_id are all strings.



  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just curious if it works like this:

    BaseEquip = DLookup("[unique_id]","[Table1]", "[parent_equipment_item_id] = '" & Me.[unique_id] & "' And [Row Type] ='" & "MEQUIP" & "'")

    I am assuming you run it on a form that has a control named Unique_Id.

    Cheers,
    Vlad

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    It is not a control on a form. I used a form to make a command button to run the VBA is all
    Table1 = Table
    unique_id = short name
    parent_equipment_item_id
    =
    short name
    Row Type
    =
    short name


  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Without seeing your dB, it is difficult to troubleshoot.

    In a copy (backup) of your dB, try changing the sub to this
    Code:
    Private Sub Command0_Click()
        Dim BaseWChild As Integer, BaseWOChild As Integer
        Dim BaseEquip As String
    
        'no criteria for DLookup
        BaseEquip = DLookup("[unique_id]", "[Table1]")
        MsgBox "No Criteria -> " & BaseEquip
    
        'Criteria is just for parent_equipment_item_id
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & [unique_id] & "'")
        MsgBox "Criteria for unique_id = " & Me.unique_id & " -> " & BaseEquip
    
        'Criteria is just for [Row Type]
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[Row Type]='MEQUIP'")
        MsgBox "Criteria for [Row Type] = 'MEQUIP' -> " & BaseEquip
    
        'Criteria is for both fields
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & [unique_id] & "' And [Row Type]='MEQUIP'")
        MsgBox "Both Criteria -> " & BaseEquip
    
        '    If BaseEquip <> "" Then
        '        BaseWChild = DCount("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & [platform_id] & "' AND [Equip LIN]='" & [Equip LIN] & "'AND ID<" & [id] + 1)
        '        ElseIf BaseEquip = "" Then
        '        BaseWOChild = DCount("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & [platform_id] & "' AND[Equip LIN]='" & [Equip LIN] & "'AND ID<" & [id] + 1)
        '    End If
    
    End Sub
    Maybe this will help find where the error is occurring...

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    ID=Autonumber

    Code:
        BaseEquip = DLookup("[unique_id]", "[Table1]")
        MsgBox "No Criteria -> " & BaseEquip
    Calls out the unique_id for the first row ID=1, No Criteria -> 1221947


    Code:
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[Row Type]='MEQUIP'")
        MsgBox "Criteria for [Row Type] = 'MEQUIP' -> " & BaseEquip
    Calls out the unique_id where [Row Type] ="MEQUIP", Criteria for [Row Type] = 'MEQUIP' -> 3608903

    Code:
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & [unique_id] & "'")
        MsgBox "Criteria for unique_id = " & [unique_id] & " -> " & BaseEquip
    Gives you
    "MS Access can't find the field '|' referenced to in your expression" on the Bold

    If I add in the
    Bold:
    Code:
        Dim db As DAO.Database
    
    Code:
    Dim rS As DAO.Recordset
        Set db = CurrentDb()
        Set rS = CurrentDb.OpenRecordset("Table1")
        MsgBox "Criteria for unique_id = " & rS![unique_id] & " -> " & BaseEquip


    It calls out a combination

    Criteria for unique_id = 1221947 -> 3608903

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is parent_equipment_item_id a field in Table1? It would help to show us the table and the query where you say it works.
    Vlad

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not Vlad, if the above was meant for me......


    I changed the code a little. Try this - it writes to the immediate window
    Code:
    Private Sub Command0_Click()
        Dim BaseWChild As Integer, BaseWOChild As Integer
        Dim BaseEquip As String
    
        'no criteria for DLookup
        BaseEquip = DLookup("[unique_id]", "[Table1]")
        Debug.Print "No Criteria -> " & BaseEquip
        Debug.Print
        BaseEquip = vbNullString
    
        'Criteria is just for parent_equipment_item_id
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & Me.[unique_id] & "'")
        Debug.Print "Criteria for unique_id = " & Me.unique_id & " -> " & BaseEquip
        Debug.Print
        BaseEquip = vbNullString
    
        'Criteria is just for [Row Type]
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[Row Type]='MEQUIP'")
        Debug.Print "Criteria for [Row Type] = 'MEQUIP' -> " & BaseEquip
        Debug.Print
        BaseEquip = vbNullString
    
        'Criteria is for both fields
        BaseEquip = DLookup("[unique_id]", "[Table1]", "[parent_equipment_item_id]='" & Me.[unique_id] & "' And [Row Type]='MEQUIP'")
        Debug.Print "Both Criteria -> " & BaseEquip
    
        MsgBox "Done"
    End Sub
    With no criteria, DLookup() will grab the first record.
    With one criteria, DLookup() grabs the first record that meets the criteria.

    Just asking because I cannot see your dB: Table! does have a field "parent_equipment_item_id" and it is spelled correctly?Where/what is [unique_id]?
    Is it a control on a form? A field in a form's Record Source?


    Kinda curious.... If [unique_id] is in fact a unique id, why would you need to have criteria for Row Type???

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Test1-26EB19.zip

    Gents,
    Please see attached

    There are multiple ID's to decide on another Columns Name and the parent...ID makes it a subcomponent of the unique_id.

    I know they are strings when it is should be a number, but that's how I receive it and import it. and I'm too lazy to change the " vice ' in the D(Lookup/Count). plus having to change it when it is imported. I can do a DoCmd.RunSQL "ALTER TABLE for this.


    Debug.Print "Criteria for unique_id = " & Me.unique_id & " -> " & BaseEquip = Method or data not found

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think the error might be because of several things:

    1) there are no records where "parent_equipment_item_id" equals "unique_id'"
    2) "parent_equipment_item_id" has LOTS of NULLs.


    I executed this query
    Code:
    SELECT Table1.ID, Table1.[Row Type], Table1.unique_id, Table1.parent_equipment_item_id, Table1.platform_id, [parent_equipment_item_id]=[unique_id] AS Expr1
    FROM Table1
    WHERE ((([parent_equipment_item_id]=[unique_id])=True));
    and 0 records were returned.

    If I add a new record and set "parent_equipment_item_id" equal to "unique_id'", then I get one record returned.


    But I think the main problem with the DLookup() is the NULLs in the field "parent_equipment_item_id"

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not knowing your db can't really speculate on the 'best" ways to achieve whatever you want to do; usually you employ a dlookup to isolate one value from a query or table. In your case there are over 2735 records that satisfy the criteria in the dlookup, so you would always get the first one anyway. Have a look at the qryParent_MEQUIP query and see if that helps you solve whatever you are trying to do.

    Cheers,
    Vlad
    Attached Files Attached Files

  13. #13
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks All, I thinks thats where the fail on the VBA part is that it ran into Nulls.

    Steve,
    I changed your query to show the sub equipment: [parent_equipment_item_id]<>[platform_id] = True. I understand the [parent_equipment_item_id]=[platform_id] = True gave no return values.

    I am trying to count the Base Items and I get the output as shown in Red Bold as the values. I am looking for a Count= Count +1 for a query to give a sequential count as shown in the second table in Red Bold as a desired output. I tried Dmax + 1, DCount +1 but it doesn't make it sequential. I know you can use - & AND ID>" & [ID]+1). It doesn't want to cooperate.

    Equip HB Name BaseCnt SubCnt TTLCnt Row Type unique_id parent_equipment_item_id platform_id ID
    117G(V)4 1 1 2 MEQUIP 3531965 3531964 3531961 6
    150(C) 1 1 2 MEQUIP 3531963 3531962 3531961 7
    RT-1523(C)/U 1 1 2 MEQUIP 3531972 3531971 3531961 17
    RT-1523(C)/U 1 2 3 MEQUIP 3531973 3531971 3531961 18

    Equip HB Name BaseCnt SubCnt TTLCnt Row Type unique_id parent_equipment_item_id platform_id ID
    117G(V)4 1 1 2 MEQUIP 3531965 3531964 3531961 6
    150(C) 2 1 3 MEQUIP 3531963 3531962 3531961 7
    RT-1523(C)/U 3 1 4 MEQUIP 3531972 3531971 3531961 17
    RT-1523(C)/U 3 2 5 MEQUIP 3531973 3531971 3531961 18


    BaseCnt is counting the Parent...id where it equals unique_id


    Attached is the dB:
    Test1-3MAR19 - Wayne.zip

    Another odd thing I noticed is I have to put +0 on the end of DCount or the output becomes a string.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is this what you're looking for?

    Cheers,
    Vlad
    Attached Files Attached Files

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  2. DLookup
    By Alex Motilal in forum Programming
    Replies: 2
    Last Post: 04-20-2015, 11:05 PM
  3. DLookup help
    By mike02 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 02:29 PM
  4. Need help with a DLookup using Or
    By jax1000rr in forum Queries
    Replies: 1
    Last Post: 05-06-2013, 11:18 AM
  5. DLookUp Help
    By ThaGreenMoose in forum Forms
    Replies: 10
    Last Post: 06-15-2011, 12:08 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
  •  
Other Forums: Microsoft Office Forums