Between Question

What am I doing wrong in the statement:

q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
Box1 & " and " & Box2 & ")"

It gives me the following error message: Incorrect syntax near the
keyword 'and'

the following code is what i am working with but it is not working as
it should. I am trying to pull data selected in the inputbox into
ectlog_holding table and run a report off there but keep getting the
errors. Stated above.

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=ETC;Data
Source=PHNOTES01"
conn.Open

conn.Execute "drop table etclog_holding"
Box1 = Trim(InputBox("Enter starting box#:"))
Box2 = Trim(InputBox("Enter ending box#:"))
q = "Select * Into etclog_holding from etclog where box# Between (" &
Box1 & " and " & Box2 & ")"
rs.Open q, conn, adOpenDynamic, adLockOptimistic

Dim rs1 As New ADODB.Recordset
q = "select * from etclog_holding"
rs1.Open q, conn, adOpenDynamic, adLockOptimistic

Any ideas??On 16 Feb 2005 14:22:39 -0800, pkruti@.hotmail.com wrote:

>What am I doing wrong in the statement:
>q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
>Box1 & " and " & Box2 & ")"

Hi pkruti,

Try this instead:

q = "Select * Into etclog_holding from etclog where box# BETWEEN " &
Box1 & " and " & Box2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(pkruti@.hotmail.com) writes:
> What am I doing wrong in the statement:
> q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
> Box1 & " and " & Box2 & ")"
> It gives me the following error message: Incorrect syntax near the
> keyword 'and'

Have you tried to work out the syntax from reading the topic on
BETWEEN in Books Online? Hint: it's simpler than that you are trying
to make it to be.

Generally, syntax issues are best investigated by using Books Online,
since there are both syntax graphs and examples to work from. It may
take some time first, but it pays back in the long run.

> conn.Execute "drop table etclog_holding"
> Box1 = Trim(InputBox("Enter starting box#:"))
> Box2 = Trim(InputBox("Enter ending box#:"))
> q = "Select * Into etclog_holding from etclog where box# Between (" &
> Box1 & " and " & Box2 & ")"
> rs.Open q, conn, adOpenDynamic, adLockOptimistic
> Dim rs1 As New ADODB.Recordset
> q = "select * from etclog_holding"
> rs1.Open q, conn, adOpenDynamic, adLockOptimistic

There are several things in this snippet I don't like:

1) Creating a table is pointless. Just get the recordset from the
underlying table.
2) Don't use dynamic cursors unless you absolutely have to. And for a
report you don't. Best is to use client-side cursors, which you
by specifying .CursorLocation on the connection object.
3) Do you have validation on InputBox, so that you only get numeric
data from it? Else the user can specify evil things to get your
bacth do other things that you intended. Use parameterized statements
instead. For examples, see
http://authors.aspalliance.com/stev...cles/sprocs.asp

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Hugo that worked =)