SQL to FrontPage 2003

[hrider1]

What I am trying to build is a form that allows the user to input two dates.
The first being a start Date then an Ending Date. I have the form built with
the first textbox named TxtStart and the second being TxtEnd and I’ve tried
the following statement but it’s not working.

WHERE (Shipments.ShipDate BETWEEN CONVERT(DATETIME, ‘::TxtStart::', 102)
AND CONVERT(DATETIME, '::TxtEnd::, 102))

Also could someone give me the name of a good book that would help me when I
am building “ASP†pages with SQL statements that need to be changed?

Thanks,

Jim

 

[Kevin Spencer]

What is the error or behavior you are experiencing which makes you say "it's
not working?"

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[hrider1]

I am not sure what the error is because when I push the "verify button" I get
back "The custom query contains errors" and this is all I get.

Jim

 

[Kevin Spencer]

Okay, next questions:

There seems to be a missing single quote in the statement:

CONVERT(DATETIME, '::TxtEnd::, 102))

Is that a typo in your message? If not, that is your problem.

Are the form field names spelled correctly? Are both form field values
convertible to DateTime? Are you working with a SQL Server database? Have
you tried pasting the query into SQL Query Analyzer and running it with
literal date strings substituted for the form field references?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[hrider1]

Yes that's a typo on my part. Yes the query has no problem running if I
input dates. I've listed the complete query with the dates in it below:

SELECT Shipments.Shipper_Reference, Shipments.Consignee_Company,
Shipments.Consignee_Contact, Shipments.Consignee_Address1,
Shipments.Consignee_City, Shipments.Consignee_State,
Shipments.Consignee_PostalCode, Shipments.Consignee_Phone, Shipments.Country,
Shipments.Fuel_Surcharge, Shipments.Total,
Shipments.UserName, Shipments.ShipDate, Shipments.ServicePlaintext,
Packages.Tracking_Number,
Shipments.Business_Unit, Shipments.Shipping_Group
FROM Shipments INNER JOIN
Packages ON Shipments.ShipmentGUID =
Packages.ShipmentGUID
WHERE (Shipments.Business_Unit <> '') AND (Shipments.Shipping_Group <>
'') AND (Shipments.ShipDate BETWEEN CONVERT(DATETIME,
'2006-03-01 00:00:00', 102) AND CONVERT(DATETIME,
'2006-03-21 00:00:00', 102))
ORDER BY Shipments.Shipper_Reference, Shipments.ShipDate,
Shipments.ServicePlaintext


But it seems that when I change the query as listed below it stops working
and I am not sure to why:

SELECT Shipments.Shipper_Reference, Shipments.Consignee_Company,
Shipments.Consignee_Contact, Shipments.Consignee_Address1,
Shipments.Consignee_City, Shipments.Consignee_State,
Shipments.Consignee_PostalCode, Shipments.Consignee_Phone, Shipments.Country,
Shipments.Fuel_Surcharge, Shipments.Total,
Shipments.UserName, Shipments.ShipDate, Shipments.ServicePlaintext,
Packages.Tracking_Number,
Shipments.Business_Unit, Shipments.Shipping_Group
FROM Shipments INNER JOIN
Packages ON Shipments.ShipmentGUID =
Packages.ShipmentGUID
WHERE (Shipments.Business_Unit <> '') AND (Shipments.Shipping_Group <>
'') AND (Shipments.ShipDate BETWEEN CONVERT(DATETIME,
'::TxtStart::', 102) AND CONVERT(DATETIME,
'::TxtEnd::', 102))
ORDER BY Shipments.Shipper_Reference, Shipments.ShipDate,
Shipments.ServicePlaintext

At this point I get the message theirs an erros in the query whe I push the
"verify button"

Jim