Formula in text box

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a report that is based on a parameter query. The
parameter is Between And in a date field. I wanted the
option of leaving [Start Date] and [End Date] null in
order to return all records, so I used Or IsNull
expressions in the query. I wanted the report to include
the date range in the header, or if no range was
specified, the range would be from the earliest date for
which there was a record to the current date. This is the
formula I used:
=Format(IIf(IsNull([Start Date]),Min([RejectDate]),[Start
Date]),"mm/dd/yy") & " to " & Format(IIf(IsNull([End
Date]),Date(),[End Date]),"mm/dd/yy").
The problem came when I put the unbound textbox containing
the formula into the page header. It worked fine when I
entered a start date and end date, but if I left [Start
Date] blank I received Error# in the text box. I finally
figured out that for some reason I could not reference
[RejectDate] in the page header, but it would work in a
group header. However, there was no field on which I
could group the records. The report is in effect a table,
with Date, Part Number, Reason for Rejection, Inspector
etc. for each record. I finally created an extra number
field in the table, set its default value to 1, and
grouped on that. I placed the unbound text box in the
group header, and it worked as intended. I am OK with it
this way, since it works, but it seems rather convoluted.
Was there a better way to solve this?
 
Try putting the textbox (say 'txtbox1') (visible = false) in the report header and then putting a new textbox where you want in the page header with control source =txtbox

If you have nothing in report header you can make the height of txtbox1 set to 0 and then the height of ReportHeader=

Ba

----- Bruce wrote: ----

I have a report that is based on a parameter query. The
parameter is Between And in a date field. I wanted the
option of leaving [Start Date] and [End Date] null in
order to return all records, so I used Or IsNull
expressions in the query. I wanted the report to include
the date range in the header, or if no range was
specified, the range would be from the earliest date for
which there was a record to the current date. This is the
formula I used
=Format(IIf(IsNull([Start Date]),Min([RejectDate]),[Start
Date]),"mm/dd/yy") & " to " & Format(IIf(IsNull([End
Date]),Date(),[End Date]),"mm/dd/yy")
The problem came when I put the unbound textbox containing
the formula into the page header. It worked fine when I
entered a start date and end date, but if I left [Start
Date] blank I received Error# in the text box. I finally
figured out that for some reason I could not reference
[RejectDate] in the page header, but it would work in a
group header. However, there was no field on which I
could group the records. The report is in effect a table,
with Date, Part Number, Reason for Rejection, Inspector
etc. for each record. I finally created an extra number
field in the table, set its default value to 1, and
grouped on that. I placed the unbound text box in the
group header, and it worked as intended. I am OK with it
this way, since it works, but it seems rather convoluted.
Was there a better way to solve this
 
I tried that. It showed the word "to" and the end date,
but still it could not find the Min([RejectDate]) field.
When I substituted Date() for Min([RejectDate]) in the
formula, it worked. That is to say, it returned today's
date, which is not what I wanted, but it confirmed that
the difficulty with the formula was with the Min
function. With the formula in the group header (or the
detail section) it worked as intended, except that I had
to add the dummy field for grouping.
-----Original Message-----
Try putting the textbox (say 'txtbox1') (visible = false)
Click to expand...
in the report header and then putting a new textbox where
you want in the page header with control source =txtbox1
If you have nothing in report header you can make the
Click to expand...
height of txtbox1 set to 0 and then the height of
ReportHeader=0
Baz

----- Bruce wrote: -----

I have a report that is based on a parameter query. The
parameter is Between And in a date field. I wanted the
option of leaving [Start Date] and [End Date] null in
order to return all records, so I used Or IsNull
expressions in the query. I wanted the report to include
the date range in the header, or if no range was
specified, the range would be from the earliest date for
which there was a record to the current date. This is the
formula I used:
=Format(IIf(IsNull([Start Date]),Min([RejectDate]), [Start
Date]),"mm/dd/yy") & " to " & Format(IIf(IsNull([End
Date]),Date(),[End Date]),"mm/dd/yy").
The problem came when I put the unbound textbox containing
the formula into the page header. It worked fine when I
entered a start date and end date, but if I left [Start
Date] blank I received Error# in the text box. I finally
figured out that for some reason I could not reference
[RejectDate] in the page header, but it would work in a
group header. However, there was no field on which I
could group the records. The report is in effect a table,
with Date, Part Number, Reason for Rejection, Inspector
etc. for each record. I finally created an extra number
field in the table, set its default value to 1, and
grouped on that. I placed the unbound text box in the
group header, and it worked as intended. I am OK with it
this way, since it works, but it seems rather convoluted.
Was there a better way to solve this?

.
Click to expand...
 
Back
Top