Dlookup with Like 1

[end922]

Greetings, I am trying to do a Dlookup in a query with Like.
I import a table that has acct #s like ###-#####-##
(field name [F6]) I want to flag accounts that are on my list. So I created a table called range with a field called ID and added in the first ### (the other digits are NA to me) to the field. It doesn't seem to work. Any suggestions?

Thanks
E

My dlookup looks like this

Keep: DLookUp("[ID]","Range","[ID] like '[F6]*'")

 

[PHV]

Keep: DLookUp("[ID]","Range","[F6] Like [ID] & '*'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Why are you using a DLookup in a query?

Why not simply...

...
Where Left([ID],3) = [F6]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Sorry for the typo:

Keep: DLookUp("[ID]","Range","'" & [F6] & "' Like [ID] & '*'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

Thank you PHV. 2nd works great. You the man!

Skip. Would have used yours if the RANGE table was in the query. There is no way to join it so a lookup seemed my only option.

Thanks Guys for your immediate response and solution.

E

 

[PHV]

There is no way to join it
Really ?
You may try this:
SELECT *
FROM ImportedTable I INNER JOIN Range R ON R.ID=Left([F6],3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

I will try.
table name is ImportBD2 field F6
What is the I after ImportedTable mean?

 

[PHV]

It's an alias.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Simply an alias: I for Import; R for Range

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

join expression not supported.

SELECT *
FROM Range INNER JOIN ImportBD2 ON Range.ID = left([ImportBD2]![F6],3);

 select *
FROM ImportBD2 I INNER JOIN Range R  ON R.ID=Left([F6],3);