Using an IIF function over and over again

[Valery]

Hi all!

I am using one of these two IIF functions in almost every report I need to create. Because I don't know any other way, I have saved them into a word file for quick access (opened on my second monitor) and keep doing copy/paste in my report queries. Is there a better way??

TenantSal: IIf([Gender]="M","M. " & [FirstName] & " " & [LastName],"Mme " & [FirstName] & " " & [LastName])

TenSalStat: IIf([Status]="M" And [Gender]="M ","M. " & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="M" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="O" And [Gender]="M","M. " & "  " & [FirstName] & " " & [LastName] & ", Occupant",IIf([Status]="O" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante"))))

Please include detailed information - I am sort of a newbie... THANK YOU

 

[JHB]

Create an extra field in your table for holding the "M./Mme".
In the table where you have their names, and register it when you type in the names. It can be done automatic when you choose the gender.

 

[Cronk]

I agree with adding an extra field, except I'd call the field Title (Titre?) because that way you can have Prof., Doctor, General (ret) or whatever.

 

[JHB]

..except I'd call the field Title (Titre?)

I haven't given any field name, (then I wouldn't call it "M./Mme").

 

[Valery]

Thank you for the tips everyone.

However, creating a new field to hold M./Mme and so on stills requires the query to concatenate FirstName and LastName... so not much saved there since with the new field I have to ad it, size it, place it in the report. It would alleviate the second expression also but again, not dispense of it altogether as it does many other things...

So no function - module - or macro - is possible to speed things up?

 

[Minty]

If you are using access 2010 it has a new calculated field type. If you added the Title field you could then have a calculated field called
FullName = [Title] & " " & [FirstName] & " " & [LastName]

 

[Valery]

A calculated field? Wow - how do you do that? Directly in the table?

 

[sneuberg]

A calculated field? Wow - how do you do that? Directly in the table?

Yes, you just add the field like any other but the data type is calculated. When you choose calculate in the drop down it will invoke the expression builder.

Purist will tell you not to do this as having a field that depends on other field violates normal form, but I have yet to see a situation where they cause a problem.

 

[Valery]

Sneuberg, works great for Salutation (M. Xxxx Xxxxx). Why am I getting a syntax error with this:

IIf([Status]="M" And [Gender]="M ","M. " & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="M" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="O" And [Gender]="M","M. " & "  " & [FirstName] & " " & [LastName] & ", Occupant",IIf([Status]="O" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante"))))

I tested with a simple IIF statement and it works but the above won't. Anything wrong in my syntax? Works as is in a query...

 

[JHB]

Thank you for the tips everyone.

However, creating a new field to hold M./Mme and so on stills requires the query to concatenate FirstName and LastName... so not much saved there since with the new field I have to ad it, size it, place it in the report. It would alleviate the second expression also but again, not dispense of it altogether as it does many other things...
So no function - module - or macro - is possible to speed things up?

The extra field is the fastest, the IIF function is time consuming and the same is a module/macro:

TenSalStat: [Title] & " " &  [FirstName] & " " & [LastName]

 

[Valery]

Yes, it is. As I said, the Mr. John Doe works fine.

But the second one I want to do does not work. See my last post. Can you help? TYTTY

It should come out : M. John Doe, Member or M. John Doe, Occupant (with an "e" at the end if a female)...

 

[sneuberg]

Sneuberg, works great for Salutation (M. Xxxx Xxxxx). Why am I getting a syntax error with this:

IIf([Status]="M" And [Gender]="M ","M. " & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="M" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre",IIf([Status]="O" And [Gender]="M","M. " & "  " & [FirstName] & " " & [LastName] & ", Occupant",IIf([Status]="O" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante"))))

I tested with a simple IIF statement and it works but the above won't. Anything wrong in my syntax? Works as is in a query...

You have two problems. First the last IIF in this doesn't have an argument for the false part. This is what would be return if none of the conditions were met. Since this shouldn't be possible I suggest just putting in null for that. When I did that it returned Null for the Gender = "M" and Status = "M". That's because you have [Gender]="M " in the first IIF, an space after M. The corrected expression is:

