How do I use IIF?

P

Phillip

I am trying to use the IIF statement to determine if the a
query returned anything.

I have a query that selects an employee number and a date
that is equal to the number and date I enter. That works.
I then have another query that is looking at the results
of the first query(where I select the employee number and
date) to determine if anything was returned.

I was trying to use the IIF statement to do something like
this: IIF(isNull[employee number], "No Number Exists",
employee number). I would do the same thing for the date
field. So in other words if nothing is returned from the
first query, the results are null, then it will
display "No Number Exists" in the field.

I don't even know if this is the correct/best way to do
that. If anyone has any ideas please let me know.

Thanks.
 
B

Brendan Reynolds

That's almost right, except that IsNull() is a VBA function, in SQL you need
IS NULL, with a space between 'IS' and 'NULL'. Something like ...

SELECT IIF(IS NULL [employee number], "No Number Exists", [employee number])
AS MyAlias FROM MyTable
 
C

ChrisM

Hi Phillip,

i don't think that you want the IIF in the query in this case:
It is important to realise the distinction between the query returning a
record containing null and a query returning no records

the IIF statement that you quote would work if your query returned a record
from the database that had a null in the EmployeeNumber field, but in this
case the query is returning NO records at all if the number/date don't
match. So your IIF statement will never get run.

As to a way to make this work, You could write some VB code to check and
update the textbox accordingly.
I have had a quick play, and somthing you could try is to set the
'DefaultValue' property of the text box to 'No Number' or whatever, and it
should show that if the query returns no rows.

ChrisM
 
P

Phillip

Thanks. This is what I have: "Employee Number": IIf(IsNull
([610005_emp_num]),0,[610005_emp_num]). 610005 is the
name of my table. I made sure the default value on the
table for this field is Null.

The query runs but does not show a '0' in the field if it
can find anything like I would expect. It shows the
employee number if it finds it.

Also, I tried adding another table to this query. I get
results if there is a match but don't get anything if
there isn't.

Do you have any ideas? Thanks for your help.

-----Original Message-----
That is exactly the way to do it. In a query column the syntax would be:

Expr1: IIF(isNull([employee number]), "No Number Exists", [employee number])
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


I am trying to use the IIF statement to determine if the a
query returned anything.

I have a query that selects an employee number and a date
that is equal to the number and date I enter. That works.
I then have another query that is looking at the results
of the first query(where I select the employee number and
date) to determine if anything was returned.

I was trying to use the IIF statement to do something like
this: IIF(isNull[employee number], "No Number Exists",
employee number). I would do the same thing for the date
field. So in other words if nothing is returned from the
first query, the results are null, then it will
display "No Number Exists" in the field.

I don't even know if this is the correct/best way to do
that. If anyone has any ideas please let me know.

Thanks.
Click to expand...


.
Click to expand...
 
D

Dirk Goldgar

Brendan Reynolds said:
That's almost right, except that IsNull() is a VBA function, in SQL
you need IS NULL, with a space between 'IS' and 'NULL'. Something
like ...

SELECT IIF(IS NULL [employee number], "No Number Exists", [employee
number]) AS MyAlias FROM MyTable
Click to expand...

Brendan, I think you've got that backward. It should either be

SELECT IIF([employee number] IS NULL,
"No Number Exists",
[employee number])
AS MyAlias
FROM MyTable

or else the pefectly valid (if executed within Access)


SELECT IIF(IsNull([employee number]),
"No Number Exists",
[employee number])
AS MyAlias
FROM MyTable
 
B

Brendan Reynolds

What's the data type of the field? If it is a text field, it's possible that
the field might contain an empty string rather than a Null. Try something
like ...

SELECT IIf([YourField] Is Null Or [YourField]="","0",[YourField]) AS
YourAlias
FROM YourTable;

--
Brendan Reynolds (MVP)

Phillip said:
Thanks. This is what I have: "Employee Number": IIf(IsNull
([610005_emp_num]),0,[610005_emp_num]). 610005 is the
name of my table. I made sure the default value on the
table for this field is Null.

The query runs but does not show a '0' in the field if it
can find anything like I would expect. It shows the
employee number if it finds it.

Also, I tried adding another table to this query. I get
results if there is a match but don't get anything if
there isn't.

Do you have any ideas? Thanks for your help.

-----Original Message-----
That is exactly the way to do it. In a query column the syntax would be:

Expr1: IIF(isNull([employee number]), "No Number Exists", [employee number])
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


I am trying to use the IIF statement to determine if the a
query returned anything.

I have a query that selects an employee number and a date
that is equal to the number and date I enter. That works.
I then have another query that is looking at the results
of the first query(where I select the employee number and
date) to determine if anything was returned.

I was trying to use the IIF statement to do something like
this: IIF(isNull[employee number], "No Number Exists",
employee number). I would do the same thing for the date
field. So in other words if nothing is returned from the
first query, the results are null, then it will
display "No Number Exists" in the field.

I don't even know if this is the correct/best way to do
that. If anyone has any ideas please let me know.

Thanks.
Click to expand...


.
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Doh! :-(

I'd still favour the SQL IS NULL over the VBA IsNull(), however - why use
something that only works within Access when you can achieve the same result
using standard SQL that will work anywhere you can use a SQL string?

--
Brendan Reynolds (MVP)

Dirk Goldgar said:
Brendan Reynolds said:
That's almost right, except that IsNull() is a VBA function, in SQL
you need IS NULL, with a space between 'IS' and 'NULL'. Something
like ...

SELECT IIF(IS NULL [employee number], "No Number Exists", [employee
number]) AS MyAlias FROM MyTable
Click to expand...

Brendan, I think you've got that backward. It should either be

SELECT IIF([employee number] IS NULL,
"No Number Exists",
[employee number])
AS MyAlias
FROM MyTable

or else the pefectly valid (if executed within Access)


SELECT IIF(IsNull([employee number]),
"No Number Exists",
[employee number])
AS MyAlias
FROM MyTable

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Brendan Reynolds said:
Doh! :-(

I'd still favour the SQL IS NULL over the VBA IsNull(), however - why
use something that only works within Access when you can achieve the
same result using standard SQL that will work anywhere you can use a
SQL string?
Click to expand...

Agreed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL IIF statement with TOP 10 0
IIF(AND) statement 3
IIf return multiple categories 6
Query Criteria IIf statement 4
Create a status designation from 5 date fields using "Not IsNull" 10
Iif Function 6
Truble with Date in Query and IIF 1
Nested IIF Statements 3

Top