code help?

[skate]

There is something wrong with my code. I think it has to do with the value of my combobox being numeric. Any suggestions?

Private Sub Name_Combo_Change()
Dim txtSearchString As Variant
Dim strSQL As String
txtSearchString = Me.Name_Combo.Value
strSQL = "SELECT Month.SIndex, Month.MIndex, Month.MOrder, Month.Month, Month.[Job Hrs], Month.[Eng Hrs], Month.[Stat Hrs],Month.[Bank Hrs],Month.[Total Hrs] FROM [Month Entry] INNER JOIN [Month] ON [Month Entry].MOrder = Month.Month WHERE (((Month.SIndex) Like '1*')) ORDER BY Month.MOrder"
strSQL = strSQL & "WHERE (([Month].[SIndex]) Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY [Month].[MOrder]"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!Name_Combo.SetFocus
End Sub

 

[PaulBricker]

Try this in the Like string.

Like """ & txtSearchString & "*""


Paul

 

[JohnYingling]

Why not use "As String"?
Dim txtSearchString As String
because any value in the SQL command must be in string format.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[MikeLoon]

The best way to debug sql with variables is to debug print the sequal statement with the variables assigned. Copy the immediate window and paste the text into you query designer. Try to run the query from there and you should be able to see the problem.

 

[SBendBuckeye]

Always make sure you add either a leading or trailing space to your statements so nothing abutts when you concatenate it (eg FROM tblTableWHERE, etc). Also, you can create your SQL in QBE and then select the show SQL option to use for cut and paste.

Good Luck!

 

[JohnLowell]

Your SQL statement is screwed up.
You have 2 Where clauses and 2 Order By's