compare fields

[Guest]

I need help!!
I have a query to create lawsuits and need to choose between two dates for
my claim date. Either [date opened] or [last pay date]. I need the later of
the two dates to put on the lawsuit. Both are text fields, and I am using
Access 2002. I tried an IIf statement:
IIf([date opened]>Nz([last pay date],0),[date opened],[last pay date])

The query won't run, it says there is a data mismatch. Is it how I am
making the [last pay date] not a null value? Please help. Thanks in advance!
Vicki ([email protected])

 

[Guest]

Try this

IIf(format([date opened],"dd/mm/yyyy") >format(Nz([last pay
date],"1/1/1900"),"dd/mm/yyyy"),[date opened],[last pay date])

Make the two fields dateformat so you can compare between them.

 

[Guest]

That isn't working, it just repeats the [date opened] field. Thanks for
trying, though. Any other suggestions?

Try this

IIf(format([date opened],"dd/mm/yyyy") >format(Nz([last pay
date],"1/1/1900"),"dd/mm/yyyy"),[date opened],[last pay date])

Make the two fields dateformat so you can compare between them.

I need help!!
I have a query to create lawsuits and need to choose between two dates for
my claim date. Either [date opened] or [last pay date]. I need the later of
the two dates to put on the lawsuit. Both are text fields, and I am using
Access 2002. I tried an IIf statement:
IIf([date opened]>Nz([last pay date],0),[date opened],[last pay date])

The query won't run, it says there is a data mismatch. Is it how I am
making the [last pay date] not a null value? Please help. Thanks in advance!
Vicki ([email protected])

 

[Guest]

Another thought. I am doing this in the [date opened] field. should this be
in a new blank field? thanks for the help.

That isn't working, it just repeats the [date opened] field. Thanks for
trying, though. Any other suggestions?

Try this

IIf(format([date opened],"dd/mm/yyyy") >format(Nz([last pay
date],"1/1/1900"),"dd/mm/yyyy"),[date opened],[last pay date])

Make the two fields dateformat so you can compare between them.

I need help!!
I have a query to create lawsuits and need to choose between two dates for
my claim date. Either [date opened] or [last pay date]. I need the later of
the two dates to put on the lawsuit. Both are text fields, and I am using
Access 2002. I tried an IIf statement:
IIf([date opened]>Nz([last pay date],0),[date opened],[last pay date])

The query won't run, it says there is a data mismatch. Is it how I am
making the [last pay date] not a null value? Please help. Thanks in advance!
Vicki ([email protected])

 

[Guest]

Instead of the format try and use the cvdate function to change the format of
the text field to date field
IIf(cvdate([date opened]) >cvdate(Nz([last pay date],"1/1/1900")),[date
opened],[last pay date])

That isn't working, it just repeats the [date opened] field. Thanks for
trying, though. Any other suggestions?

Try this

IIf(format([date opened],"dd/mm/yyyy") >format(Nz([last pay
date],"1/1/1900"),"dd/mm/yyyy"),[date opened],[last pay date])

Make the two fields dateformat so you can compare between them.

I need help!!
I have a query to create lawsuits and need to choose between two dates for
my claim date. Either [date opened] or [last pay date]. I need the later of
the two dates to put on the lawsuit. Both are text fields, and I am using
Access 2002. I tried an IIf statement:
IIf([date opened]>Nz([last pay date],0),[date opened],[last pay date])

The query won't run, it says there is a data mismatch. Is it how I am
making the [last pay date] not a null value? Please help. Thanks in advance!
Vicki ([email protected])