Code in Queries

[ArcherSteve]

I have a query field that currently has an expression that "adds" three text strings together to define a new one. The expression right now is "Division: [Class]+" "+[Male/Female]+" "+[Style]+" "+[In/Out Of State]". Is there any way i can put code in so that is [In/Out Of State] is blank ( ="") then do "Division: [Class]+" "+[Male/Female]+" "+[Style]"? Again any help is greatly appreciated.

 

[jaydwest]

Try creating a public function in a global module. You can then use the function in your query.

Please note that the function will run slower than your current approach, but it will allow you to do the more sophisticated formatting you want and it is more supportable. After you have created the function, e.g. FormatDivision, use the function in your query, by entering

Division: FormatDivision(Class], [Male/Female],[Style],[In/Out Of State])

Good Luck!

 

[dcx693]

Whether you put some code in your query or in a public function, you'll need some way of evaluating the [In/Out Of State] field. The easiest is the IIf function. Basically, it works like this:
IIf(this is true,do this, else do this).

 

[ArcherSteve]

ok, i understand what your saying but I left out one important piece of info....i'm a newbie! so one thing....how do you make a global module? do i just go into the module menu and hit new and start from there?

 

[jaydwest]

Yes. When you add a function in a Global Module, it should automatically be created as a Public Function.

The first line should read

Public Function

 

[ArcherSteve]

and can you elaborate on the IIF function? an example would help alot....like some code....please, i'm still trying to figure this stuff out

 

[dcx693]

In a module, the If...Then...Else...End If structure is more self-explanatory.

It works like this:

If InOutState=True Then
    'Do this
Else
    'Do this instead
End If

 

[ArcherSteve]

;;Delete;;

 

[ArcherSteve]

YES!! i got the Iif function to work. Division: IIf(IsNull([In/Out Of State]),[Class]+" "+[Male/Female]+" "+[Style],[Class]+" "+[Male/Female]+" "+[Style]+" "+[In/Out Of State]).


Now, is it possible to do multiple IIf functions for one field in a query?

 

[ArcherSteve]

I got it! Thanks so much guys!!

 

[Mile-O]

YES!! i got the Iif function to work. Division: IIf(IsNull([In/Out Of State]),[Class]+" "+[Male/Female]+" "+[Style],[Class]+" "+[Male/Female]+" "+[Style]+" "+[In/Out Of State]).


Now, is it possible to do multiple IIf functions for one field in a query?

Use & over + unless you are doing arithmetic. I know there's no obvious difference but it helps to identify which are numerical fields and which are text.

As for multiple IIf statements (nested IIfs) then Yes, it is possible but they can slow a query down considerably due to the amount of evaluation required per record.

But, anyway, it's quite simple - here's the basic structure...

IIf(condition, if true, IIf(condition, if true, if false))