Comparing Two Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to compare two fields. Both have dates in them. I want to
compare the second to the first and display the most recent between the two
AND if the second date is blank than it needs to return the data from the
first.
 
SELECT IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1]) AS
Expr1
FROM tblTest;
 
Assumption:
FieldA is never null
FieldB can be null

I would try:

IIF(FieldB<FieldA,FieldA,NZ(FieldB,FieldA))
 
Does "select" represent something? and i got a return error on "AS". Also
what about the rest after "AS" ..." expr1 from tbltest.

Brendan Reynolds said:
SELECT IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1]) AS
Expr1
FROM tblTest;

--
Brendan Reynolds
Access MVP


Patrick said:
I am trying to compare two fields. Both have dates in them. I want to
compare the second to the first and display the most recent between the
two
AND if the second date is blank than it needs to return the data from the
first.
Click to expand...
Click to expand...
 
the expression you have entered contains an invaild syntax.
you may have entered an operand without an operator.
 
Perhaps these will help to explain it ...

http://brenreyn.brinkster.net/QueryInSqlView.jpg

http://brenreyn.brinkster.net/QueryInDesignView.jpg

http://brenreyn.brinkster.net/QueryInDatasheetView.jpg

--
Brendan Reynolds
Access MVP


Patrick said:
Does "select" represent something? and i got a return error on "AS". Also
what about the rest after "AS" ..." expr1 from tbltest.

Brendan Reynolds said:
SELECT IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1]) AS
Expr1
FROM tblTest;

--
Brendan Reynolds
Access MVP


Patrick said:
I am trying to compare two fields. Both have dates in them. I want to
compare the second to the first and display the most recent between the
two
AND if the second date is blank than it needs to return the data from
the
first.
Click to expand...
Click to expand...
Click to expand...
 
BTW: In the design view screen shot, in the third column, the closing ")" is
cut off, so watch out for that. It looks like this in the screen shot ...

Expr1: IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1]

.... but it should look like this ...

Expr1: IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1])

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
Perhaps these will help to explain it ...

http://brenreyn.brinkster.net/QueryInSqlView.jpg

http://brenreyn.brinkster.net/QueryInDesignView.jpg

http://brenreyn.brinkster.net/QueryInDatasheetView.jpg

--
Brendan Reynolds
Access MVP


Patrick said:
Does "select" represent something? and i got a return error on "AS".
Also
what about the rest after "AS" ..." expr1 from tbltest.

Brendan Reynolds said:
SELECT IIf(NZ([Date2],[Date1])>=[Date1],NZ([Date2],[Date1]),[Date1]) AS
Expr1
FROM tblTest;

--
Brendan Reynolds
Access MVP


I am trying to compare two fields. Both have dates in them. I want to
compare the second to the first and display the most recent between
the
two
AND if the second date is blank than it needs to return the data from
the
first.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top