iif based on first char ofmthe field vale

[Rony]

Hi
In my table structure i have field "CPARTY" Text field
and the values are 12367867
23467898

Based on the First Character of the value I want to classify it as a Bank
or a customer, something like this

IIF (CPARTY,char(1))=1, banks
IIF (CPARTY,char(1))=2,customers

How can i contruct iif statement.

Thanks
-
Ron

[Tom Wickerath]

Hi Rony,

Try this:

Customer Type: IIF(Left$([CPARTY], 1) = "1", "banks", "customers")

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Kernow Girl]

Hi Rony -
It would go ----

IIF(left([cparty] = 1 ,"Banks, IIf(left([cparty]=2,"Customers "," " ))

Structure is ==== IIf( test, true, false) You can 'embed' IIF statements
in IIF statements (can't remeber how many, look in the HELP screens.) In
yours, the FALSE is the next IIF.

Hope this is what you need ---

Yours - Dika

[Tom Wickerath]

Hi Dika,

I believe this would be more correct for a nested IIF statement:

Customer Type:
IIf(Left([CPARTY],1)="1","banks",IIf(Left([CPARTY],1)="2","customers","unknown"))

Note: This version uses the Left function instead of Left$ that I used in my
first example, just in case the CPARTY field is null.

You can use up to 10 levels of nesting with IIF, but I certainly do not
recommend ever attempting to do so. The resulting field expression will be
such a mess that it will be very difficult to troubleshoot in the future.
When you exceed about three possibilities, such as "banks", "customers" and
"unknown", it is time to write a custom function that includes a SELECT CASE
.... END SELECT. This is *much* easier to maintain.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Steve Schapel]

Tom Wickerath wrote:
> When you exceed about three possibilities, such as "banks", "customers" and
> "unknown", it is time to write a custom function that includes a SELECT CASE
> .... END SELECT. This is *much* easier to maintain.

Agreed. Another idea to consider is the Switch() function.

--
Steve Schapel, Microsoft Access MVP

[Kernow Girl]

Hi Tom - yes, you're absolutely correct - sorry for the Monday morning lapse!
I also agree with not nesting too many levels and I realise that CASE is
certainly easier and cleaner, but keep in mind some of us don't have the time
to do what you can. You can so easily say 'it's time to write a custom
function', or you can even send it to a user, but not everyone has the
knowledge and/or the time to use the knowledge and get it to work. I read
some of the answers to very simple questions and sometimes wonder what the
asker made of them. Ah well - 1 more month and I'm out done. Ta - Dika

[Rony]

Thanks all of you, one more query
I have another field in the same table [tnum] text field, data underneath
0
1
FW

I NEED TO CHECK TWO CONDITIONS

IIF (CPARTY,char(1))=1,AND [TNUM]0 OR 1 "BANKSPOT"
IIF (CPARTY,char(1))=1,AND [TNUM]FW "BANKFORWARD"

IIF (CPARTY,char(1))=2,AND [TNUM]0 OR 1 "CUSTOMERSPOT"
IIF (CPARTY,char(1))=2,AND [TNUM]FW "CUSTOMERFORWARD"

PLEASE GUIDE ME

THANKS IN ADVANCE

RONY

--
Ron

[Tom Wickerath]

Hi Ron,

I think, at this point, that it would be easier if you can settle for the
word "SPOT" or "FORWARD", based on the first character of your TNUM field. Do
this in a manner similar to your CPARTY field, using the Left function.

If you really want the result to be based on the values of both fields, so
that you get one of four possible return values, then it's time to write a
custom VBA procedure. Are you comfortable attempting to do this? I can write
up a quick "how-to" later on tonight, but not right now, since I need to get
to work.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Tom Wickerath]

Hi Dika,

> but not everyone has the knowledge and/or the time to
> use the knowledge and get it to work.

I understand that, however, if they are willing to invest the time, then we
can guide them through the process. It will likely take the person much less
time versus attempting to debug some nasty nested IIF statement with five or
more levels of nesting.

> Ah well - 1 more month and I'm out done.

??

[Rony]

Hi Tom
it is very nice of to guide me via VB, thanks a lot.
Actuall i get the data download from a live system and i trying to classify
through query and no input is involved . Any how when u find time if you can
guide me through VB it can help me to incorporate the VB proc in some other
programme.

Thanks

--
Ron

[Tom Wickerath]

Hi Ron,

Okay, start by creating a new module in your database. Click on the Modules
tab, and then click on the New button. You should see a new code module
opened up, with two lines of code:

Option Compare Database
Option Explicit

If you do not see Option Explicit, then add this line of code manually. Then
click on Tools > Options while in the VBA Editor and place a check in
"Require Variable Declaration", so that you will get these two *very
important* words inserted into all new modules. For more information, please
see this link:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Now copy and paste the following function shown below into your new module.
Save the module as basDetermineCustType. Then click on Debug > Compile
ProjectName. Hopefully, you will not get any compile errors (you may uncover
compile errors in other code modules, however, if you are not compiling any
new/edited code regularly). Add the following to the Field row of your
query, to call your new function:

Customer Type: DetermineCustType([CPARTY],[TNUM])

Here is the function. Note that the URL shown as a reference will be
wrapped. You'll need to make this URL on one line:

'**************Begin Code**************************

' Written just for Ron by Tom Wickerath, 7/24/2006
' http://www.microsoft.com/office/community/en-us/default.mspx?
dg=microsoft.public.access&mid=99251f5c-3aa4-4481-b345-d017b4d29cff

Function DetermineCustType _
(CPARTY As Variant, TNUM As Variant) As String

If IsNull(CPARTY + TNUM) Then
DetermineCustType = "Unknown"
Exit Function
End If

Select Case Left$(CPARTY, 1)
Case "1"
Select Case (TNUM)
Case "0", "1"
DetermineCustType = "BANKSPOT"
Case "FW"
DetermineCustType = "BANKFORWARD"
Case Else
DetermineCustType = "Unknown"
End Select

Case "2"
Select Case (TNUM)
Case "0", "1"
DetermineCustType = "CUSTOMERSPOT"
Case "FW"
DetermineCustType = "CUSTOMERFORWARD"
Case Else
DetermineCustType = "Unknown"
End Select

Case Else

DetermineCustType = "Unknown"

End Select

End Function

'**************End Code**************************

[Rony]

Hi Tom
Thanks for your time. Something new lesson to me.
--
Ron

[Tom Wickerath]

You're welcome!

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Rony" wrote:

> Hi Tom