Syntax with db.openrecordset

[DuMont]

Hi Guys,

I'm receiving the run-time error '3141' - The SELECT statement includes a reserved word or an argument name..."

Here's the full code...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 Set db = CurrentDb
      
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _
         "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _
         "INSERT INTO [Group1-CAN Top]" & _
         "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between '" & Me.StartDate.Value & "' And '" & Me.EndDate.Value & "' ORDER BY Rnd([service_id]) DESC "
 Set rst = db.OpenRecordset(strSQL)
 Set db = Nothing
Set rst = Nothing

Does anyone see a problem with this code that would cause this?

Thanks,

 

[jdraw]

Try putting square brackets around Name

& Me.Text140.Value & " [CAN - NAME].Name,

You do not need .Value

 

[DuMont]

Like this...?

[CAN - NAME.Name]

 

[MarkK]

If Text140 Is Null, then the SQL will start off . . .

SELECT TOP [CAN - NAME].Name, [CAN - CPT/VOUCHER] . . .

. . . which will obviously fail.

So, the posted code is extremely vulnerable to failure because it lacks validation of a required element.

 

[DuMont]

That makes sense MarkK but the reason I have that there is to select the top values that are entered in the textbox - which is labeled "Required" on the form (Granted instruction will also have to be given as well...)

 

[MarkK]

I wrote a query here with this exact code . . .

SELECT TOP tTestData.* FROM tTestData;

The error I get is . . .

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

You need to validate the contents of Text140 before you use it.

 

[DuMont]

MarkK - How would I go about doing that - Would you have an example perhaps?

Also the code works (if I take out set rst = db.OpenRecordset(strSQL)

...as in I go an open the query *Group1-CAN Top*, the only thing is that I have to open the query in design view, deselect a field, reselect the field, then run it and it has my top selections based on the textbox value.

 

[MarkK]

Check out the IsNull() function, or IsNumeric() or something, like . . .

If IsNumeric(Me.Text140) Then
[COLOR="Green"]   'code that uses Test140
   'put your code here
[/COLOR]Else
   MsgBox "Text140 is not valid"
End If

Also the code works (if I take out set rst = db.OpenRecordset(strSQL)

Maybe the code runs, but as posted it does nothing. It constructs an SQL statement and opens a recordset, and closes the recordset, but nothing is changed. If you remove the OpenRecordset line, it will solve the error because you'll never use the potintially faulty SQL.

Does that make sense?

 

[pbaldy]

You might find this helpful:

http://www.baldyweb.com/ImmediateWindow.htm

I think you have a problem with the SQL, which looks like it will come out

SELECT TOP...
INSERT INTO...

which would be invalid. In any case, seeing the finished SQL will help resolve the issue.