generating a report with a DATE RANGE

[Elsie Beall]

Hi there. I am a new user of A5, and I'm having some confusion with reports and queries (maybe someone can clear this whole mess out for me!! :-)

I am working on a database that is fairly simple in structure. It has only 12 fields on one table. It contains sales information, some items specifically, date of sale, customer name and address, and sales rep.

I would like to generate reports that would allow me to (using a user entered variable) enter a start date and end date of the report.

i.e. between the dates of 05/15/2001 and 07/15/2001, how many sales did sales rep #1 (or whichever rep I want) do?

Is there a way to do this? How can I, by using a menu/button system, generate reports that will first prompt the user to enter search criteria. How do I write the syntax for the searches?

I realize that there is the query by form etc., but I am having a really hard time applying it to my needs. You see, I am building a database for someone other than myself to use, so I would like to make it as simple for them as possible, and give them access to all their reports etc with the click of a button. Am I being unrealistic.

I am grateful for any help you can give me.

Thanks a heap!
Elsie

[BRochford]

RE: generating a report with a DATE RANGE

Elsie you can go to learn alpha.com, click on articles and go down to "Barry's Tools", there is an example you can download. This may be of help to you.

-Barry

[bhphoto47]

RE: generating a report with a DATE RANGE

Elsie, If you want the user to specify a start and end date each time the report is run, use the Label Properties to do it!
Define your label, then open Label, Properties, and click on the Records Tab.
Click the arrow beside the Filter Expression box to expand the box.
Double-click on under the Variables column.
At the prompt, enter the text such as Enter Start Date
Change the type to Date (assuming your field is a date field)
Click OK - you'll be back in the Filter Expression box with a variable filled in
Enter the condition

[bhphoto47]

RE: generating a report with a DATE RANGE

OOPS - forgot about the limits of this forum - on the line Enter the Condition, enter the condition as GT sign or GTEQUAL sign Date_Field_name. Or LT sign or LTEqual sign.
Hope that made sense!

Bob

[Pat Bremkamp]

RE: generating a report with a DATE RANGE

Elsie,
Here is a scriptyou can study that does a similar thing:
' All the ScrnHigh stuff just centers the dialog box
dim FormPntr as p
dim ScrnHigh as n
dim ScrnWide as n
dim FormHigh as n
dim FormWide as n
dim global StartDate as d
dim global EndDate as d
dim LastButton as c
dim global mn as c
'**** Use a dialog box to get the dates ****
ScrnHigh = ui_info(1)
ScrnWide = ui_info(0)
FormPntr=:form.load("DateRange","Dialog")
FormHigh = FormPntr.Height
FormWide = FormPntr.Width
FormPntr.top = (ScrnHigh - FormHigh)/2
FormPntr.left = (ScrnWide - FormWide)/2
FormPntr:Sdate.Activate()
FormPntr.show()
'**** Script pauses til user closes dialog box ****
StartDate=FormPntr:SDate.value
EndDate=FormPntr:EDate.value
LastButton=FormPntr.last_pressed()
FormPntr.close()
if LastButton="CANCELBUTTON"
goto Cancelled
end if
'**** Get the report type ****
dim ReportName as c
dim PrintType as c
dim qfilter as c
dim qorder as c
'**** List the types of reports available ****
ReportName=ui_get_list("REPORT NAME",1,"PERFORMANCE","SUMMARY","INCENTIVE","REFLOW DETAIL","REFLOW SUMMARY")
ReportName=alltrim(ReportName)
select
case ReportName=""
goto Cancelled
case ReportName="PERFORMANCE"
mn=ui_get_list("MACHINE NUMBER",1,"All","1","2","3","4","5","6") 'you could sub in rep number here
mn=alltrim(mn)
if mn="All".or.mn=""
qfilter="between(MeltDate,StartDate,EndDate)"
else
qfilter="(between(MeltDate,StartDate,EndDate)).and.(left(MachSide,1)=mn)"
end if
ReportName="Perf_Report"
qorder="ProdCode+SerialNo"
case ReportName="SUMMARY"
Reportname="PerfSummary"
qfilter="between(MeltDate,StartDate,EndDate)"
qorder="ProdCode+SerialNo"
case ReportName="INCENTIVE"
ReportName="Incentive"
qfilter="between(MeltDate,StartDate,EndDate)"
qorder="left(machside,1)+serialno"
case ReportName="REFLOW DETAIL"
ReportName="ReflowResults"
qfilter="between(DateEntry,StartDate,EndDate)"
qorder="cdate(DateEntry)+ProdCode+SerialNo"
case ReportName="REFLOW SUMMARY"
ReportName="ReflowSummary"
qfilter="between(DateEntry,StartDate,EndDate)"
qorder="cdate(DateEntry)+ProdCode+SerialNo"
end select
'**** Print or view the report ****
PrintType=ui_get_list("SEND TO",1,"PRINTER","SCREEN")
if PrintType=""
goto Cancelled
elseif PrintType="PRINTER" then
:report.print(ReportName,qfilter,qorder)
else
:report.preview(ReportName,qfilter,qorder)
end if
ui_beep(48)
ui_msg_box("PRINT COMPLETE","The report has finished printing")
END

cancelled:
ui_msg_box("CANCELLED","Report Printing was cancelled")
end

[Jean-P. B�dard]

RE: generating a report with a DATE RANGE

Elsie,

If you have the X Basic book from Dr.Peter Wayne, look for Date Dialog form.

Very simple to use and also can be used with any form in your application requireing date ranges.

If you do not have the book (there are two of them) you should buy, very good investment.

Regards

JP

[Terry Morgan]

RE: generating a report with a DATE RANGE

have a look for "SET DATES" in the code archive for the method I use for setting date variables for report/query/operation filters.
I hope its useful.
Terry

[Stan Mathews]

RE: generating a report with a DATE RANGE

What limits "of this forum"?

on the line Enter the Condition, enter the condition as > or > = Date_Field_name. Or " or " = .

[bhphoto47]

RE: generating a report with a DATE RANGE

Sorry, maybe not a "limit of the forum" but I typed the line exactly as you did and it dropped the