By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,944 Members | 1,503 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,944 IT Pros & Developers. It's quick & easy.

iif statement dealing with nulls

P: n/a
using access 2000. Have a form based on a couple of controls, it
finds the last used Inquiry number in a table. So it finds the
highest Inquiry number for the "Office" , "Company" and "YearStamp"
selected by the user.

Code below:

=IIf(IsNull(DMax("[Inquiry Number]","Inquiry","[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " &
[Form]![Year])),0,DMax("[Inquiry Number]","Inquiry","[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " & [Form]![Year]))

I use to just use the DMax but had to change it to an IIF because of
nulls. But this code simply places #ERROR in the control when it is
null.

Am I doing something wrong? Wait obviously I am doing something
wrong. Can someone lead me in the right direction.

I'm doing this iif statement in the control source property.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
just off the cuff, have you tried using NZ(dmax etc.) instead of the iif
rdshultz <rd******@nooter.com> posted in
news:87**************************@posting.google.c om
using access 2000. Have a form based on a couple of controls, it
finds the last used Inquiry number in a table. So it finds the
highest Inquiry number for the "Office" , "Company" and "YearStamp"
selected by the user.

Code below:

=IIf(IsNull(DMax("[Inquiry Number]","Inquiry","[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " &
[Form]![Year])),0,DMax("[Inquiry Number]","Inquiry","[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " & [Form]![Year]))

I use to just use the DMax but had to change it to an IIF because of
nulls. But this code simply places #ERROR in the control when it is
null.

Am I doing something wrong? Wait obviously I am doing something
wrong. Can someone lead me in the right direction.

I'm doing this iif statement in the control source property.


--
Phil
Nov 13 '05 #2

P: n/a
how about using NZ(<<expression>>) which will convert the Null value
to zero for you?
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.