Main Page 
PC Review
Forums
Newsgroups
Microsoft Word
Microsoft Frontpage
Date ranges in database results with a defined variable
Forums
Newsgroups
Microsoft Word
Microsoft Frontpage
Date ranges in database results with a defined variable
 |
Date ranges in database results with a defined variable |
|
|
Thread Tools | Rate Thread |
23-09-2003, 11:20 PM
|
#1 |
|
Guest
Posts: n/a
|
Date ranges in database results with a defined variable
I'm trying to create a more efficient way of changing the
date range criterion I use in database results criterion. I frequently have to change the database results properties across a series of pages to alter the dates of articles displayed on those pages. The custom query I currently use is: SELECT * FROM Articles WHERE ("Article Date" Between #7/1/2003# AND Date( )) ORDER BY "Article Date" DESC I want to replace the hard coded date #7/1/2003# with a variable that I can set in the source of the page, or better yet, on a single include page that would be pulled into all the pages using this same database query. I have done this successfully with other types of queries, but I can't figure out the right syntax when it comes to the date property. Here is how I am defining the variable in HTML view: <% Dim MyDate Date = "9/1/2003" %> And here are several variations of the syntax I tried to use for my defined variable. I placed and tested each of these within the SQL query in the HTML view. All result in errors when I try to view my page in a browser. "::MyDate::" ""::MyDate::"" '%::MyDate::%' #::MyDate::# I believe that I have the syntax wrong where I am trying to insert my defined variable into the SQL query in HTML view. Can anyone provide help? Thanks. |
|
|
24-09-2003, 02:31 AM
|
#2 |
|
Guest
Posts: n/a
|
re: Date ranges in database results with a defined variable
One possibility, certainly, is to set up an Access query
named CurArticles, ala: SELECT * FROM Articles WHERE "Article Date" >= >#7/1/2003# Then, use the CurArticles query rather than the Articles table as the DRW's datasource. Otherwise, if you're running the DRW page from a form, create a file named begdate.inc that contains this code: <input name="begdate" type="hidden" value="7/1/2003"> Then, ssi-include this page inside the form, and configure the DRW to use begdate as a query field. Otherwise, create a page named begdate.htm that contains this code: <html> <body> <script>begdate = "7/1/2003"</script> </body> </html> Use an Include Page component to include this <body> section in the page that links to your DRW page, and then change the hyperlink to: <a href="drwpage.asp?begdate=<script>document.write (begdate);</script>"> and again use begdate as a query field in the DRW itself. Jim Buyens Microsoft FrontPage MVP buyensj@interlacken.com http://www.interlacken.com Author of: *------------------------------------------------------* |\----------------------------------------------------/| || Microsoft Office FrontPage 2003 Inside Out || || Microsoft FrontPage Version 2002 Inside Out || || Web Database Development Step by Step .NET Edition || || Troubleshooting Microsoft FrontPage 2002 || || Faster Smarter Beginning Programming || || (All from Microsoft Press) || |/----------------------------------------------------\| *------------------------------------------------------* >-----Original Message----- >I'm trying to create a more efficient way of changing >the date range criterion I use in database results >criterion. I frequently have to change the database >results properties across a series of pages to alter the >dates of articles displayed on those pages. The custom >query I currently use is: > >SELECT * FROM Articles WHERE ("Article Date" Between >#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC > >I want to replace the hard coded date #7/1/2003# with a >variable that I can set in the source of the page, or >better yet, on a single include page that would be >pulled into all the pages using this same database >query. I have done this successfully with other types >of queries, but I can't figure out the right syntax when >it comes to the date property. > >Here is how I am defining the variable in HTML view: > ><% >Dim MyDate >Date = "9/1/2003" >%> > >And here are several variations of the syntax I tried to >use for my defined variable. I placed and tested each of >these within the SQL query in the HTML view. All result >in errors when I try to view my page in a browser. > >"::MyDate::" >""::MyDate::"" >'%::MyDate::%' >#::MyDate::# > >I believe that I have the syntax wrong where I am trying >to insert my defined variable into the SQL query in HTML >view. Can anyone provide help? Thanks. > >. > |
|
|
|
25-09-2003, 03:54 AM
|
#3 |
|
Guest
Posts: n/a
|
re: Date ranges in database results with a defined variable
Thank you for all of the detailed suggestions.
Unfortunately, due the large number of pages I have that use slightly different queries, it would be cumbersome to create and modify a large number of queries in Access as you outlined in your first suggestion. Also, I can't use the second two suggestions exactly as written because I get a lot of traffic to the pages in question from search engines and places where links to the pages are already on other people's sites, so I can't limit access to my pages from a form or other links that I can fully control. I tried to modify the approach in your third option so that I wouldn't have to use a different URL for the page. I defined the variable with a <script>begdate = "7/1/2003"</script> tag in a page that is then included in the DRW page, and I used begdate as a query field in the DRW itself. Unfortunately, I get errors in the DRW region when I view the page. Placing begdate in the DRW query results in the message "Too few parameters. Expected 1." Using :: begdate:: results in the message "One or more form fields were empty." I think I am still missing something some key characters either where I define the variable in the <script> tag, or when I place begdate in the DRW. It's probably some basic syntax issue that I just don't understand. Any suggestions? >-----Original Message----- >One possibility, certainly, is to set up an Access query >named CurArticles, ala: > >SELECT * FROM Articles WHERE "Article Date" >= >#7/1/2003# > >Then, use the CurArticles query rather than the Articles >table as the DRW's datasource. > > >Otherwise, if you're running the DRW page from a form, >create a file named begdate.inc that contains this code: > ><input name="begdate" type="hidden" value="7/1/2003"> > >Then, ssi-include this page inside the form, and >configure the DRW to use begdate as a query field. > > >Otherwise, create a page named begdate.htm that contains >this code: > ><html> ><body> ><script>begdate = "7/1/2003"</script> ></body> ></html> > >Use an Include Page component to include this <body> >section in the page that links to your DRW page, and then >change the hyperlink to: > ><a href="drwpage.asp?begdate=<script>document.write >(begdate);</script>"> > >and again use begdate as a query field in the DRW itself. > >Jim Buyens >Microsoft FrontPage MVP >buyensj@interlacken.com >http://www.interlacken.com >Author of: >*------------------------------------------------------* >|\----------------------------------------------------/| >|| Microsoft Office FrontPage 2003 Inside Out || >|| Microsoft FrontPage Version 2002 Inside Out || >|| Web Database Development Step by Step .NET Edition || >|| Troubleshooting Microsoft FrontPage 2002 || >|| Faster Smarter Beginning Programming || >|| (All from Microsoft Press) || >|/----------------------------------------------------\| >*------------------------------------------------------* > > > > >>-----Original Message----- >>I'm trying to create a more efficient way of changing >>the date range criterion I use in database results >>criterion. I frequently have to change the database >>results properties across a series of pages to alter the >>dates of articles displayed on those pages. The custom >>query I currently use is: >> >>SELECT * FROM Articles WHERE ("Article Date" Between >>#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC >> >>I want to replace the hard coded date #7/1/2003# with a >>variable that I can set in the source of the page, or >>better yet, on a single include page that would be >>pulled into all the pages using this same database >>query. I have done this successfully with other types >>of queries, but I can't figure out the right syntax when >>it comes to the date property. >> >>Here is how I am defining the variable in HTML view: >> >><% >>Dim MyDate >>Date = "9/1/2003" >>%> >> >>And here are several variations of the syntax I tried to >>use for my defined variable. I placed and tested each >of >>these within the SQL query in the HTML view. All result >>in errors when I try to view my page in a browser. >> >>"::MyDate::" >>""::MyDate::"" >>'%::MyDate::%' >>#::MyDate::# >> >>I believe that I have the syntax wrong where I am trying >>to insert my defined variable into the SQL query in HTML >>view. Can anyone provide help? Thanks. >> >>. >> >. > |
|
|
|
25-09-2003, 04:58 AM
|
#4 |
|
Guest
Posts: n/a
|
Re: Date ranges in database results with a defined variable
Try
<% begdate = "7/1/2003" %> Then in your query use "SELECT * FROM Articles WHERE Article_Date >= #" & begdate & "# " Avoid using spaces, etc. in field names. -- ============================================== Thomas A. Rowe (Microsoft MVP - FrontPage) WEBMASTER Resources(tm) http://www.ycoln-resources.com FrontPage Resources, Forums, WebCircle, MS KB Quick Links, etc. ============================================== To assist you in getting the best answers for FrontPage support see: http://www.net-sites.com/sitebuilder/newsgroups.asp "paul" <paul@convergedigest.com> wrote in message news:025b01c38318$ae0fa320$a001280a@phx.gbl... > Thank you for all of the detailed suggestions. > Unfortunately, due the large number of pages I have that > use slightly different queries, it would be cumbersome to > create and modify a large number of queries in Access as > you outlined in your first suggestion. Also, I can't use > the second two suggestions exactly as written because I > get a lot of traffic to the pages in question from search > engines and places where links to the pages are already > on other people's sites, so I can't limit access to my > pages from a form or other links that I can fully > control. > > I tried to modify the approach in your third option so > that I wouldn't have to use a different URL for the > page. I defined the variable with a <script>begdate > = "7/1/2003"</script> tag in a page that is then included > in the DRW page, and I used begdate as a query field in > the DRW itself. Unfortunately, I get errors in the DRW > region when I view the page. > > Placing begdate in the DRW query results in the > message "Too few parameters. Expected 1." Using :: > begdate:: results in the message "One or more form fields > were empty." > > I think I am still missing something some key characters > either where I define the variable in the <script> tag, > or when I place begdate in the DRW. It's probably some > basic syntax issue that I just don't understand. Any > suggestions? > > >-----Original Message----- > >One possibility, certainly, is to set up an Access query > >named CurArticles, ala: > > > >SELECT * FROM Articles WHERE "Article Date" >= > >#7/1/2003# > > > >Then, use the CurArticles query rather than the Articles > >table as the DRW's datasource. > > > > > >Otherwise, if you're running the DRW page from a form, > >create a file named begdate.inc that contains this code: > > > ><input name="begdate" type="hidden" value="7/1/2003"> > > > >Then, ssi-include this page inside the form, and > >configure the DRW to use begdate as a query field. > > > > > >Otherwise, create a page named begdate.htm that contains > >this code: > > > ><html> > ><body> > ><script>begdate = "7/1/2003"</script> > ></body> > ></html> > > > >Use an Include Page component to include this <body> > >section in the page that links to your DRW page, and > then > >change the hyperlink to: > > > ><a href="drwpage.asp?begdate=<script>document.write > >(begdate);</script>"> > > > >and again use begdate as a query field in the DRW itself. > > > >Jim Buyens > >Microsoft FrontPage MVP > >buyensj@interlacken.com > >http://www.interlacken.com > >Author of: > >*------------------------------------------------------* > >|\----------------------------------------------------/| > >|| Microsoft Office FrontPage 2003 Inside Out || > >|| Microsoft FrontPage Version 2002 Inside Out || > >|| Web Database Development Step by Step .NET Edition || > >|| Troubleshooting Microsoft FrontPage 2002 || > >|| Faster Smarter Beginning Programming || > >|| (All from Microsoft Press) || > >|/----------------------------------------------------\| > >*------------------------------------------------------* > > > > > > > > > >>-----Original Message----- > >>I'm trying to create a more efficient way of changing > >>the date range criterion I use in database results > >>criterion. I frequently have to change the database > >>results properties across a series of pages to alter > the > >>dates of articles displayed on those pages. The custom > >>query I currently use is: > >> > >>SELECT * FROM Articles WHERE ("Article Date" Between > >>#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC > >> > >>I want to replace the hard coded date #7/1/2003# with a > >>variable that I can set in the source of the page, or > >>better yet, on a single include page that would be > >>pulled into all the pages using this same database > >>query. I have done this successfully with other types > >>of queries, but I can't figure out the right syntax when > >>it comes to the date property. > >> > >>Here is how I am defining the variable in HTML view: > >> > >><% > >>Dim MyDate > >>Date = "9/1/2003" > >>%> > >> > >>And here are several variations of the syntax I tried > to > >>use for my defined variable. I placed and tested each > >of > >>these within the SQL query in the HTML view. All > result > >>in errors when I try to view my page in a browser. > >> > >>"::MyDate::" > >>""::MyDate::"" > >>'%::MyDate::%' > >>#::MyDate::# > >> > >>I believe that I have the syntax wrong where I am > trying > >>to insert my defined variable into the SQL query in > HTML > >>view. Can anyone provide help? Thanks. > >> > >>. > >> > >. > > |
|
|
|
25-09-2003, 05:58 PM
|
#5 |
|
Guest
Posts: n/a
|
Re: Date ranges in database results with a defined variable
"paul" <paul@convergedigest.com> wrote in message news:<025b01c38318$ae0fa320$a001280a@phx.gbl>...
> Thank you for all of the detailed suggestions. > Unfortunately, due the large number of pages I have that > use slightly different queries, it would be cumbersome to > create and modify a large number of queries in Access as > you outlined in your first suggestion. All the pages that use the same Access table can use the same Access query. You would, however, need to modify each page to access the query instead of the table. The DRW can query an Access query, you know. Jim Buyens Microsoft FrontPage MVP buyensj@interlacken.com http://www.interlacken.com Author of: *------------------------------------------------------* |\----------------------------------------------------/| || Microsoft Office FrontPage 2003 Inside Out || || Microsoft FrontPage Version 2002 Inside Out || || Web Database Development Step by Step .NET Edition || || Troubleshooting Microsoft FrontPage 2002 || || Faster Smarter Beginning Programming || || (All from Microsoft Press) || |/----------------------------------------------------\| *------------------------------------------------------* |
|
|
|
25-09-2003, 09:22 PM
|
#6 |
|
Guest
Posts: n/a
|
Re: Date ranges in database results with a defined variable
Thomas, thank you for your reply. Unfortunately, I'm
still stuck. I can see that the variable setting format you suggested is working for me. I confirming it with a response.write command on the page. However, the query portion still causes errors. Due to a known and unfixed FrontPage DRW bug, I cannot place #" & begdate & "# " directly into the FP DRW field because it generates errors and will not allow me to save the change. So in HTML view, I have tried to paste this into the gray s-sql= section. Since this always needs variations on such characters as the quotation mark, I tried it the following ways: #"& begdate &"# #" & begdate & "# When I save and preview the page using either of the conditions above, I get a Database Results Error that says "Syntax error in date in query expression." and where the query is displayed in the error message it is cut off where the begdate condition is - it only reads #' and ends. Any further suggestions that I can try to make this query work? Also, does anyone know if FP2003 is more compatible with writing custom queries? >-----Original Message----- >Try > ><% >begdate = "7/1/2003" >%> > >Then in your query use > >"SELECT * FROM Articles WHERE Article_Date >= #" & begdate & "# " > >Avoid using spaces, etc. in field names. >-- > >============================================== >Thomas A. Rowe (Microsoft MVP - FrontPage) >WEBMASTER Resources(tm) >http://www.ycoln-resources.com >FrontPage Resources, Forums, WebCircle, >MS KB Quick Links, etc. >============================================== >To assist you in getting the best answers for FrontPage support see: >http://www.net-sites.com/sitebuilder/newsgroups.asp > >"paul" <paul@convergedigest.com> wrote in message >news:025b01c38318$ae0fa320$a001280a@phx.gbl... >> Thank you for all of the detailed suggestions. >> Unfortunately, due the large number of pages I have that >> use slightly different queries, it would be cumbersome to >> create and modify a large number of queries in Access as >> you outlined in your first suggestion. Also, I can't use >> the second two suggestions exactly as written because I >> get a lot of traffic to the pages in question from search >> engines and places where links to the pages are already >> on other people's sites, so I can't limit access to my >> pages from a form or other links that I can fully >> control. >> >> I tried to modify the approach in your third option so >> that I wouldn't have to use a different URL for the >> page. I defined the variable with a <script>begdate >> = "7/1/2003"</script> tag in a page that is then included >> in the DRW page, and I used begdate as a query field in >> the DRW itself. Unfortunately, I get errors in the DRW >> region when I view the page. >> >> Placing begdate in the DRW query results in the >> message "Too few parameters. Expected 1." Using :: >> begdate:: results in the message "One or more form fields >> were empty." >> >> I think I am still missing something some key characters >> either where I define the variable in the <script> tag, >> or when I place begdate in the DRW. It's probably some >> basic syntax issue that I just don't understand. Any >> suggestions? >> >> >-----Original Message----- >> >One possibility, certainly, is to set up an Access query >> >named CurArticles, ala: >> > >> >SELECT * FROM Articles WHERE "Article Date" >= >> >#7/1/2003# >> > >> >Then, use the CurArticles query rather than the Articles >> >table as the DRW's datasource. >> > >> > >> >Otherwise, if you're running the DRW page from a form, >> >create a file named begdate.inc that contains this code: >> > >> ><input name="begdate" type="hidden" value="7/1/2003"> >> > >> >Then, ssi-include this page inside the form, and >> >configure the DRW to use begdate as a query field. >> > >> > >> >Otherwise, create a page named begdate.htm that contains >> >this code: >> > >> ><html> >> ><body> >> ><script>begdate = "7/1/2003"</script> >> ></body> >> ></html> >> > >> >Use an Include Page component to include this <body> >> >section in the page that links to your DRW page, and >> then >> >change the hyperlink to: >> > >> ><a href="drwpage.asp?begdate=<script>document.write >> >(begdate);</script>"> >> > >> >and again use begdate as a query field in the DRW itself. >> > >> >Jim Buyens >> >Microsoft FrontPage MVP >> >buyensj@interlacken.com >> >http://www.interlacken.com >> >Author of: >> >*----------------------------------------------------- -* >> >|\---------------------------------------------------- /| >> >|| Microsoft Office FrontPage 2003 Inside Out || >> >|| Microsoft FrontPage Version 2002 Inside Out || >> >|| Web Database Development Step by Step .NET Edition || >> >|| Troubleshooting Microsoft FrontPage 2002 || >> >|| Faster Smarter Beginning Programming || >> >|| (All from Microsoft Press) || >> >|/---------------------------------------------------- \| >> >*----------------------------------------------------- -* >> > >> > >> > >> > >> >>-----Original Message----- >> >>I'm trying to create a more efficient way of changing >> >>the date range criterion I use in database results >> >>criterion. I frequently have to change the database >> >>results properties across a series of pages to alter >> the >> >>dates of articles displayed on those pages. The custom >> >>query I currently use is: >> >> >> >>SELECT * FROM Articles WHERE ("Article Date" Between >> >>#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC >> >> >> >>I want to replace the hard coded date #7/1/2003# with a >> >>variable that I can set in the source of the page, or >> >>better yet, on a single include page that would be >> >>pulled into all the pages using this same database >> >>query. I have done this successfully with other types >> >>of queries, but I can't figure out the right syntax when >> >>it comes to the date property. >> >> >> >>Here is how I am defining the variable in HTML view: >> >> >> >><% >> >>Dim MyDate >> >>Date = "9/1/2003" >> >>%> >> >> >> >>And here are several variations of the syntax I tried >> to >> >>use for my defined variable. I placed and tested each >> >of >> >>these within the SQL query in the HTML view. All >> result >> >>in errors when I try to view my page in a browser. >> >> >> >>"::MyDate::" >> >>""::MyDate::"" >> >>'%::MyDate::%' >> >>#::MyDate::# >> >> >> >>I believe that I have the syntax wrong where I am >> trying >> >>to insert my defined variable into the SQL query in >> HTML >> >>view. Can anyone provide help? Thanks. >> >> >> >>. >> >> >> >. >> > > > >. > |
|
|
|
25-09-2003, 09:42 PM
|
#7 |
|
Guest
Posts: n/a
|
Re: Date ranges in database results with a defined variable
Paul,
Have you consider learning to code ASP manually, as what you are attempting do would work with the example code I provided? -- ============================================== Thomas A. Rowe (Microsoft MVP - FrontPage) WEBMASTER Resources(tm) http://www.ycoln-resources.com FrontPage Resources, Forums, WebCircle, MS KB Quick Links, etc. ============================================== To assist you in getting the best answers for FrontPage support see: http://www.net-sites.com/sitebuilder/newsgroups.asp "Paul" <paul@convergedigest.com> wrote in message news:0ad701c383ab$19c6a180$a301280a@phx.gbl... > Thomas, thank you for your reply. Unfortunately, I'm > still stuck. I can see that the variable setting format > you suggested is working for me. I confirming it with a > response.write command on the page. However, the query > portion still causes errors. > > Due to a known and unfixed FrontPage DRW bug, I cannot > place #" & begdate & "# " directly into the FP DRW field > because it generates errors and will not allow me to save > the change. > > So in HTML view, I have tried to paste this into the gray > s-sql= section. Since this always needs variations on > such characters as the quotation mark, I tried it the > following ways: > > #"& begdate &"# > #" & begdate & "# > > When I save and preview the page using either of the > conditions above, I get a Database Results Error that > says "Syntax error in date in query expression." and > where the query is displayed in the error message it is > cut off where the begdate condition is - it only reads #' > and ends. > > Any further suggestions that I can try to make this query > work? > > Also, does anyone know if FP2003 is more compatible with > writing custom queries? > > >-----Original Message----- > >Try > > > ><% > >begdate = "7/1/2003" > >%> > > > >Then in your query use > > > >"SELECT * FROM Articles WHERE Article_Date >= #" & > begdate & "# " > > > >Avoid using spaces, etc. in field names. > >-- > > > >============================================== > >Thomas A. Rowe (Microsoft MVP - FrontPage) > >WEBMASTER Resources(tm) > >http://www.ycoln-resources.com > >FrontPage Resources, Forums, WebCircle, > >MS KB Quick Links, etc. > >============================================== > >To assist you in getting the best answers for FrontPage > support see: > >http://www.net-sites.com/sitebuilder/newsgroups.asp > > > >"paul" <paul@convergedigest.com> wrote in message > >news:025b01c38318$ae0fa320$a001280a@phx.gbl... > >> Thank you for all of the detailed suggestions. > >> Unfortunately, due the large number of pages I have > that > >> use slightly different queries, it would be cumbersome > to > >> create and modify a large number of queries in Access > as > >> you outlined in your first suggestion. Also, I can't > use > >> the second two suggestions exactly as written because I > >> get a lot of traffic to the pages in question from > search > >> engines and places where links to the pages are already > >> on other people's sites, so I can't limit access to my > >> pages from a form or other links that I can fully > >> control. > >> > >> I tried to modify the approach in your third option so > >> that I wouldn't have to use a different URL for the > >> page. I defined the variable with a <script>begdate > >> = "7/1/2003"</script> tag in a page that is then > included > >> in the DRW page, and I used begdate as a query field in > >> the DRW itself. Unfortunately, I get errors in the DRW > >> region when I view the page. > >> > >> Placing begdate in the DRW query results in the > >> message "Too few parameters. Expected 1." Using :: > >> begdate:: results in the message "One or more form > fields > >> were empty." > >> > >> I think I am still missing something some key > characters > >> either where I define the variable in the <script> tag, > >> or when I place begdate in the DRW. It's probably some > >> basic syntax issue that I just don't understand. Any > >> suggestions? > >> > >> >-----Original Message----- > >> >One possibility, certainly, is to set up an Access > query > >> >named CurArticles, ala: > >> > > >> >SELECT * FROM Articles WHERE "Article Date" >= > >> >#7/1/2003# > >> > > >> >Then, use the CurArticles query rather than the > Articles > >> >table as the DRW's datasource. > >> > > >> > > >> >Otherwise, if you're running the DRW page from a form, > >> >create a file named begdate.inc that contains this > code: > >> > > >> ><input name="begdate" type="hidden" value="7/1/2003"> > >> > > >> >Then, ssi-include this page inside the form, and > >> >configure the DRW to use begdate as a query field. > >> > > >> > > >> >Otherwise, create a page named begdate.htm that > contains > >> >this code: > >> > > >> ><html> > >> ><body> > >> ><script>begdate = "7/1/2003"</script> > >> ></body> > >> ></html> > >> > > >> >Use an Include Page component to include this <body> > >> >section in the page that links to your DRW page, and > >> then > >> >change the hyperlink to: > >> > > >> ><a href="drwpage.asp?begdate=<script>document.write > >> >(begdate);</script>"> > >> > > >> >and again use begdate as a query field in the DRW > itself. > >> > > >> >Jim Buyens > >> >Microsoft FrontPage MVP > >> >buyensj@interlacken.com > >> >http://www.interlacken.com > >> >Author of: > >> >*----------------------------------------------------- > -* > >> >|\---------------------------------------------------- > /| > >> >|| Microsoft Office FrontPage 2003 Inside Out > || > >> >|| Microsoft FrontPage Version 2002 Inside Out > || > >> >|| Web Database Development Step by Step .NET Edition > || > >> >|| Troubleshooting Microsoft FrontPage 2002 > || > >> >|| Faster Smarter Beginning Programming > || > >> >|| (All from Microsoft Press) > || > >> >|/---------------------------------------------------- > \| > >> >*----------------------------------------------------- > -* > >> > > >> > > >> > > >> > > >> >>-----Original Message----- > >> >>I'm trying to create a more efficient way of changing > >> >>the date range criterion I use in database results > >> >>criterion. I frequently have to change the database > >> >>results properties across a series of pages to alter > >> the > >> >>dates of articles displayed on those pages. The > custom > >> >>query I currently use is: > >> >> > >> >>SELECT * FROM Articles WHERE ("Article Date" Between > >> >>#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC > >> >> > >> >>I want to replace the hard coded date #7/1/2003# > with a > >> >>variable that I can set in the source of the page, or > >> >>better yet, on a single include page that would be > >> >>pulled into all the pages using this same database > >> >>query. I have done this successfully with other > types > >> >>of queries, but I can't figure out the right syntax > when > >> >>it comes to the date property. > >> >> > >> >>Here is how I am defining the variable in HTML view: > >> >> > >> >><% > >> >>Dim MyDate > >> >>Date = "9/1/2003" > >> >>%> > >> >> > >> >>And here are several variations of the syntax I tried > >> to > >> >>use for my defined variable. I placed and tested > each > >> >of > >> >>these within the SQL query in the HTML view. All > >> result > >> >>in errors when I try to view my page in a browser. > >> >> > >> >>"::MyDate::" > >> >>""::MyDate::"" > >> >>'%::MyDate::%' > >> >>#::MyDate::# > >> >> > >> >>I believe that I have the syntax wrong where I am > >> trying > >> >>to insert my defined variable into the SQL query in > >> HTML > >> >>view. Can anyone provide help? Thanks. > >> >> > >> >>. > >> >> > >> >. > >> > > > > > > >. > > |
|
|
|
25-09-2003, 10:53 PM
|
#8 |
|
Guest
Posts: n/a
|
Re: Date ranges in database results with a defined variable
Thomas:
Thank you for your prompt reply. I hadn't considered trying to code ASP manually, but considering the limitations I'm bumping into it sounds like a good idea. It may be that I'm trying to use FrontPage to do everything when I should just use it for certain things while directly writing ASP code where that is more efficient. Thanks for your feedback. Paul >-----Original Message----- >Paul, > >Have you consider learning to code ASP manually, as what you are attempting >do would work with the example code I provided? > >-- > >============================================== >Thomas A. Rowe (Microsoft MVP - FrontPage) >WEBMASTER Resources(tm) >http://www.ycoln-resources.com >FrontPage Resources, Forums, WebCircle, >MS KB Quick Links, etc. >============================================== >To assist you in getting the best answers for FrontPage support see: >http://www.net-sites.com/sitebuilder/newsgroups.asp > >"Paul" <paul@convergedigest.com> wrote in message >news:0ad701c383ab$19c6a180$a301280a@phx.gbl... >> Thomas, thank you for your reply. Unfortunately, I'm >> still stuck. I can see that the variable setting format >> you suggested is working for me. I confirming it with a >> response.write command on the page. However, the query >> portion still causes errors. >> >> Due to a known and unfixed FrontPage DRW bug, I cannot >> place #" & begdate & "# " directly into the FP DRW field >> because it generates errors and will not allow me to save >> the change. >> >> So in HTML view, I have tried to paste this into the gray >> s-sql= section. Since this always needs variations on >> such characters as the quotation mark, I tried it the >> following ways: >> >> #"& begdate &"# >> #" & begdate & "# >> >> When I save and preview the page using either of the >> conditions above, I get a Database Results Error that >> says "Syntax error in date in query expression." and >> where the query is displayed in the error message it is >> cut off where the begdate condition is - it only reads #' >> and ends. >> >> Any further suggestions that I can try to make this query >> work? >> >> Also, does anyone know if FP2003 is more compatible with >> writing custom queries? >> >> >-----Original Message----- >> >Try >> > >> ><% >> >begdate = "7/1/2003" >> >%> >> > >> >Then in your query use >> > >> >"SELECT * FROM Articles WHERE Article_Date >= #" & >> begdate & "# " >> > >> >Avoid using spaces, etc. in field names. >> >-- >> > >> >============================================== >> >Thomas A. Rowe (Microsoft MVP - FrontPage) >> >WEBMASTER Resources(tm) >> >http://www.ycoln-resources.com >> >FrontPage Resources, Forums, WebCircle, >> >MS KB Quick Links, etc. >> >============================================== >> >To assist you in getting the best answers for FrontPage >> support see: >> >http://www.net-sites.com/sitebuilder/newsgroups.asp >> > >> >"paul" <paul@convergedigest.com> wrote in message >> >news:025b01c38318$ae0fa320$a001280a@phx.gbl... >> >> Thank you for all of the detailed suggestions. >> >> Unfortunately, due the large number of pages I have >> that >> >> use slightly different queries, it would be cumbersome >> to >> >> create and modify a large number of queries in Access >> as >> >> you outlined in your first suggestion. Also, I can't >> use >> >> the second two suggestions exactly as written because I >> >> get a lot of traffic to the pages in question from >> search >> >> engines and places where links to the pages are already >> >> on other people's sites, so I can't limit access to my >> >> pages from a form or other links that I can fully >> >> control. >> >> >> >> I tried to modify the approach in your third option so >> >> that I wouldn't have to use a different URL for the >> >> page. I defined the variable with a <script>begdate >> >> = "7/1/2003"</script> tag in a page that is then >> included >> >> in the DRW page, and I used begdate as a query field in >> >> the DRW itself. Unfortunately, I get errors in the DRW >> >> region when I view the page. >> >> >> >> Placing begdate in the DRW query results in the >> >> message "Too few parameters. Expected 1." Using :: >> >> begdate:: results in the message "One or more form >> fields >> >> were empty." >> >> >> >> I think I am still missing something some key >> characters >> >> either where I define the variable in the <script> tag, >> >> or when I place begdate in the DRW. It's probably some >> >> basic syntax issue that I just don't understand. Any >> >> suggestions? >> >> >> >> >-----Original Message----- >> >> >One possibility, certainly, is to set up an Access >> query >> >> >named CurArticles, ala: >> >> > >> >> >SELECT * FROM Articles WHERE "Article Date" >= >> >> >#7/1/2003# >> >> > >> >> >Then, use the CurArticles query rather than the >> Articles >> >> >table as the DRW's datasource. >> >> > >> >> > >> >> >Otherwise, if you're running the DRW page from a form, >> >> >create a file named begdate.inc that contains this >> code: >> >> > >> >> ><input name="begdate" type="hidden" value="7/1/2003"> >> >> > >> >> >Then, ssi-include this page inside the form, and >> >> >configure the DRW to use begdate as a query field. >> >> > >> >> > >> >> >Otherwise, create a page named begdate.htm that >> contains >> >> >this code: >> >> > >> >> ><html> >> >> ><body> >> >> ><script>begdate = "7/1/2003"</script> >> >> ></body> >> >> ></html> >> >> > >> >> >Use an Include Page component to include this <body> >> >> >section in the page that links to your DRW page, and >> >> then >> >> >change the hyperlink to: >> >> > >> >> ><a href="drwpage.asp?begdate=<script>document.write >> >> >(begdate);</script>"> >> >> > >> >> >and again use begdate as a query field in the DRW >> itself. >> >> > >> >> >Jim Buyens >> >> >Microsoft FrontPage MVP >> >> >buyensj@interlacken.com >> >> >http://www.interlacken.com >> >> >Author of: >> >> >*-------------------------------------------------- --- >> -* >> >> >|\------------------------------------------------- --- >> /| >> >> >|| Microsoft Office FrontPage 2003 Inside Out >> || >> >> >|| Microsoft FrontPage Version 2002 Inside Out >> || >> >> >|| Web Database Development Step by Step .NET Edition >> || >> >> >|| Troubleshooting Microsoft FrontPage 2002 >> || >> >> >|| Faster Smarter Beginning Programming >> || >> >> >|| (All from Microsoft Press) >> || >> >> >|/------------------------------------------------- --- >> \| >> >> >*-------------------------------------------------- --- >> -* >> >> > >> >> > >> >> > >> >> > >> >> >>-----Original Message----- >> >> >>I'm trying to create a more efficient way of changing >> >> >>the date range criterion I use in database results >> >> >>criterion. I frequently have to change the database >> >> >>results properties across a series of pages to alter >> >> the >> >> >>dates of articles displayed on those pages. The >> custom >> >> >>query I currently use is: >> >> >> >> >> >>SELECT * FROM Articles WHERE ("Article Date" Between >> >> >>#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC >> >> >> >> >> >>I want to replace the hard coded date #7/1/2003# >> with a >> >> >>variable that I can set in the source of the page, or >> >> >>better yet, on a single include page that would be >> >> >>pulled into all the pages using this same database >> >> >>query. I have done this successfully with other >> types >> >> >>of queries, but I can't figure out the right syntax >> when >> >> >>it comes to the date property. >> >> >> >> >> >>Here is how I am defining the variable in HTML view: >> >> >> >> >> >><% >> >> >>Dim MyDate >> >> >>Date = "9/1/2003" >> >> >>%> >> >> >> >> >> >>And here are several variations of the syntax I tried >> >> to >> >> >>use for my defined variable. I placed and tested >> each >> >> >of >> >> >>these within the SQL query in the HTML view. All >> >> result >> >> >>in errors when I try to view my page in a browser. >> >> >> >> >> >>"::MyDate::" >> >> >>""::MyDate::"" >> >> >>'%::MyDate::%' >> >> >>#::MyDate::# >> >> >> >> >> >>I believe that I have the syntax wrong where I am >> >> trying >> >> >>to insert my defined variable into the SQL query in >> >> HTML >> >> >>view. Can anyone provide help? Thanks. >> >> >> >> >> >>. >> >> >> >> >> >. >> >> > >> > >> > >> >. >> > > > >. > |
|
|
|
|
| Thread Tools | |
| Rate This Thread | |
|
|
