running sql using variables

[daz321]

i am attempting to run some sql from within VB that selects from an access table. The use selects which records they would like by entering the range of numbers into text boxes, i have used the following sql statement:

Private Sub cmdPrint_Click()
Dim AccessApp As Access.Application
Dim DBPath As String
Dim no1, no2 As Long

no1 = txtFrom1.Text
no2 = txtTo1.Text

DBPath = "C:\My Documents\darryl.mdb"
Set AccessApp = New Access.Application
With AccessApp
.OpenCurrentDatabase DBPath
.DoCmd.RunSQL ("ALTER TABLE LetterSelection ADD [Seq_no] Counter&quot
.DoCmd.RunSQL ("Select * into output from LetterSelection where Seq_no between '" & no1 & " ' and '" & no2 & "'&quot

.CloseCurrentDatabase

End With

End Sub

I am getting a "Data type mismatch in criteria expression" error and the debug points to the "select *..." line. I have checked the layout of the table (after the new column has been added) and the format of Seq_no is Long, so i am not sure what the problem is.

 

[Norris68]

If you are using number you don't need the single quote marks.
Use:
.DoCmd.RunSQL ("Select * into output from LetterSelection where Seq_no between " & no1 & " and " & no2)

 

[sunaj]

Hi,

I think you might be confusing you data types. If Seq_no is a number the ranges should not be in quotes:

"Select * into output from LetterSelection where Seq_no between " & TextFrom1.text & " and " & txtTo1.Text

By the way usless you are doing something else there is no need to open an Access application to execute an sql Statement. Use ADO recordset object instead.



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[daz321]

i forgot to say thank you.

 

[sunaj]

daz321,
I hope you figured it out and that a small typo didn't confuse the content, the sentence should of course have read:

By the way, unless you are doing something else there is no need to open an Access application to execute an sql Statement. Use ADO recordset object instead.


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'