Dlookup Help - Three Criteria - 2 text & 1 Number

[BustyAshley]

Expr1: DLookUp("[PL4]","[07 GL Lookup]","[G/L Account No#] = " & [Input GL Account] & " And [Description] = ' " & [Input Description] & " ' And [Phase Code] = ' " & [Input Phase Code] & " ' ")

I have three columns I'm pulling off a table
G/L Account No#
Description
Phase Code

I want to do a lookup off of table [07 GL Lookup]

Only the phase code in both the data table and lookup table may be blank

--- Currently when I run it, a box pops up and asks for parameter values for a Gl Account, Description and Phase code, instead of whats in the column.

 

[pbaldy]

You'd have to use the actual field names. You might also consider joining the lookup table in the query and getting the value that way. It would be more efficient.

 

[BustyAshley]

Query brings in 3 fields from table [05 GL]

[G/L Account No#]
[Description]
[Phase Code]


My lookup table [07 GL Lookup]

[Input GL Account]
[Input Description]
[Input Phase Code]
[PL4]


To determine the PL4, I have to match up the 3 items... So I'm using the Dlookup in a query to do this. But when I run it, it asks for parameter values, but each item on my lookuptable could have 70 different values

 

[pbaldy]

A join would be more efficient, but it looks like you have the fields reversed in the criteria. It should be:

"FieldInLookupTable = " & FieldInQuery & "...

 

[BustyAshley]

Ok wow I'm dumb haha

So the parameter input box went away, but now all my Expr1 = #error??

Can I do a Join if the table sizes are different? the lookup table is like 70 items long, where the 05 GL table is like 5000 items long. (I'm going to bring in more fields after, right now I'm just trying to get the correct PL4 to pull through in the query)

 

[pbaldy]

Sure, table size doesn't affect being able to use a join. Having fields to join on does, but that's likely the case here; if a DLookup() works, a join should work. Can you attach the db here?

 

[BustyAshley]

I've uploaded a trimmed out example

 

[pbaldy]

The data types aren't consistent, but if I change that this query appears to work, though I didn't review the values returned:

SELECT DISTINCTROW [05 GL].[G/L Account No#], [05 GL].Description, [05 GL].[Phase Code], DLookUp("[PL4]","[07 GL Lookup]","[Input GL Account] = " & [G/L Account No#] & " And [Input Description] = ' " & [Description] & " ' And [Input Phase Code] = ' " & [Phase Code] & " ' ") AS Expr1, [07 GL Lookup].PL4
FROM [05 GL] INNER JOIN [07 GL Lookup] ON ([05 GL].[Phase Code] = [07 GL Lookup].[Input Phase Code]) AND ([05 GL].Description = [07 GL Lookup].[Input Description]) AND ([05 GL].[G/L Account No#] = [07 GL Lookup].[Input GL Account])
GROUP BY [05 GL].[G/L Account No#], [05 GL].Description, [05 GL].[Phase Code], [07 GL Lookup].PL4;

 

[BustyAshley]

Ok I updated the Types.. I realized one was number and one was text... the output is working for anything with a Phase code, if it doesn't have a phase code, it doesn't work


Edit: All items will always have a G/L Account No# & Description... only a select few will have a phase code

maybe have to do a lookup with two criteria if the phase code is blank, then a lookup with three criteria if the phase code isn't blank?

 

[pbaldy]

Change INNER to LEFT and try it.

 

[BustyAshley]

I get all my rows now but I don't get the PL4 lookup for the items missing PL4

 

[pbaldy]

What would you expect to get? Here's your repaired DLookup, but it returns the same values the join does (as I would expect):

DLookUp("[PL4]","[07 GL Lookup]","[Input GL Account] = '" & [G/L Account No#] & "' And [Input Description] = '" & [Description] & "' And [Input Phase Code] = '" & [Phase Code] & "'")

 

[BustyAshley]

I guess the Join or the DLookUp isn't doing what I expected.

My thought that is if it the phase code was blank in both (Table & Lookup) that the Dlookup or Join would still work.

This use to be done in excel, we would combine the 3 columns and do a vlookup. So having blank in a column was fine because the unique key (a column in the lookup table) would still pick it up..

Do I have to do the same thing? Merge the 3 columns into 1 column then do the join?


EDIT: I meant to say.. if the item doesn't have a PHASE CODE, then it won't return back the PL4, even if the lookup table doesn't have a PHase code.


Thanks for all the help

 

[pbaldy]

Here's one way, but note it can't be represented in design view, only SQL view:

SELECT DISTINCTROW [05 GL].[G/L Account No#], [05 GL].Description, [05 GL].[Phase Code], [07 GL Lookup].PL4
FROM [05 GL] LEFT JOIN [07 GL Lookup] ON (nz([05 GL].[Phase Code],"") = nz([07 GL Lookup].[Input Phase Code],"")) AND ([05 GL].[G/L Account No#] = [07 GL Lookup].[Input GL Account]) AND ([05 GL].Description = [07 GL Lookup].[Input Description])
GROUP BY [05 GL].[G/L Account No#], [05 GL].Description, [05 GL].[Phase Code], [07 GL Lookup].PL4;

 

[BustyAshley]

OMG!! Thank you soooo much

 

[pbaldy]

Happy to help!

 

[BustyAshley]

Quick question.. Every time I open the query, it only allows me to open it in "Design View." Once I open it in design view and hit ok to the erros, I then can go to SQL View but the code gets modified, so I have to repaste in the code you wrote for it to work correctly.

Is there any way to open a query in SQL view right away without having to open it in design view first?

 

[BustyAshley]

ughh... turns out when I open a query, it auto opens to the last saved query view. So I have to get into the practice of going to SQL View prior to closing and saving any query