IIF acting funny

[Norm C via AccessMonster.com]

I have a field in an Access XP report that contains the following line as its
control source:

=IIf([Education_Comp_DATE]="1/1/1900" Or [Education_Comp_DATE]="12/31/2100"
Or CheckEmpty([Education_Comp_DATE]),"N/A",[Education_Comp_DATE])

Note:CheckEmpty is a function I created to check if a field is blank or null.
It returns a boolean.

On some PCs, it calculates correctly and on others, it doesn't and when I say
it doesn't calculate correctly, i mean that it shows 1/1/1900 when it should
be showing N/A. What would cause an IIF to calculate differently on
different PCs? The references on both PCs are the same. I am out of ideas.
Help!!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

[Bruce Rusk]

Could be a problem with date formatting -- different settings on different
machines.

See this page on formatting dates:

http://mvps.org/access/datetime/date0005.htm

"Norm C via AccessMonster.com" <u8613@uwe> wrote in message
news:552909eb30049@uwe...

I have a field in an Access XP report that contains the following line as
its
control source:

=IIf([Education_Comp_DATE]="1/1/1900" Or
[Education_Comp_DATE]="12/31/2100"
Or CheckEmpty([Education_Comp_DATE]),"N/A",[Education_Comp_DATE])

Note:CheckEmpty is a function I created to check if a field is blank or
null.
It returns a boolean.

On some PCs, it calculates correctly and on others, it doesn't and when I
say
it doesn't calculate correctly, i mean that it shows 1/1/1900 when it
should
be showing N/A. What would cause an IIF to calculate differently on
different PCs? The references on both PCs are the same. I am out of
ideas.
Help!!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

[lylefair]

Is the type of [Education_Comp_DATE ] Date?
If so, on some computers, "1/1/1900" might be coreced to a date, and
result in [Education_Comp_DATE]="1/1/1900" returning True while on
others, it might not be coerced, or coerced in some unforeseen way,
resulting in [Education_Comp_DATE]="1/1/1900" returning False.

This could depend on local settings.

If ths is so, you might cure the problem with
[Education_Comp_DATE]=DateSerial(1900,1,1)

[fredg]

On Fri, 30 Sep 2005 23:26:40 GMT, Norm C via AccessMonster.com wrote:

I have a field in an Access XP report that contains the following line as its
control source:

=IIf([Education_Comp_DATE]="1/1/1900" Or [Education_Comp_DATE]="12/31/2100"
Or CheckEmpty([Education_Comp_DATE]),"N/A",[Education_Comp_DATE])

Note:CheckEmpty is a function I created to check if a field is blank or null.
It returns a boolean.

On some PCs, it calculates correctly and on others, it doesn't and when I say
it doesn't calculate correctly, i mean that it shows 1/1/1900 when it should
be showing N/A. What would cause an IIF to calculate differently on
different PCs? The references on both PCs are the same. I am out of ideas.
Help!!

Several points to consider.
1) You have written your code as if the criteria "1/1/1900" is a
string value.
Isn't [Education_Comp_DATE] a Date Datatype field?
Then you need to surround your date criteria with the date delimiter
#.

=IIf([Education_Comp_DATE] = #1/1/1900# Or [Education_Comp_DATE] =
#12/31/2100# Or CheckEmpty([Education_Comp_DATE]), "N/A",
[Education_Comp_DATE])

You could make it a bit easier

2) =IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
CheckEmpty([Education_Comp_DATE]), "N/A", [Education_Comp_DATE])

The numbers are the actual stored values for the dates you chose.

3) Also what does CheckEmpty([Education_Comp_DATE]) return, as there
is no field here using it as criteria, so the actual expression will
read

=IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
True, "N/A", [Education_Comp_DATE])
which doesn't make sense.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Steve Jorgensen]

On 30 Sep 2005 17:07:03 -0700, "lylefair" <ly******@yahoo.ca> wrote:

Is the type of [Education_Comp_DATE ] Date?
If so, on some computers, "1/1/1900" might be coreced to a date, and
result in [Education_Comp_DATE]="1/1/1900" returning True while on
others, it might not be coerced, or coerced in some unforeseen way,
resulting in [Education_Comp_DATE]="1/1/1900" returning False.

This could depend on local settings.

If ths is so, you might cure the problem with
[Education_Comp_DATE]=DateSerial(1900,1,1)

Or [Education_Comp_DATE]=CVDate("Jan 1, 1900")

I've been leaning towards this style lately because it's both a deterministic
format, and one I don't have to slow down to read when I'm skimming through
the app.

[Phil Stanton]

I thought dates had to be enclosed in Hashes
#12/31/2100#, not inverted commas "12/31/2100"

Phil

