SQL and DLookup

[Rick Brandt]

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

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()).

 

[Rick Brandt]

Thanks, Rick.
But how do I write this thing? Do I not use DLookup and use SELECT or
WHERE?
Thanks
DS

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

DoCmd.RunSQL (Test2SQL)

 

[Rick Brandt]

OK This works , but should I really be using DLookup Here?
Should it be WHERE or SELECT?
Thanks
DS

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.

 

[Rick Brandt]

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

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.

 

[DS]

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

 

[DS]

Thanks, Rick.
But how do I write this thing? Do I not use DLookup and use SELECT or
WHERE?
Thanks
DS

 

[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]

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

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

 

[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

 

[DS]

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.

OK, great then I can put this to bed.
Thanks Rick
DS