Syntax Error on Select 1

[techsponge]

I have the following code for a label. when the user clicks the label above a column it will resort the continuous form by the column clicked.

I am getting a Syntax error message on the select statement, although when I paste the SQL into a new query it runs fine?

Begin SQL

Me.Form.RecordSource = "SELECT TblMasterGameList.FrmName, TblMasterGameList.TktType, TblMasterGameList.TktBin, TblMasterGameList.TopPays, TblMasterGameList.PurchDate, TblMasterGameList.TktPrice, TblMasterGameList.TktCount, TblMasterGameList.GameName, TblMasterGameList.WashStampNum FROM TblMasterGameList WHERE (((TblMasterGameList.FrmName) Not Like "TPGC*") AND ((TblMasterGameList.TktType)="5W") AND ((TblMasterGameList.TktBin) Is Null) AND ((TblMasterGameList.TktPrice)=1) AND ((TblMasterGameList.CloseDate) Is Null))ORDER BY TblMasterGameList.GameName;"

Any Ideas?

Private Sub lblName_Click()
'change the recordset
Me.Form.RecordSource = "SELECT TblMasterGameList.FrmName, TblMasterGameList.TktType, TblMasterGameList.TktBin, TblMasterGameList.TopPays, TblMasterGameList.PurchDate, TblMasterGameList.TktPrice, TblMasterGameList.TktCount, TblMasterGameList.GameName, TblMasterGameList.WashStampNum FROM TblMasterGameList WHERE (((TblMasterGameList.FrmName) Not Like "TPGC*") AND ((TblMasterGameList.TktType)="5W") AND ((TblMasterGameList.TktBin) Is Null) AND ((TblMasterGameList.TktPrice)=1) AND ((TblMasterGameList.CloseDate) Is Null))ORDER BY TblMasterGameList.GameName;"
'set the color to red to show the column sort order
Me.lblName.ForeColor = vbRed
'set the color to black to show the column is not sorted
Me.LblDateRec.ForeColor = vbBlack
Me.LblCount.ForeColor = vbBlack
Me.LblTopPay.ForeColor = vbBlack
Me.LblStamp.ForeColor = vbBlack
End Sub

 

[TheAceMan1]

How are ya techsponge . . .

Perhaps this missing space in red:

[blue]Is Null))[COLOR=red red] [/color]ORDER BY TblMasterGameList.GameName;"[/blue]

See Ya! . . . . . .

 

[techsponge]

Thanks AceMan,

Gave that a try and immediatly got the following:

"Expected: end of statement" and it hightlighted the following:

Me.Form.RecordSource = "SELECT TblMasterGameList.FrmName, TblMasterGameList.TktType, TblMasterGameList.TktBin, TblMasterGameList.TopPays, TblMasterGameList.PurchDate, TblMasterGameList.TktPrice, TblMasterGameList.TktCount, TblMasterGameList.GameName, TblMasterGameList.WashStampNum FROM TblMasterGameList WHERE (((TblMasterGameList.FrmName) Not Like [red][b]"TPGC*"[/b][/red]) AND ((TblMasterGameList.TktType)="5W") AND ((TblMasterGameList.TktBin) Is Null) AND ((TblMasterGameList.TktPrice)=1) AND ((TblMasterGameList.CloseDate) Is Null)) ORDER BY TblMasterGameList.GameName;"

 

[PHV]

And what about this ?
Me.Form.RecordSource = "SELECT FrmName, TktType, TktBin, TopPays" _
& ", PurchDate, TktPrice, TktCount, GameName, WashStampNum" _
& " FROM TblMasterGameList" _
& " WHERE FrmName Not Like [!]'[/!]TPGC*[!]'[/!] AND TktType=[!]'[/!]5W[!]'[/!] AND TktBin Is Null" _
& " AND TktPrice=1 AND CloseDate Is Null ORDER BY GameName"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[techsponge]

PHV

Many thanks for your help!!!

A star for you!