Iif Statement in a report

[AndyCabbages]

Thanks for the offer to take a look, I wish it was as easy as that. Unfortunatly due to confidenciality issues it would be a breach of my contact if I shared the database with anyone

The box on the form where the 'PreparedBy' value is a combo box with row source type: Table/Query and the row source is defined as: SELECT [UserList].[Name] FROM UserList;

This can be changed to select 'Location' as well. Surely this means that the field 'PreparedBy' stores both the Username and the location somewhere?

 

[ChrisO]

>>Surely this means that the field 'PreparedBy' stores both the Username and the location somewhere?<<

Not necessarily, it/they could be passed as OpenArgs.
What concerns me is that you called the table Order, singular.
That would imply that the Order table is constructed specifically for that one report.

In any case, let’s assume that 'John Smith' ends up in the 'Prepared By' control in the report.

Try the code I suggested and see what you get; we may be able to take it from there.

 

[AndyCabbages]

I added the code you supplied to the report and when I tried to open it I got the following compile error:

'Method ot data member not found'

 

[ChrisO]

Okay, let’s verify what we have…

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

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

End Sub

Do you have a text box on the report called txtLocation?

Do you have a table called Users?

Does that table have a field called Name?
(Note: remember I changed it from Name to UserName but I think we can set it back to just Name for the moment. So change UserName to Name in the DLookup function above.)

Does field Name in table Users have ‘John Smith’ in it?

Does table Users have a field called Location?

Does that field have ‘London’ in it?

Does the report have a control called txtPreparedBy?

Does the control have ‘John Smith’ in it?

When you check all that do you get ‘London’ in control txtLocation?

 

[AndyCabbages]

The error was being caused because there was no control location called 'txtPreparedBy' it was just 'PreparedBy'

Complete novice mistake on my part, the text box named 'txtLocation' now displays 'London' for those users based in London and 'Slough' for those users based in Slough.

What I have done is created an 'Address' field which contains the address of the company, this is now what is displayed using the following code:

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

Me.txtLocation = DLookup("Address", "UserList", "Name = " & Chr$(34) & Me.preparedBy & Chr$(34))

End Sub


This now works perfectly and returns the full address of the company. Thank you so much for the help and for putting up with my complete lack of experience!

 

[AndyCabbages]

This is what the code looks like for the whole thing now:

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

Me.Department = DLookup("Department", "UserList", "Name = " & Chr$(34) & Me.preparedBy & Chr$(34))
Me.Address = DLookup("Address", "UserList", "Name = " & Chr$(34) & Me.preparedBy & Chr$(34))
Me.Telephone = "Tel: " & DLookup("Tel", "UserList", "Name = " & Chr$(34) & Me.preparedBy & Chr$(34))
Me.Fax = "Fax: " & DLookup("Fax", "UserList", "Name = " & Chr$(34) & Me.preparedBy & Chr$(34))
End Sub

This does the trick perfectly. I really cant thank you enough for your help

 

[ChrisO]

Well done.

 

[Insane_ai]

Have you tried using a subreport for the address information block? You could build a query linked to the user on the main record source for this block. This would be easier than revising the IIF statement or select case statement every time you expand.

 

[jeanlee411]

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.

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)

 

[AndyCabbages]

As a better method I found it easier to store the company address in the Users table and then look that up based on the PreparedBy value.

What this means is that there is now no longer any need to perform an IF or case statement on the resulting value. The resulting value of the code ChrisO provided is the final output value.