Dlookup Assistance

[Thompyt]

I don't know why I can't get the DLOOK up function in straight.

All rows are set up as plain text

Table:
Data

Columns
[Row Type]; Plat, MEQUIP
[Bumper # / Plat ID]; Many - non standard format
[USA #]; Many - non standard format
[Equip HB Name]; Not Like "*JCR*"



What my intention is:
A. Lookup all [USA #] that [Row type] = "Plat"

to determine if under the [USA #] and [Row type] = "MEQUIP" where [Equip HB Name]; Not Like "*JCR*"

Where this is true, then output = "Not assigned", not true is ignored.


example:
[Bumper # / Plat ID]
[USA #]
"Not Assigned"


NAssigdJCR: IIF(Dlookup("[USA #]","[Data],"[Row Type] = "'Plat' &......,"Not Assigned")

[davegri]

What my intention is:
A. Lookup all [USA #] that [Row type] = "Plat"

You may need to rethink this. Dlookup will find a maximum of ONE record.

[Thompyt]

Dave
I have been trying this in a different manner. 1st Query [W_JCR] I find all [USA #] with [Equip HB Name] like "*JCR*"

This part is simple.

SELECT DISTINCT Data.[Bumper # / Plat ID], Data.ID, Data.[USA #] AS USA
FROM Data
WHERE (((Data.[Bumper # / Plat ID])<>"") AND ((Data.[USA #])<>"") AND ((Data.[Equip HB Name]) Like "*JCR*") AND ((Data.[Row Type])="MEQUIP"));

Now I added a second query with the table [Data] and query [W_JCR]. I was hoping to use the first query to filter out all the [USA #] and list the remaining [USA #]

SELECT DISTINCT Data.[Bumper # / Plat ID], Data.[USA #]
FROM Data, [1-Data w_JCR]
WHERE (((Data.[Bumper # / Plat ID])<>"") AND ((Data.[USA #])<>[usa]) AND ((Data.[Row Type])="Plat"));

But it is not even close and lists all [USA #].   

Please advise.

Thanks

[davegri]

Your data names are very confusing to me and probably to Access.
[Bumper * / Plat id] could be interpreted as bumber# divided by Platid
1-Data could be interpreted as 1 minus Data
And do you really have a data item named [1-Data w_JCR] ?
# is a delimiter for dates
Good practice indicates NO spaces or special characters in data names.

Have you tried to develop the 2nd query in the query design grid?

[Thompyt]

I didn't choose the data names. They are an import. Query names I made up to find at top. I was being lazy about the spaces.

1 placed query at top. Data was then table, w_JCR was the item I was looking for.