Iif Statement in a report

[AndyCabbages]

Hey, I am having some trouble with an Iif statement. My current one works but it is massive, and impracticle.

Here is the problem:

The Iif statement is supposed to generate an address depending on which department creates the invoice (one department is in Slough, and the other is in London, but they both use the system).

Currently the Names of the users of the system are stored in a 'Users' table. This table contains the fields: 'Name' and 'Location'.

The form which generates the report has a drop down menu labeled: 'Prepared By' which looks up 'Users.Name'. This then saves the selection in an 'Invoice' table under the field name 'PreparedBy'

This 'Prepared By' value is stored as a name (E.G. 'John Smith') which is displayed on the report.

There is only a total of 5 users at the moment, with 3 in the Slough department and 2 in the London department, but future users might be added.

Currently the way the Iif statement is set up like:

Iif Prepare By = (Names of the Slough Department) then (Slough address) else (London Address).

This is very impracticle, first of all becuase the Iif statement is massive, and second of all because there is absolutely no room for expansion, if anyone gets added to the userlist then the whole statement has to be re-written.

What I am looking to do is somehow select both the user's name AND location and store them both in the 'PreparedBy' field. Then somehow write the Iif statement as something more like:

Iif PreparedBy (Location) = Slough do Slough address, else do London Address.


I have not been able to find any way of selecting both the User's name and location when adding a value to the 'PreparedBy' field or any way of representing this in the Iif statement. I am wondering if there is any way to do this? I hope my explaination is understandable

 

[Mr. B]

Have you considered a Case statement?

 

[AndyCabbages]

A case statement would definitely make it easier to read and would allow for easier expansion, but the statement would be much longer since the address would have to e typed out for each of the 5 cases (1 case per user).

 

[ChrisO]

As an alternative (without using ‘Name’ as a field name)…

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Me.txtLocation = DLookup("Location", "Users", "UserName = " & Chr$(34) & Me.txtPreparedBy & Chr$(34))

End Sub

 

[AndyCabbages]

ChrisO, where would that code fit in? What does it do? and whats the output value?

 

[ChrisO]

Behind the Report…
Private Sub Detail_Format

It looks up the Location in table Users based on the UserName in the text box PreparedBy.

It returns the Location or a Null if not found.

The Chr$(34) is an easy to read double quote, and will protect your report from those nasty people with apostrophes in their name.

 

[AndyCabbages]

Where does this resulting value appear?

In terms of using it in an iff statement would it be something like:

Iif Me.textlocation = 'Slough' then do Slough address else do London address?

Or a similar concept but in a case statement (as Mr B suggested)

 

[ChrisO]

>>Where does this resulting value appear?<<
If a Location is found for UserName it should appear in Me.txtLocation on the Report.


>>In terms of using it in an iff statement would it be something like:<<
You don’t need an if statement or a select case statement. Either of these would be hard coding the names and locations behind the report.
If you do that then every change, addition or deletion would require modifying the code.

>>This 'Prepared By' value is stored as a name (E.G. 'John Smith') which is displayed on the report.<<
So, your report already has the UserName in the report’s 'Prepared By' control.
You use the UserName to look up the Location from the Users table.

Me.txtLocation = DLookup("Location", "Users", "UserName = " & Chr$(34) & Me.txtPreparedBy & Chr$(34))

Or is there something I’m missing here?

 

[AndyCabbages]

The 'PreparedBy' value is already displayed on the report in a text box.

What I want to do is have another text box which says a bit more than either 'Slough' or 'London'. I want something that looks at the 'PreparedBy' value, looks this up the table to get the 'Location' value (as your code does), then performs an IF statement on this thats like:

If [Location] = 'Slough' then "234 Bath Road, Slough, SL1 4" else "Hanover Trading Estate, 1-3 North Roadhttp://maps.google.co.uk/, London, N7 9HD" (as a fictional example)

The 'User' table ONLY records Username and Location. There is no address field in the system so this has to be typed manually (perhaps it would be practical to have the address recorded in the table as well)

Hope this is clear

 

[ChrisO]

>>perhaps it would be practical to have the address recorded in the table as well<<

I would say it would be impractical not to store them in a table.

 

[AndyCabbages]

Yeah, I think that would be easier.

Say my updated 'User' table has the following fields:

Username, Location, Addres Line 1, Address Line 2, Address Line 3, Postcode, Tel, Fax (A new form would probably be created for filling all this in)

The question:

How can I get Access to look at the 'PreparedBy' value, look this up in the 'Username' field and then return the 'Address Line1', 'Address Line2', 'Address Line3', 'Postcode', 'Tel', and 'Fax' values and put them into the report?

(This now makes the 'Location' field kind of redundent and removes the need for any sort of IIF or Case statement)

Thoughts?

 

[ChrisO]

If the 'PreparedBy' value is in the report’s underlying query then link in the Users table based on the 'PreparedBy' value.
You can then directly bind new controls on the report to the address fields.

 

[AndyCabbages]

The 'PreparedBy' value appears as an accepted value in the 'Control source' value of a text box (This means it is in the report's underlying query?)

How do I go about linking in the users table based on the 'PreparedBy' value?

 

[ChrisO]

Is ‘PreparedBy’ in the reports query?

 

[AndyCabbages]

The report is not created from a query, it just takes various fields from different tables. Is this what you mean?

 

[ChrisO]

Yes, that’s sort of what I mean.

So it sounds like the controls on the report get their data using DLookup’s from different tables; is that correct?

 

[AndyCabbages]

Yes, 'PreparedBy' is actually a field in another table called 'Order'. This value is defined by a drop down menu in a form which lets you choose from the list of usernames recorded in the 'Users' table.

The report takes the value of 'Order.PreparedBy'

 

[ChrisO]

Then is the report bound to the table Order?
Or, how does Order.PreparedBy get into the report?

 

[AndyCabbages]

As far as I can tell the report takes values from the Order table as well as values from subtables within the Order table. I am not completely sure how Order.PreparedBy gets in there.

Sorry for my lack of understanding of exactly what is going on. I was not the person who created the database, it was created by an old employee with barely any documentation and I've been given the job of 'upgrading' it.

 

[ChrisO]

I think it’s time to see your database.
It sounds like there are lookup fields in table Order.

Can you upload an unsecured sample in Access 2003?