Using an IIF statement

[Tedd]

I am trying to an IIF statement in a query but it is not
working.
I have a query that is picking up data that is based on a
date that I enter. For example: the query gets everything
in COLUMN_A that is <= '20010101'(I provide the date).
That works fine. If there is nothing that is <= the date
nothing is returned.
I am trying to use an IIF statement to give a value if
nothing is found.
Here is my statement:
IIf(IsNull([COLUMN_A]),"No Value Returned",[COLUMN_A]).
When I run the query with a date I know does not exist I
would expect "No Value Returned" to show but I get
nothing. Just an empty cell.
If you have any ideas please let me know.
Thanks

 

[Guest]

-----Original Message-----
I am trying to an IIF statement in a query but it is not
working.
I have a query that is picking up data that is based on a
date that I enter. For example: the query gets everything
in COLUMN_A that is <= '20010101'(I provide the date).
That works fine. If there is nothing that is <= the date
nothing is returned.
I am trying to use an IIF statement to give a value if
nothing is found.
Here is my statement:
IIf(IsNull([COLUMN_A]),"No Value Returned",[COLUMN_A]).
When I run the query with a date I know does not exist I
would expect "No Value Returned" to show but I get
nothing. Just an empty cell.
If you have any ideas please let me know.
Thanks
.

You are asking the query to return records containing the
date you enter as a criteria. It won't return a null if
the date doesn't exist, it will just return an empty
recordset. It would only return a null if there where
fields with NO date entered and you didn't put a specific
date in the criteria field.

 

[Guest]

OK. I guess I am getting an empty record set. So coding
for NULLs will not work.
Do you know of a way to put something in the empty cells
if nothing is found? Like 'N/A' or "No Record Found"?

-----Original Message-----

-----Original Message-----
I am trying to an IIF statement in a query but it is not
working.
I have a query that is picking up data that is based on a
date that I enter. For example: the query gets everything
in COLUMN_A that is <= '20010101'(I provide the date).
That works fine. If there is nothing that is <= the date
nothing is returned.
I am trying to use an IIF statement to give a value if
nothing is found.
Here is my statement:
IIf(IsNull([COLUMN_A]),"No Value Returned",[COLUMN_A]).
When I run the query with a date I know does not exist I
would expect "No Value Returned" to show but I get
nothing. Just an empty cell.
If you have any ideas please let me know.
Thanks
.

You are asking the query to return records containing the
date you enter as a criteria. It won't return a null if
the date doesn't exist, it will just return an empty
recordset. It would only return a null if there where
fields with NO date entered and you didn't put a specific
date in the criteria field.
.

 

[Guest]

Unfortunately the answer is no, you can't do what you are
asking. Why would you want to populate a date field with a
statement. I think what you want is to let the user know
that there are no records for the date selected without
showing an empty recordset. Is the query the control
source of a form or a report. If it is a report then look
at the reports On No Data event. You can add a message box
there and the report will not open. A form is a little
tougher. You would need to check for an empty recordset
before the form loads using some VBA. If you know how to
create a recordset based on a query then just check for
EOF and BOF on the recordset to see if there are records.
You would put code like this in a place where you are
triggering the query to open:

Dim rs as recordset
dim db as database

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM
q_Report10_Hours_Activated WHERE [DateOfRecord] >= #" & Me.
[txtDateField]

If Not rs.EOF Then
'Ther are some records open the form here
Else
'There are no records. Msgbox Here and exit sub
End if


Hope this helps

-----Original Message-----
OK. I guess I am getting an empty record set. So coding
for NULLs will not work.
Do you know of a way to put something in the empty cells
if nothing is found? Like 'N/A' or "No Record Found"?

-----Original Message-----

-----Original Message-----
I am trying to an IIF statement in a query but it is not
working.
I have a query that is picking up data that is based on a
date that I enter. For example: the query gets everything
in COLUMN_A that is <= '20010101'(I provide the date).
That works fine. If there is nothing that is <= the date
nothing is returned.
I am trying to use an IIF statement to give a value if
nothing is found.
Here is my statement:
IIf(IsNull([COLUMN_A]),"No Value Returned",[COLUMN_A]).
When I run the query with a date I know does not exist I
would expect "No Value Returned" to show but I get
nothing. Just an empty cell.
If you have any ideas please let me know.
Thanks
.

You are asking the query to return records containing the
date you enter as a criteria. It won't return a null if
the date doesn't exist, it will just return an empty
recordset. It would only return a null if there where
fields with NO date entered and you didn't put a specific
date in the criteria field.
.

.