Syntax problem with Dlookup

[LeAnn]

I have a string variable for my SQL statement. In the statement I have a
dlookup function (which is a frustrating thing to deal with I must say!) and
a left function. I've tried all combinations of single quotes, double
quotes, brackets no brackets etc. I'm getting a 2465 error "......can't find
the field "|" ......" There is no "|" in my statement. I have a listbox
with a query SQL statement in the row source and I am trying to emulate that
statement in the Open event of the form. I have an if statement that sets the
listbox based on how/where the user opens the form. I have a where statement
but have not included it for simplicity. Maybe a fresh pair of eyes can help
me or tell me what I'm doing really wrong here.

Thanks
LeAnn

Here's what I have............

strSQL = "SELECT CYC_ID, tblReagents.RgtNum as Rgt, " & DLookup("RgtType",
"tblReagentType", "RegNum = '" &

 & "'") & " as Type, FreezeDte,
ThawDte," _
& " CycleNum, RPIID, " & Left([Rgt], 4) & " AS Code FROM tblCycle LEFT
JOIN tblReagents ON " _
& " tblCycle.RgtNum = Rgt"

 

[pietlinden]

Here's what I have............

strSQL = "SELECT CYC_ID, tblReagents.RgtNum as Rgt, " & DLookup("RgtType",
"tblReagentType", "RegNum = '" &

 & "'") & " as Type, FreezeDte,
ThawDte," _
& " CycleNum, RPIID, " & Left([Rgt], 4) & " AS Code FROM tblCycle LEFT
JOIN tblReagents ON " _
& " tblCycle.RgtNum = Rgt"[/QUOTE]

Might be a simplistic question, but what results are you looking for?
What tables are involved?  tblReagents,  tblCycle and what else?  Any
reason you can't inner/outer join those two tables?  If they are
related, how are they related?  Could you post your table structures
(only the critical fields).

One thing to keep in mind - people here can only see as much as you
show them.  Without complete information, it's difficult or impossible
to figure out what's wrong.

 

[LeAnn]

I realized I should have created a stand alone query for the listbox row
source for the load event in the first place - MUCH easier. Problem solved.

Here's what I have............

strSQL = "SELECT CYC_ID, tblReagents.RgtNum as Rgt, " & DLookup("RgtType",
"tblReagentType", "RegNum = '" &

 & "'") & " as Type, FreezeDte,
ThawDte," _
& " CycleNum, RPIID, " & Left([Rgt], 4) & " AS Code FROM tblCycle LEFT
JOIN tblReagents ON " _
& " tblCycle.RgtNum = Rgt"[/QUOTE]

Might be a simplistic question, but what results are you looking for?
What tables are involved?  tblReagents,  tblCycle and what else?  Any
reason you can't inner/outer join those two tables?  If they are
related, how are they related?  Could you post your table structures
(only the critical fields).

One thing to keep in mind - people here can only see as much as you
show them.  Without complete information, it's difficult or impossible
to figure out what's wrong.
[/QUOTE]