SQL, Dates, Results Wiz?

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

SQL, Dates, Results Wiz?

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> SQL, Dates, Results Wiz?

Page: [1]

akreci

Posts: 3
From: West Palm Beach, FL US
Status: offline

SQL, Dates, Results Wiz? - 2/28/2001 20:19:00

This is the issue I am using frontpage results wiz to get data from my access database I use this custom SQL to access the data as you can see I have set 2 criteria:

SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = ::Aval_Date:: ORDER BY product_var ASC

MFG which is the manufactur set in the data base as text and Aval_Date which is a date and is in date/time format. The problem is when a user fills in the criteria feilds the results page makes the users put # sings around the date there for I guess qualifing it as a date or number field,if they are not there then no results are found. is there a way to eliminate the # signs or at lease hide them from the users aside from setting the format in the data base as text.

Thanks for your time

[This message has been edited by akreci (edited 02-28-2001).]

Spooky

Posts: 26597
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: SQL, Dates, Results Wiz? - 2/28/2001 14:53:00

Sure, change the SQL to :

SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = #::Aval_Date::# ORDER BY product_var ASC

------------------
Spooky
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!

(in reply to akreci)

akreci

Posts: 3
From: West Palm Beach, FL US
Status: offline

RE: SQL, Dates, Results Wiz? - 2/28/2001 15:48:00

I tried this and many other ways of concatenating the # signs into the query and this is what I get when I try to verify the query.
The server encountered an error while verifying the query:
Server error: Unable to retrieve schema information from the query:

SELECT * FROM mfgqty3 WHERE (MFG = '1' AND Aval_Date = #2#) ORDER BY product_var ASC

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(MFG = '1' AND Aval_Date = #2#)'.

quote:
Originally posted by Spooky:
Sure, change the SQL to :
SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = #::Aval_Date::# ORDER BY product_var ASC

(in reply to akreci)


Spooky Posts: 26597 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: SQL, Dates, Results Wiz? - 2/28/2001 18:52:00 Hmm, that SQL check is a PITA Change the # back to ' and you should have more success (otherwise modify the gray code in html view) (in reply to akreci)


Guest	RE: SQL, Dates, Results Wiz? - 3/1/2001 23:28:00 Why don't you add the #'s the value you grab from the text box before you pass it to your SQL string using some similar to the script below. That way the users never have to enter them and the SQL string will be happy too. <%txtDate.value = CDate("#"&textDate.value&"#")%> (in reply to akreci)

akreci

Posts: 3
From: West Palm Beach, FL US
Status: offline

RE: SQL, Dates, Results Wiz? - 3/2/2001 17:08:00

I want to thank both of you for all the help with this problem. With a little more fooling around I got it to work using a little bit of both ideas. I all I had to do was put the #'s in the sql like we first figured but I was using the GUI to much and not looking at the actual code. When I put my SQL in the GUI it could not update the bots so I needed to make some changes in the webbot code. I want to give this forum props the replies were quick and very helpful I will use it again!!!!! Good luck to all...

quote:
Originally posted by EleciaE:
Why don't you add the #'s the value you grab from the text box before you pass it to your SQL string using some similar to the script below. That way the users never have to enter them and the SQL string will be happy too.
<%txtDate.value = CDate("#"&textDate.value&"#")%>

(in reply to akreci)