Access SQL to ASP SQL not working ... need help please

[David]

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped) >=0) AND
(OrderLines.Sel ectAll=-1) AND (PCBForecast.He ldMarker='Sched uled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.S hipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.S hipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Apprecaite your advise

Thanks

David

[Anthony Jones]

"David" <da*********@sc ene-double.co.ukwro te in message
news:11******** **************@ e3g2000cwe.goog legroups.com...

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped) >=0) AND
(OrderLines.Sel ectAll=-1) AND (PCBForecast.He ldMarker='Sched uled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.S hipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.S hipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Apprecaite your advise

The first thing you should do when ever you have a problem like this is to
check the SQL that is actually being executed.

Response.Write strQuery
Response.End

Just before you would normally exectute it. Check that the SQL is what you
expect it to be.

Another useful piece of advise would be specific! "I cannot get either to
work" tells us nothing.

Did it ignore your changes? Produce the wrong results?
Generate an error? If so what did it say? I dunno but it might be a clue
:P
There is a space before the AND right?

Thanks

David

[Bob Barrows [MVP]]

David wrote:

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped) >=0) AND
(OrderLines.Sel ectAll=-1) AND (PCBForecast.He ldMarker='Sched uled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.S hipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.S hipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Two things:
1. Datetime literals in SQL Server are delimited by quotes ('), not
hashmarks (#).
2. There are no VBA functions such as Now() available in SQL Server. The
variant of sql used by SQL Server is called Transact-SQL (sometimes
called T-SQL for short). Transact-SQL has builtin functions for doing
many of the things VBA functions are used for in Jet. GETDATE() is the
T-SQL equivalent to Now().

You may want to have a look at this article:
http://www.aspfaq.com/show.asp?id=2214
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Anthony Jones]

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:uh******** ******@TK2MSFTN GP06.phx.gbl...

David wrote:

Hi,

I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.

The working SQL I have in asp is:-

strQuery = strQuery & " HAVING ((OrderQuantity-Sum(QtyShipped) >=0) AND
(OrderLines.Sel ectAll=-1) AND (PCBForecast.He ldMarker='Sched uled'))"

I need to add the following but keep getting errors saying my SQL is
incorrect.

This is the additional part which should also be on this line:-

AND ((PCBForecast.S hipETA) Between Now() And #12/30/3000#))"

or another variant I require

AND ((PCBForecast.S hipETA)=#12/31/3000#))"

What is the correct way to write both of these, as I cannot get either
to work in asp, yet they work in Access.

Two things:
1. Datetime literals in SQL Server are delimited by quotes ('), not
hashmarks (#).
2. There are no VBA functions such as Now() available in SQL Server. The
variant of sql used by SQL Server is called Transact-SQL (sometimes
called T-SQL for short). Transact-SQL has builtin functions for doing
many of the things VBA functions are used for in Jet. GETDATE() is the
T-SQL equivalent to Now().

You may want to have a look at this article:
http://www.aspfaq.com/show.asp?id=2214

Did I miss something that points to SQL Server?

>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Anthony Jones wrote:

>
Did I miss something that points to SQL Server?

Nope. I misread it.

To the OP:
Show us the vbscript code that is failing. Better yet, read these:

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.