IIF Functions

[ldmcfall via AccessMonster.com]

Hi,
Just wanted to say thanks in advance to any help. I had a question the other
day on this same report and you guys helped me fix it, so thanks.
What I am doing is trying to figure out which teller at which branch has made
a transaction. I get the information that tells me their station Number. From
that I have another table that says if station number is 1234 then it is Main
Branch, if it is 1235 it is East Branch and so on. The problem is that some
of the transactions that are done are not done by tellers but by others at
the bank that just come up with IP in this field. Because it is a numeric
field it will not bring in IP, nor do I want it to say IP. I comes up Blank.
This is OK that it is Blank, but a blank field needs to know that it is equal
to our Main Branch on my report. I have tried to write an IIF statement in
the query that runs the report but I cannot seem to get it to work. I am new
with access and I am not that familiar with IIF statements. I have a DLookup
on my report that is working fine that tells it when it sees the station
number to go to the BranchName table and put the branch name that is equal to
the Station Number in that table. Here is where it is finding the blanks and
does not put anything on the report. Hope this is not too confusing. Any Help
would be great.
Thanks!

 

[Rob Parker]

Assuming that your blank is actually a null (rather than a zero-length
string - and that seems like a perfectly reasonable assumption here), the
standard way of treating them is to use the nz function. nz is usually used
to convert a null to zero (hence the name), but it can convert it to
anything you define. See Help for more info.

Here, you simply need to wrap your dlookup in an nz, as follows:

nz(dlookup("field","domain","criteria"),""Main Branch")

This will put the string "Main Branch" into the control on your report when
the dlookup returns a null.

HTH,

Rob

 

[Wayne Morgan]

If I'm understanding you correctly, don't sort on the field you mention.
Instead, create a second, calculated field to sort on. This field doesn't
need to show in the report (your choice), it is just there for sorting. (If
you do any sorting or grouping in the report, you'll need to use this field
there instead of in the query. The report will override what sorting you do
in the query.)

First, instead of the DLookup(), it would be faster to place both tables in
the query, join them on their common field, and get the name from the
BranchName table. However, to do this with a calculated field will require a
little more work. You will need 2 queries. The first one will create the
calculated field. In the second you will add the first query and the
BranchName table to the Tables part at the top of the design grid. Link the
two on the calculated field and the BranchID field.

The first query should look like (SQL view):
SELECT *, Nz([Table1].[BranchID], 1234) As CalculatedBranchID FROM Table1;

This will replace the blank (Null) entries with 1234 (Main Branch ID).
Replace Table1 with the appropriate table name. The second query would then
use this query as one of its 2 tables and would use the BranchName table as
the second table. Drag the CalculatedBranchID field from the query/table to
the BranchID in the BranchName table. (If Access tried to create a link for
you automatically, delete it and create this one instead.) Next, drag and
drop the desired fields from each of the table/query to the design grid.
Include this calculated field to sort on instead of the real BranchID from
that table/query. Also drag the BranchName field from the BranchName table
into the grid.