iIF statement in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
Please provide your data again however include the final result expected
like:
EA (1)
CS/12 EA (12)
BX/4 PK/50 EA (200)
 
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)
 
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
 
Back
Top