How to show table value with new properties! (1 Viewer)

S

star777

Registered User.

Local time

Today, 01:54

Joined

Jul 20, 2018

Messages

15

• Jul 22, 2018

• #1

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".

The SPERQ is a field with values PA, PD, PE, QF, QG, QH etc. I want to show value properties as well in the query with IIF stamnt. Is it possible?
Any other way?
Now, I want to enter value properties

 

isladogs

MVP / VIP

Local time

Today, 09:54

Joined

Jan 14, 2017

Messages

18,219

• Jul 22, 2018

• #2

This is a repeat of an existing thread https://www.access-programmers.co.uk/forums/showthread.php?t=300609

The original thread had multiple replies. Why start a new thread?

 

S

star777

Registered User.

Local time

Today, 01:54

Joined

Jul 20, 2018

Messages

15

• Jul 22, 2018

• #3

ridders said:

This is a repeat of an existing thread https://www.access-programmers.co.uk/forums/showthread.php?t=300609

The original thread had multiple replies. Why start a new thread?

Click to expand...

Hi Colin,
Thank you.
Yes but problem is not solved.
Could you please have a look?

please find the file attached.

 

Attachments

• L09. VENDOR v6.2C 2018-07-17 - Corrective data load for DISABLED and BBA_COSTATUS.xlsx

  27.4 KB · Views: 142

isladogs

MVP / VIP

Local time

Today, 09:54

Joined

Jan 14, 2017

Messages

18,219

• Jul 22, 2018

• #4

Several people including myself gave you advice in the previous thread.
You keep repeating the IF...ELSE..END IF statement but there is no indication that you've attempted what has been suggested.

I now see its taken straight from the Excel file but that seems to add nothing of any consequence as far as I could see.

Am I correct in assuming you don't really understand this.

 

S

star777

Registered User.

Local time

Today, 01:54

Joined

Jul 20, 2018

Messages

15

• Jul 22, 2018

• #5

ridders said:

Several people including myself gave you advice in the previous thread.
You keep repeating the IF...ELSE..END IF statement but there is no indication that you've attempted what has been suggested.

I now see its taken straight from the Excel file but that seems to add nothing of any consequence as far as I could see.

Am I correct in assuming you don't really understand this.

Click to expand...

Hi Colin,
you are right that I am a kind of messed up.
I use the code:
IIf([LOEVM]="X" Or [SPERR]="X" Or [SPERM]="X" Or [SPERQ]="PA" Or [SPERQ]="PD" Or [SPERQ]="PE" Or [SPERQ]="QF" Or [SPERQ]="QG" Or [SPERQ]="QH",1,0)

works great however the value properties is an issue now. you suggested to use lookup but it seems that i dont know how is that?


Appreciate your help.

 

June7

AWF VIP

Local time

Today, 00:54

Joined

Mar 9, 2014

Messages

5,470

• Jul 22, 2018

• #6

IIf(LOEVM = "X" Or SPERR = "X" Or SPERM="X" Or SPERQ IN("PA","PD","PE","QF","QG","QH"), 1, 0)

Build a table that shows the association between letter codes and description. Include table in query.