"Norm C via AccessMonster.com" <u8613@uwe> wrote in message
news:552909eb30049@uwe...

I have a field in an Access XP report that contains the following line as
its
control source:

=IIf([Education_Comp_DATE]="1/1/1900" Or
[Education_Comp_DATE]="12/31/2100"
Or CheckEmpty([Education_Comp_DATE]),"N/A",[Education_Comp_DATE])

Note:CheckEmpty is a function I created to check if a field is blank or
null.
It returns a boolean.

On some PCs, it calculates correctly and on others, it doesn't and when I
say
it doesn't calculate correctly, i mean that it shows 1/1/1900 when it
should
be showing N/A. What would cause an IIF to calculate differently on
different PCs? The references on both PCs are the same. I am out of
ideas.
Help!!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

[Tom van Stiphout]

On 30 Sep 2005 17:07:03 -0700, "lylefair" <ly******@yahoo.ca> wrote:

Sounds a lot like some of the bugs that exist in oleauth.dll (which is
used for date math). MSFT has known about this for YEARS but refuses
to fix them.

-Tom.

Is the type of [Education_Comp_DATE ] Date?
If so, on some computers, "1/1/1900" might be coreced to a date, and
result in [Education_Comp_DATE]="1/1/1900" returning True while on
others, it might not be coerced, or coerced in some unforeseen way,
resulting in [Education_Comp_DATE]="1/1/1900" returning False.

This could depend on local settings.

If ths is so, you might cure the problem with
[Education_Comp_DATE]=DateSerial(1900,1,1)

[Norm C via AccessMonster.com]

You and Phil Stanton were correct in that my dates were suppose to be
enclosed in hashes. Also, regarding how my CheckEmpty function doesn't make
sense, since it returns a Boolean, you really don't need a variable using it.
Basically, the function CheckEmpty is actually its own variable that will
result in TRUE or FALSE.

fredg wrote:

I have a field in an Access XP report that contains the following line as its
control source:

[quoted text clipped - 10 lines]

different PCs? The references on both PCs are the same. I am out of ideas.
Help!!

Several points to consider.
1) You have written your code as if the criteria "1/1/1900" is a
string value.
Isn't [Education_Comp_DATE] a Date Datatype field?
Then you need to surround your date criteria with the date delimiter
#.

=IIf([Education_Comp_DATE] = #1/1/1900# Or [Education_Comp_DATE] =
#12/31/2100# Or CheckEmpty([Education_Comp_DATE]), "N/A",
[Education_Comp_DATE])

You could make it a bit easier

2) =IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
CheckEmpty([Education_Comp_DATE]), "N/A", [Education_Comp_DATE])

The numbers are the actual stored values for the dates you chose.

3) Also what does CheckEmpty([Education_Comp_DATE]) return, as there
is no field here using it as criteria, so the actual expression will
read

=IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
True, "N/A", [Education_Comp_DATE])
which doesn't make sense.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Steve Jorgensen]

Note that the reason the value needs to be un hashes is because you want to be
comparing dates, not converting the date to a string and comparing strings.
Using hashes is not the most reliable way to do that, though. I'd use
DateSerial or CVDate with a date string to make the date value.

On Wed, 05 Oct 2005 14:50:13 GMT, "Norm C via AccessMonster.com" <u8613@uwe>
wrote:

You and Phil Stanton were correct in that my dates were suppose to be
enclosed in hashes. Also, regarding how my CheckEmpty function doesn't make
sense, since it returns a Boolean, you really don't need a variable using it.
Basically, the function CheckEmpty is actually its own variable that will
result in TRUE or FALSE.

fredg wrote:

I have a field in an Access XP report that contains the following line as its
control source:

[quoted text clipped - 10 lines]

different PCs? The references on both PCs are the same. I am out of ideas.
Help!!

Several points to consider.
1) You have written your code as if the criteria "1/1/1900" is a
string value.
Isn't [Education_Comp_DATE] a Date Datatype field?
Then you need to surround your date criteria with the date delimiter
#.

=IIf([Education_Comp_DATE] = #1/1/1900# Or [Education_Comp_DATE] =
#12/31/2100# Or CheckEmpty([Education_Comp_DATE]), "N/A",
[Education_Comp_DATE])

You could make it a bit easier

2) =IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
CheckEmpty([Education_Comp_DATE]), "N/A", [Education_Comp_DATE])

The numbers are the actual stored values for the dates you chose.

3) Also what does CheckEmpty([Education_Comp_DATE]) return, as there
is no field here using it as criteria, so the actual expression will
read

=IIf([Education_Comp_DATE] = 2 Or [Education_Comp_DATE] = 73415 Or
True, "N/A", [Education_Comp_DATE])
which doesn't make sense.