IIf([Status] = "M" And [Gender] = "M", "M. " & "  " & [FirstName] & " " & [LastName] & ", Membre", IIf([Status] = "M" And [Gender] = "F", "Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre", IIf([Status] = "O" And [Gender] = "M", "M. " & "  " & [FirstName] & " " & [LastName] & ",  Occupant", IIf([Status] = "O" And [Gender] = "F", "Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante", Null))))

It can be very difficult to find these errors just poking at it in a query as you don't get much information as to what is wrong. If you need to debug one of these in the future I suggest setting up a small program to test the expression in a module. This may seem like a lot of work, but in the end it can save you time. Here's what to do:

1. In the CREATE tab in Macros & Code click on Visual Basic or Press Alt F11
2. Optional but highly recommended that you Click Tools, Options and in the Editor Tab check Require Variable Declaration
3. Click Insert and then Module
4. Click Insert and then Procedure. Any name will do, let's say Test
5. In the subroutine Type Debug.Print and then copy and paste your expression after it.
6. If you click Debug above and then Compile at this point it will probably complain about missing variables.
7. Before the expression declare and assign values to the fields in the expression. When I did this I ended up with something like

Public Sub Test()

Dim Gender As String
Dim Status As String
Dim FirstName As String
Dim LastName As String

Gender = "M"
Status = "M"
FirstName = "Steven"
LastName = "Heller"

Debug.Print IIf([Status] = "M" And [Gender] = "M", "M. " & "  " & [FirstName] & " " & [LastName] & ", Membre", IIf([Status] = "M" And [Gender] = "F", "Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre", IIf([Status] = "O" And [Gender] = "M", "M. " & "  " & [FirstName] & " " & [LastName] & ",  Occupant", IIf([Status] = "O" And [Gender] = "F", "Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante"))))

End Sub

When I compiled at this point it highlighted the last IIF and the error message was "Argument not optional" . That's better than just "Syntax Error" but I still didn't know what it was referring to so I split the expression into different lines use the underscore to extend the code to the next line. It this format I could see that the last argument was missing. I split it out as shown below, but you could do it anyway that helps you, but you can't do this in the query builder.

Public Sub Test()

Dim Gender As String
Dim Status As String
Dim FirstName As String
Dim LastName As String

Gender = "M"
Status = "M"
FirstName = "Steven"
LastName = "Heller"

Debug.Print IIf([Status] = "M" And [Gender] = "M", _
    "M. " & "  " & [FirstName] & " " & [LastName] & ", Membre", _
        IIf([Status] = "M" And [Gender] = "F", _
            "Mme" & "  " & [FirstName] & " " & [LastName] & ", Membre", _
                IIf([Status] = "O" And [Gender] = "M", _
                    "M. " & "  " & [FirstName] & " " & [LastName] & ",  Occupant", _
                        IIf([Status] = "O" And [Gender] = "F", _
                            "Mme" & "  " & [FirstName] & " " & [LastName] & ", Occupante", Null))))

End Sub

To see the result of the Debug.Print open the immediate window by clicking View and Immediate Window or press ctrl G. To test the code place the cursor in the code and press F5.

 

[Valery]

You are a teacher! Learning much but this is a bit over my head still. I get how to do it (I just did try it). However, I did not add the "Dim" parts... and the fields... only what I know how to write : the IIF statement or expression... I am going to keep this preciously in my "learning folder" and keep at it as I go. THANK YOU

 

[Valery]

It worked! For the benefit of others, here it is. In a table, a field with it's property as calculated (using MS Access 2010):

IIf([Status]="M" And [Gender]="M","M." & "  " & [FirstName] & " " & [LastName] & ", " & "Membre",IIf([Status]="M" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", " & "Membre", IIf([Status]="O" And [Gender]="M","M." & "  " & [FirstName] & " " & [LastName] & ", " & "Occupant",IIf([Status]="O" And [Gender]="F","Mme" & "  " & [FirstName] & " " & [LastName] & ", " & "Occupante",Null))))

This returns, in French, all possibilities with the tenant's salutation, name and status, considering their gender, so :

M. John Doe, Membre
Mme Jane Doe, Membre
M. Joe Smith, Occupant
Mme Jane Smith, Occupante

Super time saver for forms, reports and queries!