Another Dlookup ?

[Thompyt]

I have:


DLCH1: DLookUp("[parent_equipment_item_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'")

This works as the ID's are numbers

and want to add:
[Equip Lin]="XXB840"

it is a string

DLCH1: DLookUp("[parent_equipment_item_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "' AND [Equip Lin]=XXB840") = #Error

I know it has to do with the " and '

[JoeM]

Try:

DLCH1: DLookUp("[parent_equipment_item_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "' AND [Equip Lin]='XXB840'")

[Thompyt]

I think I am having an absolute brain cramp.

I am wanting to look up the parent_unique_item_id with [Equip LIN] = XXB840 that is equal to unique_id with [Equip Lin] = Z05072


I have tried several iterations without success. I get an null out, but no errors. I did try your example and get a null out. Wich is correct as I don't get an error.

[CJ_London]

you say

This works as the ID's are numbers

but this

"[unique_id] ='" & [parent_equipment_item_id] & "'"

is treating them as strings

to treat as numbers you would have

"[unique_id] =" & [parent_equipment_item_id]

[Thompyt]

I guess they are considered as a string then. I see numbers, but didn't check the cell type.

DLCH1: DLookUp("[parent_equipment_item_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'")

This does provide numbers, but it shows uyp under XXB480 and XXB481 LINS, so it is technichally wrong.

[CJ_London]

go with JoeM's solution then

[John_G]

I am wanting to look up the parent_unique_item_id with [Equip LIN] = XXB840 that is equal to unique_id with [Equip Lin] = Z05072

In that description, you said that the value you want must be "equal to unique_id with [Equip Lin] = Z05072"

So all you need is this:

UniqueID_1 = DLookUp("[unique_id]","[Data]"," [Equip Lin] = 'Z05072')


I'm not sure that is what you really want - but that's what your description says.

[Thompyt]

I used this to get around it:

DLCH1: IIf([Equip LIN]="XXB840",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'"),"")

DLCH2: IIf([Equip LIN]="XXB841",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'"),"")

I'd prefer not to use the IIF statement and I know there's a more eloquent way of doing it with just the DLookup. Maybe the code explains it better than I can.

If query is on a row where the [Equip Lin] = XXB840, then find the Unique ID elsewhere in the query where it equals the parent_equipment_item_id and [Equip Lin] = Z05072.

Example:

[Equip Lin] Unige_ID Parent_..._id
XXB840 123456 123454
XXB841 123457 123454
Z05072 123454 120019

Z05072 = Radio
XXB840 = Channel 1
XXB841 = Channel 2

[John_G]

If query is on a row where the [Equip Lin] = XXB840, then find the Unique ID elsewhere in the query where it equals the parent_equipment_item_id and [Equip Lin] = Z05072.

That doesn't make sense. You are trying to find a [Unique ID] value when you already know the value you want - it is in [parent_equipment_item_id] of the current query row.

Your dlookup : DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'")

Is doing just that. However, you are using it in an Iif which returns an empty string if the DLookup does not find a match - is that the object of the exercise?

[Thompyt]

"However, you are using it in an Iif which returns an empty string if the DLookup does not find a match - is that the object of the exercise?"

yes, that was my intentions.

That radio is not the only radio in the db, and in this instance the radio count exceeds 150. There are many more and they all use the XXB840/1 [Equip LIN].

So if the Equip Lin = that radio, then I need to know the unique_ID so that I can search the parent.._ID that matches the radios unique_ID and then have separation between channel 1 and channel 2. They are assigned different nets.

[Thompyt]

I figured it out finally. This is/was my intent:
DLCH1: IIf([Equip LIN]="XXB840",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'" & " And [Equip Lin]='Z05072'"),"")
DLCH2: IIf([Equip LIN]="XXB841",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'" & " And [Equip Lin]='Z05072'"),"")