IIF function with *

  • Thread starter Thread starter KarenY
  • Start date Start date
K

KarenY

Hi, I've checked many IIF functions from the Discussin Group but I couldn't
find one similar to what I am looking for....

I have 3 columns: INVCNO, SALESACCT, NetValue

Invoice number's prefixes are S and I.
Credit memo prefix is C.
All I, S and C are in the INVCNO column.

The query/table has put all the NetValue as positive figures.
I want to change the value into minus value for the credit memo in the
INVCNO.
I can separate into 2 tables, but I think there must be a way to change the
value into negative for the credit memo's.
I've tried the following expressions for the IIF function but not all of the
values were changed properly ??? I don't have a definite number for the
invoice/credit memo number, so I've used *, probably * is not working for IIF.

Please help :

Value: CCur(IIf([INVCNO]="S*" And [INVCNO]="I*",[NetValue],-[NetValue]))

Neither does this work :

Value: Value: CCur(IIf([INVCNO]="C*",-[NetValue],[NetValue]))

I am using Access 2003.
thanks
karen
 
hi Karen,
I can separate into 2 tables, but I think there must be a way to change the
value into negative for the credit memo's.
I've tried the following expressions for the IIF function but not all of the
values were changed properly ??? I don't have a definite number for the
invoice/credit memo number, so I've used *, probably * is not working for IIF.
Click to expand...
The * wildcard is only vaild when using the LIKE operator, e.g.

[INVCNO] LIKE "S*"
Please help :

Value: CCur(IIf([INVCNO]="S*" And [INVCNO]="I*",[NetValue],-[NetValue]))
Click to expand...
You have here a contradiction. Use OR instead of AND.
Value: Value: CCur(IIf([INVCNO]="C*",-[NetValue],[NetValue]))
Click to expand...
btw, you only need the CCur() cast, if [NetValue] is not a currency.


mfG
--> stefan <--
 
hi Karen,
I can separate into 2 tables, but I think there must be a way to change the
value into negative for the credit memo's.
I've tried the following expressions for the IIF function but not all of the
values were changed properly ??? I don't have a definite number for the
invoice/credit memo number, so I've used *, probably * is not working for IIF.
Click to expand...
The * wildcard is only vaild when using the LIKE operator, e.g.

[INVCNO] LIKE "S*"
Please help :

Value: CCur(IIf([INVCNO]="S*" And [INVCNO]="I*",[NetValue],-[NetValue]))
Click to expand...
You have here a contradiction. Use OR instead of AND.
Value: Value: CCur(IIf([INVCNO]="C*",-[NetValue],[NetValue]))
Click to expand...
btw, you only need the CCur() cast, if [NetValue] is not a currency.

mfG
--> stefan <--
Click to expand...

Additionally, Value is a reserved Access/VBA/Jet word and should not
be used as a field name (column heading).
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
 
hi Fred, Karen,
Value: Value: CCur(IIf([INVCNO]="C*",-[NetValue],[NetValue]))
Click to expand...
btw, you only need the CCur() cast, if [NetValue] is not a currency.
Click to expand...

Additionally, Value is a reserved Access/VBA/Jet word and should not
be used as a field name (column heading).
Click to expand...
It is a compound using a reserved word. I would not consider it as that
evil because of the use of a resverved, but I would consider using a
more meaningful - by the means of semantics - name.


mfG
--> stefan <--
 
KarenY <[email protected]>

(...)
| Please help :
|
| Value: CCur(IIf([INVCNO]="S*" And
[INVCNO]="I*",[NetValue],-[NetValue]))

CCur(IIf(Left([INVCNO],1)="S" OR
Left([INVCNO],1)="I",[NetValue],-[NetValue]))


| Neither does this work :
|
| Value: Value: CCur(IIf([INVCNO]="C*",-[NetValue],[NetValue]))

CCur(IIf(Left([INVCNO],1)="C",-[NetValue],[NetValue]))
 
I've tried the following expressions for the IIF function but not all of the
values were changed properly ??? I don't have a definite number for the
invoice/credit memo number, so I've used *, probably * is not working for IIF.

Please help :

Value: CCur(IIf([INVCNO]="S*" And [INVCNO]="I*",[NetValue],-[NetValue]))
Click to expand...

* will be treated as a wildcard ONLY if you use the Like operator! The =
operator you're using will treat it as a literal character; in this case, it
will select only those records where INVCNO consists - at one and the same
time - of the literal text string "S*" and ALSO consists of the literal text
string "I*" (that is, never).

In this case, use the Left() function to extract the first character, and use
OR (to see if either one of the conditions is true):

CCur(IIF(Left([INVCNO], 1) = "S" OR Left([INVCNO], 1) = "I", [NetValue],
-[NetValue])
 
Thank you to all of you guys helping me out and learned more about * and
terms !
Sorry to come back late due to offline !

karen


John W. Vinson said:
I've tried the following expressions for the IIF function but not all of the
values were changed properly ??? I don't have a definite number for the
invoice/credit memo number, so I've used *, probably * is not working for IIF.

Please help :

Value: CCur(IIf([INVCNO]="S*" And [INVCNO]="I*",[NetValue],-[NetValue]))
Click to expand...

* will be treated as a wildcard ONLY if you use the Like operator! The =
operator you're using will treat it as a literal character; in this case, it
will select only those records where INVCNO consists - at one and the same
time - of the literal text string "S*" and ALSO consists of the literal text
string "I*" (that is, never).

In this case, use the Left() function to extract the first character, and use
OR (to see if either one of the conditions is true):

CCur(IIF(Left([INVCNO], 1) = "S" OR Left([INVCNO], 1) = "I", [NetValue],
-[NetValue])
Click to expand...
 
Back
Top