condition in iff statement

[aarellano]

I have some part numbers that are someting like this

0001-10
0015-25
0036-50

I am trying to create an iff statement, the number after the dash "-"is the number of pieces of that part. I need to multiply that by the price but not sure how to go about putting that into an iff statement

I tried this

iif(partnum like 50, 50*price)as Total_Price

but that did not work

any suggestions?

 

[SgtJarrow]

First, this assumes that the "part number" portion you want after the dash is always two digits. Second, an IIF statement has three parts: condition, answer if true, answer if false. So your IFF should look something like:

IIF(CInt(Right(partnum, 2)) = 50, 50*price, 0) as Total_Price

Finally, do you want to use the number after the dash as the multiple?? If so, try this instead:

CInt(Right(partnum, 2)) * price As Total_Price

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[aarellano]

mstrmanage1768, I tried that and I get #Error not sure what might be causing that

 

[SgtJarrow]

Can you post the SQl for your query???? Open your query in SQL View and copy/paste it here.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[aarellano]

SELECT AMFLIB_PSTRUC.PINBR, AMFLIB_PSTRUC.CINBR, AMFLIB_ROUTNG.OPSEQ, AMFLIB_ROUTNG.RUNLB, AMFLIB_ROUTNG.TBCOD, IIF(CInt(Right(cinbr, 2)) = 50, 50*runlb, 0) as Total_Price
FROM AMFLIB_PSTRUC INNER JOIN AMFLIB_ROUTNG ON AMFLIB_PSTRUC.CINBR = AMFLIB_ROUTNG.ITNBR
WHERE (((AMFLIB_PSTRUC.PINBR)="7279"));

 

[SgtJarrow]

Is CINBR a string or a number??? And what about runlb?? Is it a string or numeric value?? I am assuming a string value for CINBR so replace:

IIF(CInt(Right(cinbr, 2)) = 50, 50*runlb, 0)

with

IIF(Right(cinbr, 2) = "50", 50*runlb, 0)

If runlb is also a string, try this instead:

IIF(Right(cinbr, 2) = "50", 50 * CInt(runlb), 0)

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[aarellano]

the CINBR is an alpha field I tried

'50'
"50"
and both instances I get 0 so I go back to the
50 and I get and #Error