SQL and DLookup

  • Thread starter Thread starter Rick Brandt
  • Start date Start date
R

Rick Brandt

DS said:
Can you use DLookUp in SQL?

Test2SQL = "UPDATE table1 IN '" & strPath = DLookup("BackName",
"tblBackPath", "BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)

Thanks
DS
Click to expand...

If it's outside the quotes then it's not *in* your SQL string. You are just
using the value it retrieves to build your SQL string. Of course the "strPath
= " should not be there. That will cause the expression to evaluate to True
(not the value retrieved by DLookup()).
 
DS said:
Thanks, Rick.
But how do I write this thing? Do I not use DLookup and use SELECT or
WHERE?
Thanks
DS
Click to expand...

Test2SQL = "UPDATE table1 IN '" & _
DLookup("BackName","tblBackPath", "BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"

DoCmd.RunSQL (Test2SQL)
 
DS said:
OK This works , but should I really be using DLookup Here?
Should it be WHERE or SELECT?
Thanks
DS
Click to expand...

Is there a particular reason you do not want to use DLookup()? You need to
retrieve a string value from a table so you can use that string value to build a
larger string in your code. Seems like a perfectly legitimate use of DLookup()
to me.
 
DS said:
I just though using SELECT and/or WHERE would keep it all SQL as
oppose to jumbling it up with the DLookup thing. Is there any
downsides to this, would a SELECT and/or WHERE be faster and more
solid, I don't know so I'm asking.
Thank you for your input.
DS
Click to expand...

But it's not SQL until you do the Execute and at that point the DLookup is not
involved. The DLookup is only used to construnct a *string*. The string that
is *executed* has no DLookup in it.
 
Can you use DLookUp in SQL?

Test2SQL = "UPDATE table1 IN '" & strPath = DLookup("BackName",
"tblBackPath", "BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)

Thanks
DS
 
Thanks, Rick.
But how do I write this thing? Do I not use DLookup and use SELECT or
WHERE?
Thanks
DS
 
OK This works , but should I really be using DLookup Here?
Should it be WHERE or SELECT?
Thanks
DS


Test2SQL = "UPDATE table1 IN '" & DLookup("BackName", "tblBackPath",
"BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "'," & _
"table1.IDName2 = '" & Forms!Form1!TxtInfo2 & "'," & _
"table1.IDName3 = ' " & Forms!Form1!TxtInfo3 & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
 
DS said:
Can you use DLookUp in SQL?

Test2SQL = "UPDATE table1 IN '" & strPath = DLookup("BackName",
"tblBackPath", "BackID=1") & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)

Thanks
DS
Click to expand...

Sorry, Forgot a BIG thing.
The Dlookup is looking up the path to an external database.
The path is stored in a table in the current database.
Thanks
DS
 
I just though using SELECT and/or WHERE would keep it all SQL as oppose
to jumbling it up with the DLookup thing. Is there any downsides to
this, would a SELECT and/or WHERE be faster and more solid, I don't know
so I'm asking.
Thank you for your input.
DS
 
Rick said:
But it's not SQL until you do the Execute and at that point the DLookup is not
involved. The DLookup is only used to construnct a *string*. The string that
is *executed* has no DLookup in it.
Click to expand...
OK, great then I can put this to bed.
Thanks Rick
DS
 
Back
Top