Problem! (1 Viewer)

star777

Registered User.
Local time
Today, 12:16
Joined
Jul 20, 2018
Messages
15
Hi All,
I am a new member on this site. Could you please help on this problem.

For the selected vendors:
A) if at least one of the indicators LOEVM, SPERR, or SPERM ="X", set COMPANIES.DISABLED to "1"
ELSE
B) If SPERQ = one of the values below:
PA (Vendor Moved)
PD (Block for Duplication)
PE (Block for Commercial Issue)
QF (Disapproved)
QG (Inactivated)
QH (Unapproved)
set COMPANIES.DISABLED to "1"
ELSE:
C) set COMPANIES.DISABLED to "0".

I tried with
"IIf([LOEVM]="X" and [SPERQ]="PA" or "PD" or "PE" or "QF" or "QG" or "QH",1 Or 0,IIf([SPERR]="X" And [SPERQ]="PA" or "PD" or "PE" or "QF" or "QG" or "QH",1 or 0,IIf([SPERM]="X" And [SPERQ]="PA" or "PD" or "PE" or "QF" or "QG" or "QH",1,0)))
"
didn't work.

Thanks.
 
You need to specify the field for each value of SPERQ.
So you need a very long series of OR statements in your IIf function

Code:
IIf(LOEVM="X" OR SPERR ="X" OR SPERM="X" OR SPERQ="PA" OR SPERQ="PB" OR ......,1,0)

However the expression will be so lengthy that I would suggest using a different approach such as
1. Select Case statement
2. A Lookup table
 
Hi Colin,
Thanks a lot. I will try if then first. if it does not work i want to try 1. Select Case statement
2. A Lookup table

could you please help me with that. thanks .
LifeSaver!
 
Is [COMPANIES.DISABLED] overrideable? Is it independent of the calculation you are doing?

What I mean is, are you trying to set an initial value for [COMPANIES.DISABLE] that can later be over ridden by a human who changes it? Or is the value of [COMPANIES.DISABLE] completely dependent on the logic you laid out above?
 
Is [COMPANIES.DISABLED] overrideable? Is it independent of the calculation you are doing?

What I mean is, are you trying to set an initial value for [COMPANIES.DISABLE] that can later be over ridden by a human who changes it? Or is the value of [COMPANIES.DISABLE] completely dependent on the logic you laid out above?

Hi Thank you.
Yes it is independent.
 
Ok, just to be clear that means a record could have

SPERM ="X" and COMPANIES.DISABLE = 0

That would be valid data and not a conflict.
 
You need to specify the field for each value of SPERQ.
So you need a very long series of OR statements in your IIf function

Code:
IIf(LOEVM="X" OR SPERR ="X" OR SPERM="X" OR SPERQ="PA" OR SPERQ="PB" OR ......,1,0)

However the expression will be so lengthy that I would suggest using a different approach such as
1. Select Case statement
2. A Lookup table

Hi Colin,
how do i get B) If SPERQ = one of the values below:
PA (Vendor Moved)
PD (Block for Duplication)
PE (Block for Commercial Issue)
QF (Disapproved)
QG (Inactivated)
QH (Unapproved)

i mean instead of PA i must show
(Vendor moved) for example
 
Ok, just to be clear that means a record could have

SPERM ="X" and COMPANIES.DISABLE = 0

That would be valid data and not a conflict.

Hi,
Thank you.
Yes valid data.

if SPERM="X" it must be 1 in the COMPANIES.DISABLE column.
 
Not sure I understand...
Possibly use 'Vendor moved' instead of 'PA' in the IIf function?
Or use a DLookup?
 
Not sure I understand...
Possibly use 'Vendor moved' instead of 'PA' in the IIf function?
Or use a DLookup?


Hi Colin,
Thanks again.
As you see the SPERQ column contains only PA not (Vendor Moved)!
I would have to show (Vendor Moved) instead of PA where applicable.
is it possible?

For the selected vendors:
A) if at least one of the indicators LOEVM, SPERR, or SPERM ="X", set COMPANIES.DISABLED to "1"
ELSE
B) If SPERQ = one of the values below:
PA (Vendor Moved)
PD (Block for Duplication)
PE (Block for Commercial Issue)
QF (Disapproved)
QG (Inactivated)
QH (Unapproved)
set COMPANIES.DISABLED to "1"
ELSE:
C) set COMPANIES.DISABLED to "0".
 
Where do you want to show the phrase instead of PA?
In a query or a form?

Suggest you create a separate lookup table with two fields for SPERQValue and SPERQDescription then add that table to your query if feasible
 
Where do you want to show the phrase instead of PA?
In a query or a form?

Suggest you create a separate lookup table with two fields for SPERQValue and SPERQDescription then add that table to your query if feasible

In a query!
not clear to me.
Could you add an example please?
 
Sorry but no.
Answering on a tablet so can't provide code
 
Create a table with two fields SPERQValue and SPERQDescription
Add the values ;
Code:
PA     Vendor Moved
PD     Block for Duplication
PE     Block for Commercial Issue
QF     Disapproved
QG    Inactivated
QH     Unapproved

Now add this table to you query and join the SPERQValue to the field in your existing query.
 
Create a table with two fields SPERQValue and SPERQDescription
Add the values ;
Code:
PA     Vendor Moved
PD     Block for Duplication
PE     Block for Commercial Issue
QF     Disapproved
QG    Inactivated
QH     Unapproved

Now add this table to you query and join the SPERQValue to the field in your existing query.

Hi Minty,
Thank you.
This is just an example. It is a huge table with huge records. Is it possible? How?
 
Even more reason to add these codes to a table with a description then.

I would probably paste them into excel and manipulate the text to create the correct layout, then paste as a new table back into access.
 
Just to chip in with a relatively minor comment : Companies.Disabled looks and sounds to me like a Boolean. Can it have values other than 0 or 1 ? If not then define it as a Boolean and use True and False as its values rather than 0 and 1. And I would also probably have used Companies.Enabled as the field name rather than .Disabled to avoid double negatives (e.g. "Enabled"/"not Enabled" is a bit easier to grasp than "Not Disabled/Disabled").
 

Users who are viewing this thread

Back
Top Bottom