Need to select between dates

[JonR]

I am trying to write a query based on data tables including a project origination date and an update date. I want the query to return the most recent date of the two. Some projects will not have two dates; only the origination date.

I get error messages when trying to use MAX functions, and I don't speak SQL. Any ideas?

Thanks in advance

 

[hcj]

assuming [update], if it exists, is always more recent,
try this calculated field in your query:
LatestDate: iif(isnull([update]),[orig],[update])

if, for some reason, [update] might be older (can't
imagine, but possible), try:

LatestDate: iif(isnull([update]),[orig],iif([orig]<
[update],[update],[orig]))

hope this helps.

-----Original Message-----
I am trying to write a query based on data tables

including a project origination date and an update date.
I want the query to return the most recent date of the
two. Some projects will not have two dates; only the
origination date.

I get error messages when trying to use MAX functions,

and I don't speak SQL. Any ideas?

 

[hcj]

pleased to help. FAR from genius, but thanks for the
compliment.

-----Original Message-----
That worked! You're a genius!
Thanks

assuming [update], if it exists, is always more recent,
try this calculated field in your query:
LatestDate: iif(isnull([update]),[orig],[update])

if, for some reason, [update] might be older (can't
imagine, but possible), try:

LatestDate: iif(isnull([update]),[orig],iif([orig]<
[update],[update],[orig]))

hope this helps.

-----Original Message-----
I am trying to write a query based on data tables

including a project origination date and an update date.
I want the query to return the most recent date of the
two. Some projects will not have two dates; only the
origination date.

I get error messages when trying to use MAX functions,

and I don't speak SQL. Any ideas?

Thanks in advance
.

.