iIF statement in a query

[T Miller]

I do not know how to set up an iIF statement in a query. I have sample data
and I know what the outcome should be....

Sample Data:

EA
CS/12 EA
BX/4 PK/50 EA
BX/5 RL
PK/3 EA
CS/12 EA
BX/100 EA
BX/1 RL/2800 EA
CS/20 BX/250 EA
CT/10 PK/25 EA
PD/100 EA
CT/10 PK/100 EA

This is in a row and I need to have the (EA,CS,PK) to have a one and the
ones with BX/4 RL to have the 4 and the ones with two numbers to be
multiplied together (CT/10 PK/100 EA) = 1000. I have done this in excel but
I am not sure how to make it happen in access. The file is to big to put
into excel.

Any and all help would be appreciated.

Thanks,

 

[Duane Hookom]

Please provide your data again however include the final result expected
like:
EA (1)
CS/12 EA (12)
BX/4 PK/50 EA (200)

 

[T Miller]

EA (1)
CS/12 EA (12)
BX/4 PK/50 EA (200)
BX/5 RL (5)
PK/3 EA (3)
CS/12 EA (12)
BX/100 EA (100)
BX/1 RL/2800 EA (2800)
CS/20 BX/250 EA (5000)
CT/10 PK/25 EA (250)
PD/100 EA (100)
CT/10 PK/100 EA (1000)

 

[Duane Hookom]

You can create a function in a standard module with code as below. Make sure
you save the module with a name like "modStringFunctions". You can then use
this function in places where you would use other functions.

Function GetQty(pstrPkg As String) As Long
Dim lngOut As Long
lngOut = 1
Dim intLen As Integer
Dim intChar As Integer 'which character to examine
Dim intNum As Integer 'found number in string
intLen = Len(pstrPkg)
For intChar = 1 To intLen
If IsNumeric(Mid(pstrPkg, intChar, 1)) Then
'get the value of the found number
intNum = Val(Mid(pstrPkg, intChar))
'multiply the values
lngOut = lngOut * intNum
'skip characters to a non-numeric
intChar = intChar + Len(Trim(Str(intNum)))
End If
Next
GetQty = lngOut
End Function