Problem with "To / From" Report

[Rich Hartnett]

I'm trying to print a report (pdf) that has three filters: Start Date, End Date and ID No of the person running the report. In my code, the Start Date is "vdate," the End Date is "vdate2," and the ID No is "vidno."

In the actual report, the Date field is called "date" and the ID No. field is called "Idno_Bhpd."

With all three filters in the code nothing happens upon clicking on the Submit Button, but if I remove one of the date filters, it works fine. (Of course it limits the report to just that one day.)

Here's the code that works with two filters:

dim cFilter as c
cFilter = "dtoc(date) = '" + vdate + "' .and. Idno_bhpd = '" + vidno + "'"
filename = session.session_folder + chr(92) + "foo.pdf"
report.saveas("timesheet@[PathAlias.ADB_Path]\admin_bhpd.dbf", "pdf", cfilter,"", filename, .f.)
if file.exists(filename)
    response.redirect(session.session_url + "foo.pdf?" + time("hms3"))
end if

And here's the code with all three filters that DOESN'T work:

dim cFilter as c
cFilter = "dtoc(date) = '" + vdate + "' .and. '" + vdate2 + "' .and. IDNo_bhpd = '" + vidno + "'"
filename = session.session_folder + chr(92) + "foo.pdf"
report.saveas("timesheet@[PathAlias.ADB_Path]\admin_bhpd.dbf", "pdf", cfilter,"", filename, .f.)
if file.exists(filename)
    response.redirect(session.session_url + "foo.pdf?" + time("hms3"))
end if

What am I doing wrong with the second string of code?

[Tom Cone Jr]

Don't you want to use the between() function to include records with dates between the start and end dates? -- tom

[Rich Hartnett]

I guess I could. The 3 filter code was one I had used on this particular form two weeks ago, and it worked fine, but then I had to retool it, and upon recreating the dialog component I can't get it to work again. I've obviously missed something in my code.

What would be the correct way to write the code using the between filter?

[Tom Cone Jr]

I guess I could.

Shouldn't have to guess. Do you want records with date values that fall in the range, or do you want something else? (i.e. records that have one date or another?)

-- tom

[Rich Hartnett]

Yep that's what I want. A report that covers a time period from "mm/dd/yyyy" to "mm/dd/yyyy."

And like I said, it was working previously with the .and. filtering, but if you think it's better to use the between() filter that's fine with me, I'm just not sure how to code it.

[Pat Bremkamp]

Rich,

Tom is right, you want the between function which you will see is very easy to use, but also, there is an error in your filter. You should use something like

cFilter = "dtoc(date) = '" + vdate + "' .and. '"dtoc(date)='" + vdate2 + "' .and. IDNo_bhpd ....

You missed the second dtoc(date)= statement.

Pat

[Rich Hartnett]

Thanks Pat. I tried plugging in the missing "dtoc(date)=" part of the code, and although it works, it doesn't return any records. If I put in the same date twice, I get the report for ONE day, but when I put in two different dates, it returns a messase saying "No Records Found."

How would I go about using the "between()" filter with my present code?

[Pat Bremkamp]

Rich,
Yes, it will only work if you put in the same date because you have used the .and. operator. i was only trying to point out the need for the extra statement.

using the between operator would be something like

cFilter = "between(dtoc(date)," + s_quote(startdate) + "," + s_quote(enddate) + ") .and. IDNo_bhpd = " + s_quote(vidno)

Pat

[Rich Hartnett]

Thanks Pat, that worked with one small exception. I had to hard code the year of 2006 into the actual report, in order for the .pdf report to return the correct period of records. If I left the "2006" out, I got records starting with 2002, which is how far back this particular database goes, even though my starting and ending dates are entered fully. (Ex. 02/22/2006)

Any idea why? (Other than that it works great!)

[Tom Cone Jr]

Rich, if you'll post a sample of the tables involved in your report, and tell us which fields in which tables you're using in the filter, I think we can get you going in short order.

You are talking about a report, right?

-- tom

[Pat Bremkamp]

Rich,

Sure. That is because you are compaing character values, not date values. So, you can either convert everything to dates and compare them, or make sure that the character expressions are yielding the same thing.

For example, dtoc() may yield 02/27/2006, where your dates yield 02/27/06.

To convert character values to dates, you can use
convert_type(date,"D")

Pat

[George Corder]

I am trying to achieve the same thing but am not getting anywhere with it. Could you have a look at my code below which I have tried to construct using the above advice. I am not getting a pdf built. If I set the filter to "" I do get the report to print.

IF eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
goto skipreport
end if 
dim filter as c 
dim order as c 
Filter = "between(dtoc(transdate)," + s_quote(convert_type(fromdate,"D")) + "," + s_quote(convert_type(todate,"D")) + ")"
Order = ""

Dim filename as c 
select
    case radiobutton3="1"
    	 filename = session.session_folder + chr(92) + "tempreport.pdf"
   		 filename = report.saveas("MonthlyTellerActivity@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
			currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if
	case radiobutton3="2"
		 filename = session.session_folder + chr(92) + "tempreport.pdf"
		 filename = report.saveas("MonthlyTellerReport@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
		 	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if

end select
skipreport:

[Rich Hartnett]

Rich, if you'll post a sample of the tables involved in your report, and tell us which fields in which tables you're using in the filter, I think we can get you going in short order.

You are talking about a report, right?

-- tom

Ok attached is the single table that is used for this report. My test time period is 02/12/06 to 02/25/06, and the ID Number for the test is 1426. That will return a correct report using the desktop report. It's the web based report that is giving me more records than it's supposed to.

The fields used in the search paramaters are DATE and IDNo_BHPD.

Tom like I said earlier, it's working fine now as long as I keep the year hard coded into the report, but I'd really like to learn how to do it the right way so I'm not confined to a single year when making report inquiries.

[Rich Hartnett]

I guess I should have also included the code that I'm using in the dialog component that is giving me the incorrect return:

im cFilter as c
cFilter = "between(dtoc(date)," + s_quote(FromDate) + "," + s_quote(ToDate) + ") .and. IDNo_bhpd = " + s_quote(vidno)
filename = session.session_folder + chr(92) + "foo.pdf"
report.saveas("timesheet@[PathAlias.ADB_Path]\admin_bhpd.dbf", "pdf", cfilter,"", filename, .f.)
if file.exists(filename)
    response.redirect(session.session_url + "foo.pdf?" + time("hms3"))
end if

[Tom Cone Jr]

Rich,

I modified your TimeSheet (Demo) report layout to remove the built in filter expression. The modified layout was saved as TimeSheet (Demo2). This script illustrates a correct filter expression:

dim vc_id as C; vc_id = "1426"
dim fromdate as d; fromdate = {02/12/2006}
dim todate as d; todate = {02/25/2006}
filter_expression = "between(date,"+s_quote(fromdate)+","+s_quote(todate)+") .and. idno_bhpd = "+s_quote(vc_id)
:report.saveas("TimeSheet (DEMO2)","PDF",filter_expression,"","Toms_PDF")

You will need to modify this for use in the Web App to get the destination correct, but I think it illustrates how to get the filter going.

-- tom