Date Range Queries Using Access and Front Page 200 (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Peter Schwartz -> Date Range Queries Using Access and Front Page 200 (2/18/2002 21:40:14)

I have had a lot of trouble using the DRW in FP2002 to allow a date range query using an Access database.

This is the SQL I am now using in a test document with SQL pulled from the Access Query Design Tool.

SELECT Test1.Date, Test1.Rating, Test1.State
FROM Test1
WHERE (((Test1.Date) Between #::StartDate::# And #::EndDate::#))

The query works if I use specific dates instead of "StartDate" and "EndDate" to specify forms. But I get a SQL syntax error otherwise. How can I identify the text
boxes for entering dates on the web without getting the SQL? The datatype for the "Date" field is date/time.

I have reviewed the other postings for similar problems in the forum, and find they only help me to a point. The posting at 9/12/2001 at 16:04 by Jasonenneking is similar to my problem, and he solves it by bracketing the date with # characters in the input form. His application was for an intranet, though. I can't ask my website visitors to do that. Is there any way I can use the # character in the SQL and not get the errors he reports (which I get, also)?

The posting on 06/21/2001 at 16:44 by dragonspider also is useful: my question there, though, is whether it is accurate to have the & character flush with the date form name on the left side and have it separated by a space on the right side?

Thank you.




ginnie -> RE: Date Range Queries Using Access and Front Page 200 (2/19/2002 10:27:56)

The problem might be with Date being a reserved word in Access. Try changing the name to something else (ideally), or adding brackets around the column named "Date" in your query:

SELECT [Date], Rating, State
FROM Test1
WHERE [Date] Between #StartDate# And #EndDate#

Don't know about all of the colons and parentheses around portions of your query. I think that's the way FrontPage generates them, but I don't use them in my queries, but then I also don't use the DRW so they might be necessary for the DRW.

Give it a try.



ginnie
Global Presence Web Design



Chameleon -> RE: Date Range Queries Using Access and Front Page 200 (2/19/2002 14:47:13)

I use alot of these queries in the DRW the syntax I use is ...
(Date BETWEEN '::BeginningDate::' AND '::EndingDate::')"

 



ginnie -> RE: Date Range Queries Using Access and Front Page 200 (2/19/2002 15:43:33)

Hi Cameleon,

Are you by any chance using SQL Server as your database instead of Access? Date is a reserved word in Access and date fields need to be surrounded by # signs (when using an Access database) whereas when using SQL Server the syntax is to use the single quote around date fields as you describe. I don't think "Date" is a reserved word in SQL Server either.

ginnie
Global Presence Web Design



Chameleon -> RE: Date Range Queries Using Access and Front Page 200 (2/19/2002 16:24:11)

You got me. It is indeed SQL. I'm having one of those days (for about 2 weeks now). Sorry for any confusion Peter, I wish I could help but I don't use access wth my web DB's.

 



Peter Schwartz -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 0:39:08)

Ginnie and Chameleon,

Thank you for your suggestions. I was finally able to solve 80 percent of my problem in the following way. The key to the DRW workaround for date range queries is to leave the "'" character surrounding the ::DateField:: parameter in the DRW. After saving file, go into the HTML and change the "'" characters surrounding the form names to "#". This is in the grey webbot code. Save the changes and they cascade to the maroon webbot code. When published, this page will initially display an error, but you can enter data into the date fields and return results.

To eliminate the error message, use this workaround from William Lee (who helped me enormously with this problem, in general). See http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#return. This tip involves replicating the page, stripping the DRW from the first page (but leaving the forms) and having both post to the second page.

This fix works well, but leaves one problem. In pages with other search parameters besides the date range fields, queries will often want to leave the date fields blank. But this returns an error message: "Syntax error in date in query expression '(ActionType LIKE '%%%' AND ReleaseNo LIKE '%33-%' AND Summary LIKE '%%' AND (ReleaseDate BETWEEN ## AND ##))'." How can I tweak this page so that entering data in the date range forms is not necessary? Thanks very much.

Peter







ginnie -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 11:11:11)

I don't see any reason why you should have to do all that. Did you change the column names for the date field to something other than "Date", like "DatePosted" or "OrderDate" or anything but "Date"?

ginnie
Global Presence Web Design



Peter Schwartz -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 12:13:29)

Yes, I did try changing the name. That didn't help. There is definitely a bug in FP that limits the ability to handle date ranges -- MS acknowledges it on their website. The only way to fix it is to go into the code. I agree that it seems pretty silly that such a straightforward problem should require such a convoluted solution.




William Lee -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 12:27:45)

Hi Peter,
I got something working at www.biblewitness.com/db for the problem relating to the latter part of your project.

The only problem with your query is that if the user fills ONLY the Start and End dates and leave out the other textboxes ,ALL the records will be returned even though the records fall outside the date range.


Rgds,
William Lee

William Lee



Peter Schwartz -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 12:34:38)

That looks great William. How were you able to do it?




William Lee -> RE: Date Range Queries Using Access and Front Page 200 (2/20/2002 12:53:04)

It wasn't as easy as it should be.
I had to detect if the 2 date boxes are filled and if not, response.redirect with a modified sql string value.
If the dates are filled, then the form will submit normally and I have a hidden form field strSQL that passed to the DRW.
The strSQL then gets added to the end of fp_sQry.

Anyway, what is your comment regarding my earlier post about returning ALL records?


William Lee



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875