SQL beginner question... [Solved]

Can you insert SQL code directly into a Access Module and if so can someone provide me with a example statement to update a blank field with a variable?

Thanks for any help I am pulling my hair out. :P

VB Code:

Dim dbsCurrent As Database
Dim qryTest As QueryDef
 
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("UpdateQuery")
qryTest.SQL = "UPDATE [TestTable] SET [TestTable].[Field1] = "test"
WHERE ((([TestTable].[Field1])=""));"

This is pretty much what I want to do but I would like to replace the "test" with a variable if that is at all possible. Can anyone help out with this? Thanks in advance

You'll have to set your variable somewhere, through user input or however you're filling it, then try this...

VB Code:

"UPDATE [TestTable] SET [TestTable].[Field1] = '" & test & "' WHERE ((([TestTable].[Field1])=""));"

Note - you don't need the single quotes before and after the variable if the variable is numeric, only need them for text.

VB Code:

Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim stTest as string
 
stTest = "what I want"
 
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("UpdateQuery")
qryTest.SQL = "UPDATE [TestTable] SET [TestTable].[Field1] = " & stTest & _ "WHERE ((([TestTable].[Field1])=""));"

Tried this with the single quotes and without and I keep getting a syntax error.... beginning to think its impossible =/

VB Code:

qryTest.SQL = "UPDATE [TestTable] SET [TestTable].[Field1] = '" & stTest & "' WHERE ((([TestTable].[Field1]) Is Null));"

Keep it all on the same line, your line continuation is throwing it off, and remove the quotes where you're looking for the empty field, us is null instead.

Will try that when I get to work in the morning... gawd I hope it works. Thanks for the help :D

Quote:

---

Originally Posted by demotivater

VB Code:

qryTest.SQL = "UPDATE [TestTable] SET [TestTable].[Field1] = '" & stTest & "' WHERE ((([TestTable].[Field1]) Is Null));"

Keep it all on the same line, your line continuation is throwing it off, and remove the quotes where you're looking for the empty field, us is null instead.

---

Thanks alot man got it running was a big help.