Using a defined variable in an SQL string (1 Viewer)

[Phonik]

Hello

Can anyone tell me how I use a variable I have defined within a SQL string.

e.g.

***code start ***

Dim ComputerName as string
Dim Sqlstr as string

ComputerName = Environ("ComputerName")
Sqlstr = "INSERT INTO ComputerName ("ComputerName") SELECT ***This is where I need help including the variable ComputerName I defined above ****

I would really appreciate some help.

Thank you

 

[RoyVidar]

Try

Sqlstr = "INSERT INTO ComputerName (ComputerName) " & _
         "VALUES ('" & ComputerName & "')"

you need to concatenate it's value into the string.

 

[Phonik]

Great stuff!! Thank you so much.

 

[RoyVidar]

You're welcome!

 

[Phonik]

If I wanted to add more afterward, how to I seperate the first concatenated variable from the next?

 

[RoyVidar]

Sqlstr = "INSERT INTO ComputerName (ComputerName, DateField, NumberField) " & _
         "VALUES ('" & ComputerName & "', #" & _
         Format(SomeDate, "yyyy-mm-dd") & "#, " & SomeNumber & ")"

You need to kind of build it like an SQL string looks in the query builders SQL view. Hint, use

Debug.Print Sqlstr

then, from the immediate pane (ctrl+g), copy paste the result into the SQL view of the query builder, then it's supposed to run. With regards to the date formatting, check out http://allenbrowne.com/ser-36.html (I prefer the above format, which is an ISO standard (ISO 8601), but many use the one suggested by Allen Browne.

 

[Phonik]

Thanks Roy-Vidar and my aspologies again for PM'ing you. Its the first time I haver used it.