iif based on first char ofmthe field vale

R

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
 
K

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
 
T

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
__________________________________________


Kernow Girl said:
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

Rony said:
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
Click to expand...
Click to expand...
 
S

Steve Schapel

Tom said:
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.
Click to expand...

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

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

Tom Wickerath said:
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
__________________________________________


Kernow Girl said:
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

Rony said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

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
 
T

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
__________________________________________
 
T

Tom Wickerath

Hi Dika,
but not everyone has the knowledge and/or the time to
use the knowledge and get it to work.
Click to expand...

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.
Click to expand...
??


Tom Wickerath
Microsoft Access MVP

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

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 said:
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
__________________________________________

Rony said:
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
Click to expand...
Click to expand...
 
T

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**************************


Tom Wickerath
Microsoft Access MVP

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

Rony

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


Tom Wickerath said:
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**************************


Tom Wickerath
Microsoft Access MVP

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

Rony said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the right style field in VBA 0
Data Mismatch in Excel 2010 4
Link a text box based on drop down box with four options MS Word 365 0
sum of calculated fields in header of report 1
IIF statement using 2 criteria 1
Filtering Records based on results of an IIF statement 2
Sum IIF Expression One Field Two Codes 8
Weird results (I think) for IIF 4

Top