help writing an expression on a report

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have an Access app that I wrote and on one of the reports I need to use an
unbound control with an expression. The control should display the oldest
date in a field (recdate) where resoby (a text field for initials) is null
or ='XXX'. Can I write an expression in expression builder to do that? Can
someone help? I can do =MIN(recdate) but that doesn't look at the value of
resoby.

Thanks for your help.

Karen
 
I am very new to this but try using the following in your expression.

=DMin("[yourdatefield]")
 
I have an Access app that I wrote and on one of the reports I need to use an
unbound control with an expression. The control should display the oldest
date in a field (recdate) where resoby (a text field for initials) is null
or ='XXX'. Can I write an expression in expression builder to do that? Can
someone help? I can do =MIN(recdate) but that doesn't look at the value of
resoby.

Thanks for your help.

Karen
Click to expand...

If you are looking for the minimum RecDate of all the records in the
table that meet your criteria, you can use DMin().

=DMin("[RecDate]","TableName","[Resoby] = 'XXX' or [Resoby] Is Null")
 
I have an Access app that I wrote and on one of the reports I need to use an
unbound control with an expression. The control should display the oldest
date in a field (recdate) where resoby (a text field for initials) is null
or ='XXX'. Can I write an expression in expression builder to do that? Can
someone help? I can do =MIN(recdate) but that doesn't look at the value of
resoby.

Thanks for your help.

Karen
Click to expand...

The DMin() function will work; it takes three arguments - the field to
search for its minimum value, the table or query name containing the
field, and an optional criterion. In this case:

=DMin("[Recdate]", "[name of your table]", "[resoby] = 'XXX' or is
null")

John W. Vinson[MVP]
 
So it does. This has done the trick. Thanks for your assistance.


John Vinson said:
I have an Access app that I wrote and on one of the reports I need to use an
unbound control with an expression. The control should display the oldest
date in a field (recdate) where resoby (a text field for initials) is null
or ='XXX'. Can I write an expression in expression builder to do that? Can
someone help? I can do =MIN(recdate) but that doesn't look at the value of
resoby.

Thanks for your help.

Karen
Click to expand...

The DMin() function will work; it takes three arguments - the field to
search for its minimum value, the table or query name containing the
field, and an optional criterion. In this case:

=DMin("[Recdate]", "[name of your table]", "[resoby] = 'XXX' or is
null")

John W. Vinson[MVP]
Click to expand...
 
Back
Top