SQL statement

[Dsperry101 via AccessMonster.com]

I'm trying to fill a combo box with a sql statement. The first one works:

sqlString = "SELECT [tblNewMwo].[Work Order Number],[tblNewMwo].[Requestor]
" & _
", [tblNewMwo].[Dept], [tblNewMwo].[Problem], [tblNewMwo].
[Equipment] " & _
"FROM tblNewMwo ORDER BY [tblNewMwo].[Work Order Number] ;"
MsgBox sqlString
cboMwoselect.RowSource = sqlString
cboMwoselect.RowSourceType = "Table/Query"
cboMwoselect.BoundColumn = 0

the next one gives me an empty box:

whereString = Environ("FULL_NAME")
cboMwoselect.SetFocus
sqlString = "SELECT [tblNewMwo].[Work Order Number], [tblNewMwo].
[Requestor]" & _
", [tblNewMwo].[Dept], [tblNewMwo].[Problem], [tblNewMwo].
[Equipment] " & _
"FROM tblNewMwo ORDER BY [tblNewMwo].[Work Order Number] " & _
"WHERE [tblNewMwo].[Requestor] like " & "'" & whereString &
"'" & ";"
MsgBox sqlString
cboMwoselect.RowSource = sqlString
cboMwoselect.RowSourceType = "Table/Query"
cboMwoselect.BoundColumn = 0
'cboMwoselect.Requery

whereString is returning my name and it is in the [Requestor] field
Tried the requery line but no help.

Thanks

 

[Guest]

See VBA Help on the Like operator. Not knowing your data, it's hard to be
sure, but your statement does not include a wildcard or other special
character. If you were intending to find hits for a Where string = "John"
amongst data of the following you would need the following wildcard
characters:

"John Smith" Like "'" & WhereString & "'*"
"Smith John" Like "*'" & WhereString & "'"
"Dr. John Smith" Like "*'" & WhereString & "'*"

I'd also check that Environ is returning the value you think it should.

Hope that helps.
Sprinks

 

[Dsperry101 via AccessMonster.com]

I found the problem !!
The where statement is part of the select can't be preceded by
orderby !! Thanks for the help.

See VBA Help on the Like operator. Not knowing your data, it's hard to be
sure, but your statement does not include a wildcard or other special
character. If you were intending to find hits for a Where string = "John"
amongst data of the following you would need the following wildcard
characters:

"John Smith" Like "'" & WhereString & "'*"
"Smith John" Like "*'" & WhereString & "'"
"Dr. John Smith" Like "*'" & WhereString & "'*"

I'd also check that Environ is returning the value you think it should.

Hope that helps.
Sprinks

I'm trying to fill a combo box with a sql statement. The first one works:

[quoted text clipped - 29 lines]

Thanks

 

[Guest]

Danny:

The ORDER BY clause should follow the WHERE clause.

"FROM tblNewMwo " & _
"WHERE [tblNewMwo].[Requestor] = '" & whereString & "' " & _
"ORDER BY [tblNewMwo].[Work Order Number]"

Note that you don't need to include the terminating semi-colon in the string
expression.

Ken Sheridan
Stafford, England