Sort a report on Multiple dates

  • Thread starter Thread starter Jim Conrady
  • Start date Start date
J

Jim Conrady

Can someone help me out on this one? I am creating a
report that has 2 date fields. I would like to sort on
the latest of the 2 dates. I have attempted to code an
expression in the sorting and grouping box, but am able to
get it to work.

Does anyone have any suggestions?

Jim
 
Hi Jim,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

I think you would have to create a calculated field in a query to sort on.
You could use an IIF() statement to compare the two date values and place
the latest one in the column of the query. Then you could use that value
to sort on in your report.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support
 
Dennis,

Getting closer... I cannot get the syntax right in the IIF
command. Here is exactly what I have right now in the
Field window in the query:

Expr1: IIf([Revised Target]<>"",[Revised Target],[Target
Complete])

When I try to run it, I get an error message ("Syntax
Error (comma) in query expression ... and lists the
expression. I can capture the screen in an image if that
would help).

If you can help me solve that one, I am off to the races.

Jim
 
Jim,

A couple of ideas that may help...
First of all, where are you? I can see no problem as regards the
commas in the expression, except there are some international versions
of Access which should use a ; rather than a , for the argument
separator.

Secondly, if Revised Target is a date field, it is highly unlikely
that it will ever be a zero-length string. Try this...
Expr1: Nz([Revised Target],[Target Complete])

- Steve Schapel, Microsoft Access MVP
 
Steve, and Dennis, thanks for the help. I did get it to
work. I, too, thought the syntax was pretty tight. What
I eventually did was re-enter it doing a numeric compare:

=iif([Revised Target]>0,[Revised Target],[Target Complete]}

That worked out fine.

I also found that I could have done the same thing in the
Sorting and Grouping window in the report. I actually
coded it up both ways and it is actually easier to do it
in the Sorting and Grouping panel.

Steve, the tip on Nz is also a good one, and probably the
way to go. I will experiment with that when I get some
time.

Thanks again to both of you. Calculated Fields are a
powerful tool, and I would not have been able to figure
them out without your help.

Regards,

Jim
-----Original Message-----
Jim,

A couple of ideas that may help...
First of all, where are you? I can see no problem as regards the
commas in the expression, except there are some international versions
of Access which should use a ; rather than a , for the argument
separator.

Secondly, if Revised Target is a date field, it is highly unlikely
that it will ever be a zero-length string. Try this...
Expr1: Nz([Revised Target],[Target Complete])

- Steve Schapel, Microsoft Access MVP


Dennis,

Getting closer... I cannot get the syntax right in the IIF
command. Here is exactly what I have right now in the
Field window in the query:

Expr1: IIf([Revised Target]<>"",[Revised Target],[Target
Complete])

When I try to run it, I get an error message ("Syntax
Error (comma) in query expression ... and lists the
expression. I can capture the screen in an image if that
would help).

If you can help me solve that one, I am off to the races.

Jim
Click to expand...

.
Click to expand...
 
Back
Top