Report InputBox

[Marko via AccessMonster.com]

Hi all,

I created a report that linked to the data table as source and on my regular
report, I created textboxes and set it to:

=DCount("*","BOOK TABLE","[BOOK_ID]='00167' AND [B_CONDITION]='USED'")

This gave me the total count of the book ID base on its condition.

Each book title has an assigned ID number as they being entered to the table
and are either New or Used as B_Condition. I have other field on the table
such as B_Name, B_Year, and B_DateEntry. The 3 fields that I am focused on is
BOOK_ID, B_CONDITION, and B_DATEENTRY (set to automatically insert as Date()
function).

Now I want the report to display inputboxes so I can input the date range (I.
E from 1/12/2009 to 2/12/2009) when it is being open so that I can get a
county of how many Used and New copies of a certain book on the data table in
a given time frame so I can predict the sell trend base on the criteria given
in the DCount() statement above. I'be veen trying to carry out this idea but
failed miserably. Any suggestion will be greatly appreciated.

Marko

 

[Jim Burke in Novi]

You'd have to do that on the form where the report is run from. I just use
textboxes on the form for start and end date, then my data source for the
report uses a date range based on those dates. If the report is based on a
table and you have a date field in that table that you are using the date
range on, then when you open the report just do something like

DoCmd.OpenReport "reportName",,,"MyDate Between #" & txtStartDate & _
"# AND #" & txtEndDate & "#"

where 'reportName' is the name of your report and 'MyDAte' is the name of
the date field from the table. The #'s have to be used to delimit the date
fields in your Where condition (the word WHERE is not included when using
this form of OpenReport, only the WHERE condition). Just make sure you force
the user to enter the dates, i.e. before opening the report check that both
textboxes have valid date values and that the end date is >= the start date.
For the textboxes use a format of short date - that way they can't enter an
invalid date.

 

[Armen Stein]

Now I want the report to display inputboxes so I can input the date range (I.
E from 1/12/2009 to 2/12/2009) when it is being open so that I can get a
county of how many Used and New copies of a certain book on the data table in
a given time frame so I can predict the sell trend base on the criteria given
in the DCount() statement above. I'be veen trying to carry out this idea but
failed miserably. Any suggestion will be greatly appreciated.

One way to have a report prompt the user for criteria before it runs
is to open a form from the report's Open event. Open the form in
Dialog mode so that the report waits for the form to be closed or
hidden before it proceeds. That way you can collect criteria from the
user and build a Where clause for the report. It also means that you
can call the report directly - you don't need to call it from a form.
And the selection form is reusable - it can be called from multiple
reports if they need the same criteria.

Your DCount can also use the same Where clause if it needs to.

I've posted examples of this technique at
www.JStreetTech.com/downloads - see "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Marko via AccessMonster.com]

Hi Jim,

Sorry for the long replied, I was out sicked for almost the whole week.
Anyway, I took on your advise and created a form with 2 fields ( txtFromDate,
txtToDate) and format them as Date (for validation purposed), and an Command
button. I set the properties of this form as Dialog, which wuold run upon the
Report open and prompt user to specify the date range. I've also tried to
validate the From Date and the ToDate by an If loop but does not seem to work.
Under the command button, I've placed the code below:

Dim stDocName As String
If CDate(txtFromDate) >CDate(txtToDate) then
MsgBox .........
Else
stDocName = "TREND REPORT"
DoCmd.OpenReport stDocName,,, "SDATE Between #" &txtFromDate & "# AND #" &
txtToDate & "#"
Me.Visible = False
End If

**SDATE is the name of the date field on the table.
However, when the report is open, it disregarded the date range and shows all
record from the table.

Any Idea why?

Many thanks
Marko

You'd have to do that on the form where the report is run from. I just use
textboxes on the form for start and end date, then my data source for the
report uses a date range based on those dates. If the report is based on a
table and you have a date field in that table that you are using the date
range on, then when you open the report just do something like

DoCmd.OpenReport "reportName",,,"MyDate Between #" & txtStartDate & _
"# AND #" & txtEndDate & "#"

where 'reportName' is the name of your report and 'MyDAte' is the name of
the date field from the table. The #'s have to be used to delimit the date
fields in your Where condition (the word WHERE is not included when using
this form of OpenReport, only the WHERE condition). Just make sure you force
the user to enter the dates, i.e. before opening the report check that both
textboxes have valid date values and that the end date is >= the start date.
For the textboxes use a format of short date - that way they can't enter an
invalid date.

Hi all,

[quoted text clipped - 19 lines]

Marko

 

[Marko via AccessMonster.com]

Hi Armen,

Sorry for the late response, I was out sick. Anyway, I've look into your
recommendations and It was a little to complex for me. However, I was able to
utulized the NoData property to force the report to close when there is no
record present. As i said in my reply for Jim, I've created a form as your
advise and run it as dialog box as the report open but encountered some
problem. I've been looking into your sample file from "Report Selection
Techniques" with "WHERE" clause and sort of confused, Any suggestion base on
my reply for Jim wuold help.

Many Thanks

Marko

 

[Armen Stein]

Dim stDocName As String
If CDate(txtFromDate) >CDate(txtToDate) then
MsgBox .........
Else
stDocName = "TREND REPORT"
DoCmd.OpenReport stDocName,,, "SDATE Between #" &txtFromDate & "# AND #" &
txtToDate & "#"
Me.Visible = False
End If

**SDATE is the name of the date field on the table.
However, when the report is open, it disregarded the date range and shows all
record from the table.

Hi Marko,

If you're running this in the Open event of the Report, then you can't
call *another* OpenReport - you're already in one. Instead, you need
to concatenate that Where clause to the Report's Recordsource
property. To do that, the property needs to be a SQL statement
"Select * from MyTableName", not just the table name. Then you can
use:
Me.Recordsource = Me.Recordsource & " " & "SDATE Between #" &
txtFromDate & "# AND #" & txtToDate & "#"

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com