IIF's Text is Too Long in Query -- Need help w/ creating a module

[Tom]

Does anyone know a way as to how I create a module into which I can place
the code below?

I tried to copy/paste the multiple IIFs into a query but the "text is too
long" (I actually have even more IIFs that I list below).

If that's possible in a module, I then probably could call the module's
function, right?

Or is there even a better way of doing this?


***************************
PayeeShort: IIf([Payee]="Bank One Credit Card Services","Bank One CCS",
IIf([Payee]="Charter Communications","Charter",
IIf([Payee]="Columbia Gas of Virginia","Columbia Gas",
IIf([Payee]="Dominion Virginia Power","Dominion VA Power",
IIf([Payee]="Direct TV","DTV",
IIf([Payee]="Greenbrier Owners Association","Greenbrier",
IIf([Payee]="MCI Residential Service","MCI")))))))

***************************

 

[Ken Snell [MVP]]

Create a table named PayeeAliases. Put two fields in it: Payee (primary key)
and Alias. Put the payee names in the first field and the aliases in the
second. Then add this table to your query, joining it on Payee field, and
use the field from the table instead of the long IIf.

 

[fredg]

Does anyone know a way as to how I create a module into which I can place
the code below?

I tried to copy/paste the multiple IIFs into a query but the "text is too
long" (I actually have even more IIFs that I list below).

If that's possible in a module, I then probably could call the module's
function, right?

Or is there even a better way of doing this?

***************************
PayeeShort: IIf([Payee]="Bank One Credit Card Services","Bank One CCS",
IIf([Payee]="Charter Communications","Charter",
IIf([Payee]="Columbia Gas of Virginia","Columbia Gas",
IIf([Payee]="Dominion Virginia Power","Dominion VA Power",
IIf([Payee]="Direct TV","DTV",
IIf([Payee]="Greenbrier Owners Association","Greenbrier",
IIf([Payee]="MCI Residential Service","MCI")))))))

***************************

From the main database folder, click on Modules + New
Click on Insert + Procedure.
Enter the name of your function and then write one.

You can use a Select Case or an If ..Then .. Else function.

Function Whatever(PayTo as String) as String
Dim strString as String

If PayTo = "Bank One Credit Card Services" Then
strString = "Bank One CCS"
ElseIf PayTo = "Charter Communications" Then
strString = "Charter"
etc.
Else
strString = "Unknown"
End If
Whatever = strString
End Function
===========
Using Select Case is just as easy.
Function Whatever(PayTo as String) as String
Dim strString as String
Select Case PayTo
Case "Bank One Credit Card Services"
strString = "Bank One CCS"
Case "Charter Communications"
strString = "Charter"
Etc.
Case Else
strString = "Unknown"
End Select
Whatever = strString
End Function
===========

Call it from your query, using:
Exp:Whatever([FieldName])

 

[Tom]

Thanks, Fred, that works great!

--
Tom

Does anyone know a way as to how I create a module into which I can place
the code below?

I tried to copy/paste the multiple IIFs into a query but the "text is too
long" (I actually have even more IIFs that I list below).

If that's possible in a module, I then probably could call the module's
function, right?

Or is there even a better way of doing this?

***************************
PayeeShort: IIf([Payee]="Bank One Credit Card Services","Bank One CCS",
IIf([Payee]="Charter Communications","Charter",
IIf([Payee]="Columbia Gas of Virginia","Columbia Gas",
IIf([Payee]="Dominion Virginia Power","Dominion VA Power",
IIf([Payee]="Direct TV","DTV",
IIf([Payee]="Greenbrier Owners Association","Greenbrier",
IIf([Payee]="MCI Residential Service","MCI")))))))

***************************

From the main database folder, click on Modules + New
Click on Insert + Procedure.
Enter the name of your function and then write one.

You can use a Select Case or an If ..Then .. Else function.

Function Whatever(PayTo as String) as String
Dim strString as String

If PayTo = "Bank One Credit Card Services" Then
strString = "Bank One CCS"
ElseIf PayTo = "Charter Communications" Then
strString = "Charter"
etc.
Else
strString = "Unknown"
End If
Whatever = strString
End Function
===========
Using Select Case is just as easy.
Function Whatever(PayTo as String) as String
Dim strString as String
Select Case PayTo
Case "Bank One Credit Card Services"
strString = "Bank One CCS"
Case "Charter Communications"
strString = "Charter"
Etc.
Case Else
strString = "Unknown"
End Select
Whatever = strString
End Function
===========

Call it from your query, using:
Exp:Whatever([FieldName])

 

[Tom]

Ken,

wow, that's a good idea... sometimes, the easiest solutions don't come to
mind.

--
Thanks,
Tom

Create a table named PayeeAliases. Put two fields in it: Payee (primary key)
and Alias. Put the payee names in the first field and the aliases in the
second. Then add this table to your query, joining it on Payee field, and
use the field from the table instead of the long IIf.

--

Ken Snell
<MS ACCESS MVP>

Does anyone know a way as to how I create a module into which I can place
the code below?

I tried to copy/paste the multiple IIFs into a query but the "text is too
long" (I actually have even more IIFs that I list below).

If that's possible in a module, I then probably could call the module's
function, right?

Or is there even a better way of doing this?


***************************
PayeeShort: IIf([Payee]="Bank One Credit Card Services","Bank One CCS",
IIf([Payee]="Charter Communications","Charter",
IIf([Payee]="Columbia Gas of Virginia","Columbia Gas",
IIf([Payee]="Dominion Virginia Power","Dominion VA Power",
IIf([Payee]="Direct TV","DTV",
IIf([Payee]="Greenbrier Owners Association","Greenbrier",
IIf([Payee]="MCI Residential Service","MCI")))))))

***************************