471,119 Members | 1,362 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,119 software developers and data experts.

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????

Jul 23 '05 #1
3 1570
On 16 Feb 2005 14:22:39 -0800, pk****@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)
Jul 23 '05 #2
(pk****@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/steve...les/sprocs.asp

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Thank you Hugo that worked =)

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by raghunandan_1081 | last post: by
7 posts views Thread by barias | last post: by
16 posts views Thread by edepperson | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.