IIF date statement

[Guest]

Please help. I am trying to turn all dates equaling 1/1/2000 into N/A’s in
another column if the date is not 1/1/2000 I need to pull that date pulled
over. There can also be blanks in the date 1 column these can come back as
blanks.

Statement I'm using
Test2: IIf([master]![HOC]="1/1/2000","N/A",[master]![HOC])

This comes back with blanks where they should be blanks but errors in any
cell having any dates

Date 1 Test2 (I need)
12/1/2005 12/1/05
1/1/2000 N/A
1/5/2006 1/5/2006

Thank you in advance.

 

[Ken Snell \(MVP\)]

Delimite the date value with # character:

Test2: IIf([master]![HOC]=#1/1/2000#,"N/A",[master]![HOC])

 

[John Spencer]

Assuming that HOC is a date field, you need to delimit it with #
(oglethorpes), not with " (quote marks).

Test2: IIf([master]![HOC]=#1/1/2000#,"N/A",[master]![HOC])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you so much

Assuming that HOC is a date field, you need to delimit it with #
(oglethorpes), not with " (quote marks).

Test2: IIf([master]![HOC]=#1/1/2000#,"N/A",[master]![HOC])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please help. I am trying to turn all dates equaling 1/1/2000 into N/A's in
another column if the date is not 1/1/2000 I need to pull that date pulled
over. There can also be blanks in the date 1 column these can come back as
blanks.

Statement I'm using
Test2: IIf([master]![HOC]="1/1/2000","N/A",[master]![HOC])

This comes back with blanks where they should be blanks but errors in any
cell having any dates

Date 1 Test2 (I need)
12/1/2005 12/1/05
1/1/2000 N/A
1/5/2006 1/5/2006

Thank you in advance.

 

[Guest]

Thank you so much

Delimite the date value with # character:

Test2: IIf([master]![HOC]=#1/1/2000#,"N/A",[master]![HOC])

--

Ken Snell
<MS ACCESS MVP>

Please help. I am trying to turn all dates equaling 1/1/2000 into N/A's in
another column if the date is not 1/1/2000 I need to pull that date pulled
over. There can also be blanks in the date 1 column these can come back as
blanks.

Statement I'm using
Test2: IIf([master]![HOC]="1/1/2000","N/A",[master]![HOC])

This comes back with blanks where they should be blanks but errors in any
cell having any dates

Date 1 Test2 (I need)
12/1/2005 12/1/05
1/1/2000 N/A
1/5/2006 1/5/2006

Thank you in advance.