Using dates with params

I'm having trouble using params to set a date range in a query for a report. 
Here's what I have:

The sql form the query -

  sql = "SELECT r.RabiesTagNo, r.RabiesVaccDate, r.RabiesVaccBrandID, 
RabiesVaccSN, ;
      p.PetName, p.Sex, p.Neutered, p.SpeciesID, p.Color, p.BreedID, 
p.DateofBirth, p.TattooChipNo, ;
      c.LastName||', '||c.FirstName AS LastNameFirst, c.Street, c.StateID, 
c.City||', '||c.StateID||'  '||c.Zipcode AS CityStateZip, c.HomePhone ;
      FROM RabiesInfo r ;
      JOIN PetData p ON p.PetID = r.PetID ;
      JOIN Client c ON c.ClientID = p.ClientID ;
      WHERE r.RabiesVaccDate BETWEEN :pStartDate AND :pEndDate"
      params['pStartDate'] = {08/01/2007}
      params['pEndDate'] = {08/31/2007}
      requestLive = false
  active = true

... and then down in the print button function -

         set procedure to Monthly.rep additive
         r = new MonthlyReport()
         r.Combined1.params['pStartDate'] = {08/01/2007}  //startDate
         r.Combined1.params['pEndDate'] = {08/31/2007}   //endDate
         r.Combined1.requery()
         r.Combined1.rowset.first()
         r.render()

I have the variables commented out to make sure they are not a factor, but 
even with the dates hardcoded this only gives me one record. I can fill a 
grid with the same query. Maybe the problem is with the report? Any ideas?

Dain Lochridge 

In article <F#4zJ8IVIHA.928@news-server>, deloch@hotmail.com says...
> I'm having trouble using params to set a date range in a query for a
report. 
> Here's what I have:
> 
> The sql form the query -
> 
>   sql = "SELECT r.RabiesTagNo, r.RabiesVaccDate, r.RabiesVaccBrandID,

> RabiesVaccSN, ;
>       p.PetName, p.Sex, p.Neutered, p.SpeciesID, p.Color, p.BreedID, 
> p.DateofBirth, p.TattooChipNo, ;
>       c.LastName||', '||c.FirstName AS LastNameFirst, c.Street, c.StateID,

> c.City||', '||c.StateID||'  '||c.Zipcode AS CityStateZip, c.HomePhone ;
>       FROM RabiesInfo r ;
>       JOIN PetData p ON p.PetID = r.PetID ;
>       JOIN Client c ON c.ClientID = p.ClientID ;
>       WHERE r.RabiesVaccDate BETWEEN :pStartDate AND :pEndDate"
>       params['pStartDate'] = {08/01/2007}
>       params['pEndDate'] = {08/31/2007}
>       requestLive = false
>   active = true
> 
> ... and then down in the print button function -
> 
>          set procedure to Monthly.rep additive
>          r = new MonthlyReport()
>          r.Combined1.params['pStartDate'] = {08/01/2007}  //startDate
>          r.Combined1.params['pEndDate'] = {08/31/2007}   //endDate
>          r.Combined1.requery()
>          r.Combined1.rowset.first()
>          r.render()
> 
> I have the variables commented out to make sure they are not a factor, but

> even with the dates hardcoded this only gives me one record. I can fill a 
> grid with the same query. Maybe the problem is with the report? Any ideas?
> 
> Dain Lochridge 
> 

Dain,

Just a wild guess: could it be a date issue related to the order of the 
days, months and year? For instance, you are using MMDDYYYY but is dBASE 
setup for DDMMYYYY or some other format?

You could try using the date object: NEW DATE( "31 aug 2007" )

However, if the grid works and the report doesn't there has to be 
something in the report. If it's not the date format, perhaps the order 
of the data doesn't match what the report wants.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Thanks for the reply, Geoff.  I didn't get a chance to work on this over the 
weekend.  I'm thinking it has to be report related as well.

I was trying to count the rows returned by the query and q.rowCount() seems 
to give -1 for any of the Firebird rowsets.  Is there a more reliable way to 
count rows with these tables?

Dain

>
> Dain,
>
> Just a wild guess: could it be a date issue related to the order of the
> days, months and year? For instance, you are using MMDDYYYY but is dBASE
> setup for DDMMYYYY or some other format?
>
> You could try using the date object: NEW DATE( "31 aug 2007" )
>
> However, if the grid works and the report doesn't there has to be
> something in the report. If it's not the date format, perhaps the order
> of the data doesn't match what the report wants.
>
> -- 
> Geoff Wass [dBVIPS]
> Montréal, Québec, Canada
>
> .|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
> .|.|.| ---------------------------------------------------------- |.|.|.
> .|.|.|             IT Consultant http://Geoff_Wass.com            |.|.|. 

Dain Lochridge schrieb:

> I was trying to count the rows returned by the query and q.rowCount() seems

> to give -1 for any of the Firebird rowsets.  Is there a more reliable way
to 
> count rows with these tables?

You can use "Select count(field)" to do counting. But you would have
to 
introduce also a "group by" for each column you do not want to be 
counted but listed.

But I usually also count the rows locally - saves server time and is as 
fast.

It does not make sense that the rows are counted as "-1".
Something must be wrong with the statement. Then again, maybe 
"rowcount()" is too new a feature for me :)
I am using q.rowset.count().

When you are using Firebird, keep in mind that the date-format is always 
in US notation (MM/DD/YYYY).
Because (nearly) everybody outside the US is using (DD/MM/YYYY) there is 
an option in the BDE to turn this format - I think...... yes... 
Formats\Date\Mode.

If you happened to have set it to anything new, this might cause a 
different transmission of date-types to the server engine.

On Fri, 11 Jan 2008 12:58:41 -0700, in dbase.sql-servers, 
Subject: Using dates with params, 
Message-ID: <F#4zJ8IVIHA.928@news-server>,  
"Dain Lochridge" <deloch@hotmail.com> wrote:

>I'm having trouble using params to set a date range in a query for a report.


Try this:

>      WHERE r.RabiesVaccDate BETWEEN :pStartDate AND :pEndDate"
      params['pStartDate'] = "08/01/2007"	// {08/01/2007}
      params['pEndDate'] = "08/31/2007"		// {08/31/2007}
>      requestLive = false
>  active = true