xBasic Filter from Form to Report

[spain246]

Ok,
I have a setup on my reports form that will allow you to select from 5 different options with radio buttons, and between certain dates. It keeps crashing on me, telling me that this line is not a valid filter:

filter1 = "between(date,[varD-"begindate],[varD-"enddate])"

Basically, you select a filter with the radio buttons, enter more search info from conditional buttons that appear with each different radio button, enter a start and end date (you do this everytime), and then you hit a button to run that particular report. Here is a copy of the code:

if option = 1 then 'print All ANIs
filter1 = "between(date,[varD-"begindate],[varD-"enddate])"

else if option = 2 then 'print Single ANI
filter1 = "between(date,[varD-"begindate],[varD-"enddate])"
list_invno = stritran(ani,",",crlf()) 'turn the list into a cr-lf delimited list
list_invo = alltrim(list_invno)
filter = *for_each(x,"ani = " + quote(x) ,list_invo)
filter = alltrim(filter)
filter = stritran(filter,crlf()," .or. ")

else if option = 3 then 'print Serial Number
filter1 = "between(date,[varD-"begindate],[varD-"enddate])"
list_invno = stritran(serial,",",crlf()) 'turn the list into a cr-lf delimited list
list_invo = alltrim(list_invno)
filter = *for_each(x,"serial = " + quote(x) ,list_invo)
filter = alltrim(filter)
filter = stritran(filter,crlf()," .or. ")

else if option = 4 then 'print Account List
filter1 = "between(date,[varD-"begindate],[varD-"enddate])"
filter = "between(invoice_number,[varC-"begin_inv],[varC-"end_inv])"

else if option = 5 then 'print Service Zone
filter1 = "between(date,[varD-"begindate],[varD-"enddate])"
filter = "between(invoice_number,[varC-"begin_inv],[varC-"end_inv])"

end if

'substitute the parameters in the filter expression
'filter2 = replace_parameters(filter,local_variables())
'filter3 = replace_parameters(filter1,local_variables())


filter = replace_parameters(filter,local_variables())
filter = replace_parameters(filter,local_variables())
report.preview("coin2",filter1 +filter)

Is there something i'm supposed to put on the report? The date field on the report is called "offhookdate". The date varialbe line is straight from Alpha Sports, so I shouldn't be getting this error. Any ideas on how I can fix my script?

Thanks!

Steve Hall

[Stan Mathews]

RE: xBasic Filter from Form to Report

Among other things the -" syntax indicates that what follows is part of a table named prior, as in tablename-"fieldname. I don't think that is what you meant.

Also try

filter1 = "between(date,["+dtoc(begindate)+"],["+dtoc(enddate)+"])"

[CALocklin]

RE: xBasic Filter from Form to Report

I think Stans, method should work. Here's a little more explanation about why.

First, I have to test this to see if the curly brackets work on this message board. If they do, the date should be enclosed in curly brackets, {}, not square brackets, [].

However, if the variable is already a date, the curly brackets shouldn't be used. The curly brackets are only used to tell A5 to convert the text inside them into a date value.

Second, I could be wrong on this one but I don't believe that varC->MyCharVariable or varD->MyDateVariable is a valid construction. I have never noticed anything in the Help files other than a simple var->MyWhateverVariable.

From the interactive editor:
qwer = "asdf"
?varc->qwer
ERROR: Field is not recognized
?var->qwer
= "asdf"


Cal Locklin
www.aimsdc.net

[Jerry Gray]

RE: xBasic Filter from Form to Report

Steve,

This is a button script I use for the same type of setup. The variables begindate and enddate are defined on the form as dates. The variable rpt_show_filt is passed to the report and becomes part of the report header to show what filter was used to produce the report.
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'Date Created: 18-Jul-2002 09:41:43 PM
'Last Updated: 29-Jun-2005 05:01:31 AM
'Created By :
'Updated By : Jerry

DIM global rpt_show_filt as c

if option = 1 then 'print today's callins
filter = "Attendance-"absent_date = now()"
'rpt_show_filt is passed to the report and becomes part of the header
rpt_show_filt = "Absent Date = " + today
'substitute the parameters in the filter expression
filter = replace_parameters(filter,local_variables())
else if option = 2 then 'range of dates
filter = between_date("attendance-"absent_date",begindate,enddate)
filter = replace_parameters(filter,local_variables())
'rpt_show_filt is passed to the report and becomes part of the header
rpt_show_filt = "Reporting Absent Dates " + begindate + " through " + enddate
else if option = 3 then 'list of employee numbers
list_empid = stritran(empl_no,",",crlf()) 'turn the list into a cr-lf delimited list
list_empd = alltrim(list_empid)
filter = *for_each(x,"employee_id = " + quote(x) ,list_empd)
filter = alltrim(filter)
'convert CRLF to .or. in filter
filter = stritran(filter,crlf()," .or. ")
'rpt_show_filt is passed to the report and becomes part of the header
rpt_show_filt = "Reporting Employe ID(s) = " + empl_no
'substitute the parameters in the filter expression
filter = replace_parameters(filter,local_variables())

end if

if print_option = "preview" then
report.preview("Attendance Report",filter)
else
report.print("Attendance Report",filter)
end if

Jerry Gray

[Jerry Gray]

RE: xBasic Filter from Form to Report

Steve,

A little research on the REPLACE_PARAMETERS() function says that if you define your variables as SHARED, then you can reference them directly, rather than using the REPLACE_PARAMETERS() function.

Goto: REPLACE_PARAMETERS()

I've also attached a rtf file of my code. It is easier to read than what posted in the thread.

Jerry Gray

[spain246]

RE: xBasic Filter from Form to Report

Actually, the way I have it set up is this script in on a blank table, but the report is based on a set. Am I supposed to have a pointer or something on the report? I'm thinking that it doesn't know where to stick the variable or check for dates.

Steve Hall

[spain246]

RE: xBasic Filter from Form to Report

Looking at your code, it looks like you apply the filter before you open the report. Is there something specific on the report that I need to correlate between the query code and the report?

Thanks

Steve Hall

[Jerry Gray]

RE: xBasic Filter from Form to Report

Steve,

My form is based on the same set as the report. I basically copied the form from AlphaSports and modified it to meet my needs.

You don't have to base the form on the set, you can set up shared or global variables and use them in the report to create the filter.

The following script is on a button to print all records in a file. You can build the query.filter and query.order as needed before the report is opened.

'Print Peak Period Report
query.filter = ""
query.order = ""

:Report.Print("Peak Period Report",query.filter,query.order)

I hope this gives you some ideas.

Jerry Gray