Solved: SQL Variable

[jmentor]

I have the following SQL statement

strSQL = "SELECT DISTINCTROW * "
strSQL = strSQL & "FROM tblOrder "
strSQL = strSQL & "WHERE tblOrder.[Order#]= " & ctlId & ";"


I also have a control on the form txtSql which contains the
name of a table(s). I would like the table shown in the control
to be table for the SQL statement above so I need to know what
to put in the place of tblOrder (variable or such like).

Thanks

[sandam]

Try this -> strSQL = strSQL & "FROM " & txtSql.Text & " "

It should work for ya

HTH
Andrew;?

[Norie]

Does this work?

Replace MyControl with the name of the control with the table name.


strSQL = "SELECT DISTINCTROW * "
strSQL = strSQL & "FROM " & txtSql!MyControl
strSQL = strSQL & "WHERE " & & txtSql!MyControl & ".[Order#]= " & ctlId & ";"

[jmentor]

Hi Norie

I put you code into the sql statement but got the following;

Error 2185 You can't reference a property or method for a
control unless the control has the focus.

I rummaged around a bit and found this on the web

Don't reference the controls with .Text
Use .Value, or leave it blank (.Value is the Default).

Now it works fine

Thanks for your help

[jmentor]

Just spotted your post now

We must have crossed at the same time.

Thanks

[xCav8r]

Just a tip about the code.


strSQL = "SELECT DISTINCTROW * "
strSQL = strSQL & "FROM " & txtSql!MyControl
strSQL = strSQL & "WHERE " & & txtSql!MyControl & ".[Order#]= " & ctlId & ";"

Would be better as this...


strSQL = "SELECT DISTINCTROW *" _
& " FROM " & txtSql!MyControl _
& " WHERE " & txtSql!MyControl & ".[Order#]=" & ctlId.Value & ";"


Note the spacing: you can save yourself headaches by being consistent with where you place your spaces. :wink: