Link to home
Start Free TrialLog in
Avatar of dougsouders1
dougsouders1

asked on

FrontPage 2003 / Access '97 Query to show all records in a month from hyperlink

I have two pages: Page 1 shows the Year and Month with a count of sales. I have the Year and Month as a hyperlink to another page that should show the specific month and year selected from the prior page and therefore show all sales for that month.

For my life I can't get the page to display the month range selected. I can hardcode any year and month into the query and it works- so I know the problem is in passing or transferring the parameter from the prior page into the query.

Here is the query:

SELECT *
FROM orders
WHERE Year([OrderDate])=#::Year::# and  Month([OrderDate]) =#::Month::#

I have spent 15 - 20 hours on this and am at my wits end. Been doing FrontPage and Access for 10 years now but I am stumped.
Avatar of jkurzner
jkurzner

I would put the Select statement into a variable and write it to the screen to make sure your query works. Use the results of the query in the actual database to see if the results are what you want.  It's hard to tell if the query is the problem or the Select statement without a little more information about where you are getting your vaiables for poulating the select statement
Response.Write(SC)

SC = "SELECT * FROM orders WHERE Year([OrderDate])=#::Year::# and  Month([OrderDate]) =#::Month::#

Avatar of dougsouders1

ASKER

I'm not sure I understand whay you are saying. I already have the Select statement with the variables. The variables from the prior form are 'Year' and 'Month'. It may help you to know that when I am on the previous page and hold the mouse cursor over any hyperlink, at the bottom of Internet Explorer you can see the link to the next page with the variables there. I have attached a pic of what I am talking about...
hyperlink-page-with-parameters.gif
I turned up the error reporting in Server Extensions and here is the error I get:

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT * FROM orders WHERE ((Year([OrderDate])=#::Year::#) and (Month([OrderDate]) =#::Month::#))
One or more form fields were empty. You should provide default values for all form fields that are used in the query.

Somehow the parameters in the hyperlink just aren't making it to the query in the 2nd page. Remember, I said that if I hardcode a Year and Date the query works fine. An example of the working query with hardcoded Year and Date would be:

SELECT *
FROM orders
WHERE Year([OrderDate])=2009 and  Month([OrderDate]) =09


Sorry, I forgot you were using FrontPage.  I normally would handcode the select statement.

SELECT * FROM orders
WHERE Year([OrderDate])=<%=Request.QueryString("'Year'")%> and  Month([OrderDate]) =<%=Request.QueryString("'Month'")%>

When I use the code I get this error:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/payroll/sales_month.asp, line 95

fp_sQry="SELECT * FROM orders WHERE Year([OrderDate])=<%=Request.QueryString(""'Year'"")
----------------------------------------------------------------------------------------^
Here is the query as it show in the DBRW wizard after modifying the grey code in text mode:

SELECT * FROM orders
WHERE Year([OrderDate])=<%=Request.QueryString("'Year'")%> and  Month([OrderDate]) =<%=Request.QueryString("'Month'")%>
You need to tell the query wizrd that a parameter is being used for the month and year.  The parameter comes from query string variables 'Year' and 'Month'.  Since Month and Year are reserved words you might want to consider using something like yr and mth  as it is also putting the " ' " character into the quesry string. The query wizard creates the select statement and it appears that is hard to edit by hand.  
You might also try the following.  I tried to anticipate how the wizard might code it.  Unfortuantely I dont have Front page 2003 to guide you better.

SELECT * FROM orders
WHERE Year([OrderDate])="& Request.QueryString("'Year'") & "Month([OrderDate]) =" & Request.QueryString("'Month'")
I change the hyperlink names from 'Year' to 'Yr' and 'Month' to 'Mth', put in the modified code above and still get an error:

Database Results Wizard Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'WHERE Year([OrderDate])='.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Any chance at some sccreen prints from the query wizard?  Is there a place to add parameters or specify where the parameters come from?

Can you hand code the following right above the select statement and let me know what it shows on the screen when you run it?
Response.Write(fp_sQry)

I have attached a pic of the DBRW with the working query with hardcoded year and month. I don't know what other pic you want.

I tried putting the Response.Write(fp_sQry) above the SELECT statement in the DBRW but it errors out when I click on "Next"...
hardcoded-year-and-date.gif
Replace 2009 with a parameter called vYear and replace 09 with a parameter called vMonth.  Then for the default value use 2009 and 09 respectively.  Then shoot me a section of the code where the parameters are defined and we can get what you want.
Replace the 2009 in the QUERY, or in the page sending the query? Use the default value where? Are you allowed to use remote control? It sure would seem a lot faster if we just did that and talked on the phone.
You can give me a call at seven 8 seeks, too tree ate, seven oh seven seven.  Jeff
ASKER CERTIFIED SOLUTION
Avatar of jkurzner
jkurzner

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fantastic and dedicated support professional