Input Box to SQL Statement

[CJCESENA]

I am trying to take the results of an inputbox and place it into a SQL statement. Here is the Code:

Function Technician()
Dim str As String
Dim strsql As String


str = InputBox("Type in Technician's Name")
strsql = "Select * from Master where Technician_Name = " & str & "; "

DoCmd.runsql strsql
End Function

I get run time error '3075'

Syntax error (Missing operator) in query expression 'Technician_Name = XXXXXXX'.


'XXXXXXX' represents whatever I put in the input box.


Any ideas?

 

[sfreeman@co.mer]

Try this...

strsql = "Select * from Master where Technician_Name = "' & str & "'; "

Added two apostrophes, one after and one before your middle set of quotes.

Haven't tested, so not SURE if this works.

 

[CJCESENA]

strsql = "Select * from Master where Technician_Name = "' & str & "'; "



I am receiving the same error code as before. How frustrating!


Thanks for the input thought!

 

[sfreeman@co.mer]

ooops, my bad



the first apostrophe goes BEFORE, not after the first middle quote...

should be one before and one after the middle set of quotes.

 

[CJCESENA]

I think that fixed that error. Thanks

I am now getting:


runtime error 2342
A runSql action requires an arguement consisting of a Sql statement.

Thanks

 

[Rich]

Run Sql is for action queries, what are you trying to do?

 

[CJCESENA]

I am trying to take the results of an inputbox and place it into a SQL statement.

Am I using 'runsql' incorrectly?

 

[WayneRyan]

CJ,

The DoCmd.RunSQL does not return anything. It is used to Insert, Delete
or Update records only. You can't retrieve information from tables with it.

As an alternative:

Me.SomeField = Nz(DLookUp("[SomeField]", "Master", "[Technician_Name] = "' & str & "'"), "")

This will get the value from SomeFIeld in table Master where the name of
the Tecnhician is in the control str. If there are no matches, it will return
an empty string (Nz function).

Or, you can use a recordset.

Wayne