AspWebSolution Forums » Products » ASP Report Wizard v2

index | search | no replies | posting guidelines | help

» »

:: Parameter/Filter Date Format :: (2)

Post a New Message | Post a Reply

Oct 13 2013, 5:34 PM

Parameter/Filter Date Format

Post a Reply

markiovino

since: Feb 23, 2012
from: Adelaide, South Australia

I have a few reports set up in the Report Wizard that prompt users for a date filter. Most accept dates in Australian (d/m/y) format, which is what I'm after. However, one report only accepts dates in American (m/d/y) format. I need this report (and all future reports) to accept dates in Australian format.

Note, all dates DISPLAY in Australian format on-screen, which is fine - I just need filters/parameters to accept Australian format input.

Unfortunately, I didn't create these reports, so I'm not sure why one is behaving differently to the rest.

Background:

The Report Wizard is on a classic ASP page, connected to an MS Access database. The ASP code is:

Dim objWiz
  Set objWiz = Server.CreateObject("AspWebSolution.ReportWizard2")

objWiz.LicenseKey= "<valid licence key>"
objWiz.Init("<valid connection string>")

objWiz.HideTables =  True
objWiz.HideViews =  True
objWiz.HideProcedures =  True
objWiz.DisplayReports

Let's call the non-working report rptUS. It is based on the Access query qryUS, which has the following SQL:

SELECT
     *
  FROM
     Table1  AS t1
     INNER  JOIN Table2  AS t2 ON t1.ID = t2.ID

Compare this to a working report rptAU, which is based on the query qryAU:

SELECT
     *
  FROM
     Table3  AS t3
     INNER  JOIN Query1  AS q1 ON t3.ID = q1.ID

And Query1 has:

SELECT
     *
  FROM
     Table1  AS t1
     INNER  JOIN Table2  AS t2 ON t1.ID = t2.ID
  WHERE
     t1.WeekEnding = [WeekEndingParameter]

When I build rptAU in the Report Wizard, the first screen asks for parameter values.  The Parameter Name [WeekEndingParameter] has a Parameter Value [?Week_Ending].

When I build rptUS, the first screen asks for fields to display.  One of the next screens asks for Filters, which is set up as qryUS.WeekEnding is equal to ?Week_Ending.

In both cases, when I run the report through the Wizard, the ?Week_Ending prompt appears.  I enter "6/10/13" (Oct 6) for both.  rptAU works, but rptUS doesn't (it assumes Jun 10).

So, I removed the Filter from rptUS, and tried changing qryUS to have a similar setup to qryAU, by adding the bold text:

SELECT
     *
  FROM
     Table1  AS t1
     INNER  JOIN Table2  AS t2 ON t1.ID = t2.ID
WHERE
     t1.WeekEnding = [WeekEndingParameter]

Back to the website, after I type the input date for rptUS, the Wizard now displays "No value given for one or more required parameters".

When I try to build rptUS, the first screen still asks for fields to display, but is now completely blank - no fields listed, and no Next/Finish buttons.

I even changed qryUS to have exactly the same SQL as qryAU, but I get the same result ("No value given..." and blank screen when building in the Report Wizard).

How do I get the Wizard to accept all dates, in all reports, in Australian format? And/or, how do I link the Wizard to the Parameters already created in the Access query, rather than setting up separate Filters?

Note, I've tried adding the following to the ASP code, but they made no difference:

objWiz.DefaultDateFormat= "DD/MM/YY"
objWiz.AutoDetectLocale = True

Locale Files didn't seem to help in this instance, either...

Oct 15 2013, 7:30 AM

Re: Parameter/Filter Date Format

Post a Reply

ghost

from: Washington, DC Try entering the date as: "2013-10-06"

-------------------------
Ghost

Pages: (1) [1]