Help witha modual/Sql in Access xp

[alastair69]

I use the following code and i get the following error message:

The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I used the following code:

Sub CreateTopSQL()

On Error GoTo Err_CreateTopSQL
Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim strSQL As String
Dim strTopVal As String

Set db = CurrentDb
strTopVal = InputBox("Enter TOP value:")
strSQL = "SELECT TOP " & strTopVal & " ClientLookUp,ClientID, Address1LookUp, Address2LookUp,Address3LookUp,Address4LookUp," & _
" PostCode, Classification, ContactName, ContactPhone,ContactFax,WebAddress,Owner,Lab,Contractor,AccountRef,Status,TermsAgreed," & _
" ContactType,AccountManager, FROM JT Client List " & _
" ORDER BY RandomNumber([ClientID]);"
db.QueryDefs.Delete "qry Random Clients Temp"
Set qdf = db.CreateQueryDef("qry Random Clients Temp", strSQL)

Exit_CreateTopSQL:
Exit Sub

Err_CreateTopSQL:
If Err.Number = 3265 Then 'query not found
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTopSQL
End If
End Sub

Then i called it in the following way:-

Private Sub cmb_Assign_Click()
On Error GoTo Err_cmb_Assign_Click


Call CreateTopSQL
DoCmd.OpenQuery "qry Random Clients", acNormal, acEdit

Exit_cmb_Assign_Click:
Exit Sub

Err_cmb_Assign_Click:
MsgBox Err.Description
Resume Exit_cmb_Assign_Click

End Sub

All help will be of use please help.

Alastair

 

[RoyVidar]

You have an extra comma between "AccountManager" and "FROM", and the table name will need [brackets], cause it contains spaces.

Hint, use

debug.print strSQL

then either study the code in the immediate pane (ctrl+g), or copy the sql from there and paste it into the sql view of the QBE, then it should either run, or provide means to correct the query.

I'd also either provide an alternate value, or at least test what's entered thru the input box to avoid errors due to that.

 

[alastair69]

Thank you very much.

I will try your suggestions

 

[alastair69]

You have an extra comma between "AccountManager" and "FROM", and the table name will need [brackets], cause it contains spaces.

Hint, use

debug.print strSQL

then either study the code in the immediate pane (ctrl+g), or copy the sql from there and paste it into the sql view of the QBE, then it should either run, or provide means to correct the query.

I'd also either provide an alternate value, or at least test what's entered thru the input box to avoid errors due to that.

_________________________________________________________________

were abouts in the code should the "debug.print strSQL " go, does it matter.

 

[RoyVidar]

After you've assigned the string, but before it is appended to the querydef.

 

[alastair69]

Thank you the sql statement would appear to work.