iIF statements in a query

T

T Miller

OK, I have some sample data, not real good with IF statements in Access, Need
to know if it can be done with the reult I need and how the iIF statement
looks. If this can be done, thank you in advance for the help.

PackagingString Result
EA 1
CS/12 EA 12
CS/20 PK/4 EA 80
CS/20 PK/40 EA 800
CS/4 EA 4
CS/100 EA 100
BX/1000 EA 1000
KT/1 PK/100 EA 100
KT/1 BX/2 ST/1 EA 2
KT/10 BX/20 ST/10 EA 200
KT/100 BX/200 ST/1000 EA 200000
CS/10 BX/100 EA 1000
CS/10 PK/1000 EA 10000
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
 
T

T Miller

How do I create an iIF statement in a query using the data above? I need to
get the result.
 
J

Jeff Boyce

Thomas

Maybe it is only my email reader software, but I couldn't make out the
Input/Result pairs that would be an example.

What have you tried already?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

Tom Lake

T Miller said:
OK, I have some sample data, not real good with IF statements in Access,
Need
to know if it can be done with the reult I need and how the iIF statement
looks. If this can be done, thank you in advance for the help.
Click to expand...

I wouldn't use an IIf for all those choices. I make a Packaging table
and do a DLookup into it

DLookup("[Result]", "Packaging", "[Packaging String] = '" &
[yoursearchfield] & "'")

Tom Lake
 
K

Klatuu

That would be one ugly nested IIf statement. May I suggest an alternative
using a fuction:
Put the functio below in a standard module. Then call it in your query
using a Calculated field:

PkQty: ShowQty([FieldName])

Public Function ShowQty(strPkg) as Long

Select Case
Case strpkg = "EA"
ShowQty = 1
Case strpkg = "CS/12 EA"
ShowQty = 12
Case strpkg = "CS/20 PK/4 EA"
ShowQty = 80
Case strpkg = "CS/20 PK/40 EA"
ShowQty = 800
Case strpkg = "CS/4 EA"
ShowQty = 4
Case strpkg = "CS/100 EA"
ShowQty = 100
Case strpkg = "BX/1000 EA"
ShowQty = 1000
Case strpkg = "KT/1 PK/100 EA"
ShowQty = 100
Case strpkg = "KT/1 BX/2 ST/1 EA"
ShowQty = 2
Case strpkg = "KT/10 BX/20 ST/10 EA"
ShowQty = 200
Case strpkg = "KT/100 BX/200 ST/1000 EA"
ShowQty = 200000
Case strpkg = "CS/10 BX/100 EA"
ShowQty = 1000
Case strpkg = "CS/10 PK/1000 EA"
ShowQty = 10000
Case strpkg = "CS/100 PK/1000 EA"
ShowQty = 100000
Case strpkg = "CS/1000 PK/1000 EA"
ShowQty = 1000000
Case Else
ShowQty = 0
End Select
End Function
 
T

T Miller

Thank you, I will try this.
--
Thomas


Tom Lake said:
T Miller said:
OK, I have some sample data, not real good with IF statements in Access,
Need
to know if it can be done with the reult I need and how the iIF statement
looks. If this can be done, thank you in advance for the help.
Click to expand...

I wouldn't use an IIf for all those choices. I make a Packaging table
and do a DLookup into it

DLookup("[Result]", "Packaging", "[Packaging String] = '" &
[yoursearchfield] & "'")

Tom Lake
PackagingString Result
EA 1
CS/12 EA 12
CS/20 PK/4 EA 80
CS/20 PK/40 EA 800
CS/4 EA 4
CS/100 EA 100
BX/1000 EA 1000
KT/1 PK/100 EA 100
KT/1 BX/2 ST/1 EA 2
KT/10 BX/20 ST/10 EA 200
KT/100 BX/200 ST/1000 EA 200000
CS/10 BX/100 EA 1000
CS/10 PK/1000 EA 10000
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
 
T

T Miller

Yes, I figured as much, it is not as easy as excel, I will try this way also
and see which will work for me. Thanks again.
--
Thomas


Klatuu said:
That would be one ugly nested IIf statement. May I suggest an alternative
using a fuction:
Put the functio below in a standard module. Then call it in your query
using a Calculated field:

PkQty: ShowQty([FieldName])

Public Function ShowQty(strPkg) as Long

Select Case
Case strpkg = "EA"
ShowQty = 1
Case strpkg = "CS/12 EA"
ShowQty = 12
Case strpkg = "CS/20 PK/4 EA"
ShowQty = 80
Case strpkg = "CS/20 PK/40 EA"
ShowQty = 800
Case strpkg = "CS/4 EA"
ShowQty = 4
Case strpkg = "CS/100 EA"
ShowQty = 100
Case strpkg = "BX/1000 EA"
ShowQty = 1000
Case strpkg = "KT/1 PK/100 EA"
ShowQty = 100
Case strpkg = "KT/1 BX/2 ST/1 EA"
ShowQty = 2
Case strpkg = "KT/10 BX/20 ST/10 EA"
ShowQty = 200
Case strpkg = "KT/100 BX/200 ST/1000 EA"
ShowQty = 200000
Case strpkg = "CS/10 BX/100 EA"
ShowQty = 1000
Case strpkg = "CS/10 PK/1000 EA"
ShowQty = 10000
Case strpkg = "CS/100 PK/1000 EA"
ShowQty = 100000
Case strpkg = "CS/1000 PK/1000 EA"
ShowQty = 1000000
Case Else
ShowQty = 0
End Select
End Function




T Miller said:
OK, I have some sample data, not real good with IF statements in Access, Need
to know if it can be done with the reult I need and how the iIF statement
looks. If this can be done, thank you in advance for the help.

PackagingString Result
EA 1
CS/12 EA 12
CS/20 PK/4 EA 80
CS/20 PK/40 EA 800
CS/4 EA 4
CS/100 EA 100
BX/1000 EA 1000
KT/1 PK/100 EA 100
KT/1 BX/2 ST/1 EA 2
KT/10 BX/20 ST/10 EA 200
KT/100 BX/200 ST/1000 EA 200000
CS/10 BX/100 EA 1000
CS/10 PK/1000 EA 10000
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
 
T

T Miller

Duane,

Sorry, I did not respond, Honesty I could not remember if I asked the
question or not. So yes, creating the function is what I need help with.
Would you mind helping me out with this? Or, is it something that can not be
done on a message board more in person?
 
D

Duane Hookom

From your database window, select Modules. Then click New. This will open
the VBA editor. Copy and paste the function from "Function Get..." through
"End Function" into the window. Then click the save icon to save your
module with the name "modCalcs". Then press Ctrl+G to open the
immediate/debug window. You can test your function by typing in:

+----------------
|? GetQty("KT/100 BX/200 ST/1000")
|
Pressing enter on the above line should return a result of 20000000.

You can then use this function in queries, code, control sources,... like
GetQty([PackagingString])
where PackagingString is the name of your field.

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
 
T

T Miller

Duane,

So when this is saved, then I can use it any time? Just change the "field
name". Where is this done in the query? I am unsure about that part?
--
Thomas


Duane Hookom said:
From your database window, select Modules. Then click New. This will open
the VBA editor. Copy and paste the function from "Function Get..." through
"End Function" into the window. Then click the save icon to save your
module with the name "modCalcs". Then press Ctrl+G to open the
immediate/debug window. You can test your function by typing in:

+----------------
|? GetQty("KT/100 BX/200 ST/1000")
|
Pressing enter on the above line should return a result of 20000000.

You can then use this function in queries, code, control sources,... like
GetQty([PackagingString])
where PackagingString is the name of your field.

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

--
Duane Hookom
MS Access MVP
Click to expand...
 
D

Duane Hookom

You can use this function almost anywhere that you would use any other
function. For instance if you needed the quantity in a query where you have
a field named "PackagingString" then you would create a column like:

PackedQty: GetQty([PackagingString])


--
Duane Hookom
MS Access MVP

T Miller said:
Duane,

So when this is saved, then I can use it any time? Just change the "field
name". Where is this done in the query? I am unsure about that part?
--
Thomas


Duane Hookom said:
From your database window, select Modules. Then click New. This will open
the VBA editor. Copy and paste the function from "Function Get..."
through
"End Function" into the window. Then click the save icon to save your
module with the name "modCalcs". Then press Ctrl+G to open the
immediate/debug window. You can test your function by typing in:

+----------------
|? GetQty("KT/100 BX/200 ST/1000")
|
Pressing enter on the above line should return a result of 20000000.

You can then use this function in queries, code, control sources,... like
GetQty([PackagingString])
where PackagingString is the name of your field.

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

--
Duane Hookom
MS Access MVP




T Miller said:
Duane,

Sorry, I did not respond, Honesty I could not remember if I asked the
question or not. So yes, creating the function is what I need help
with.
Would you mind helping me out with this? Or, is it something that can
not
be
done on a message board more in person?
--
Thomas


:

I answered this same question for you Oct 24th
http://groups.google.com/group/micr...f1da9?lnk=st&q=&rnum=1&hl=en#0d58ceb0704f1da9

You didn't reply. Do you need assistance with creating a function?

--
Duane Hookom
MS Access MVP

OK, I have some sample data, not real good with IF statements in
Access,
Need
to know if it can be done with the reult I need and how the iIF
statement
looks. If this can be done, thank you in advance for the help.

PackagingString Result
EA 1
CS/12 EA 12
CS/20 PK/4 EA 80
CS/20 PK/40 EA 800
CS/4 EA 4
CS/100 EA 100
BX/1000 EA 1000
KT/1 PK/100 EA 100
KT/1 BX/2 ST/1 EA 2
KT/10 BX/20 ST/10 EA 200
KT/100 BX/200 ST/1000 EA 200000
CS/10 BX/100 EA 1000
CS/10 PK/1000 EA 10000
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
Click to expand...
 
B

bluefalcon904

HI ... Can you also help? Im such a newbie in Ms Access and I need desperate
help.

Can you please help me translate below SQL statement into an IIF statement?

Select
A.SecIDFlag, A.SecID as MasterSecID,
case when A.SecIDFlag = 1 then
(select top 1 SecID from SecRefCode where RefCodeType='12' and
RefCode=A.Secid)
when A.SecIDFlag = 8 then
(select top 1 SecID from SecRefCode where RefCodeType='06' and
RefCode=A.Secid)
when A.SecIDFlag = 30 then
(select top 1 SecID from SecRefCode where RefCodeType='08' and
RefCode=A.Secid)
else null
end as NewSecID
from SecMaster A

Im not so familar on how to use the functions/module in Ms Access.

Hope you can help.
Thanks

That would be one ugly nested IIf statement. May I suggest an alternative
using a fuction:
Put the functio below in a standard module. Then call it in your query
using a Calculated field:

PkQty: ShowQty([FieldName])

Public Function ShowQty(strPkg) as Long

Select Case
Case strpkg = "EA"
ShowQty = 1
Case strpkg = "CS/12 EA"
ShowQty = 12
Case strpkg = "CS/20 PK/4 EA"
ShowQty = 80
Case strpkg = "CS/20 PK/40 EA"
ShowQty = 800
Case strpkg = "CS/4 EA"
ShowQty = 4
Case strpkg = "CS/100 EA"
ShowQty = 100
Case strpkg = "BX/1000 EA"
ShowQty = 1000
Case strpkg = "KT/1 PK/100 EA"
ShowQty = 100
Case strpkg = "KT/1 BX/2 ST/1 EA"
ShowQty = 2
Case strpkg = "KT/10 BX/20 ST/10 EA"
ShowQty = 200
Case strpkg = "KT/100 BX/200 ST/1000 EA"
ShowQty = 200000
Case strpkg = "CS/10 BX/100 EA"
ShowQty = 1000
Case strpkg = "CS/10 PK/1000 EA"
ShowQty = 10000
Case strpkg = "CS/100 PK/1000 EA"
ShowQty = 100000
Case strpkg = "CS/1000 PK/1000 EA"
ShowQty = 1000000
Case Else
ShowQty = 0
End Select
End Function
OK, I have some sample data, not real good with IF statements in Access, Need
to know if it can be done with the reult I need and how the iIF statement
Click to expand...
[quoted text clipped - 16 lines]
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
 
J

Jeff Boyce

You'll find you get a lot more eyes on your question if you post it as a new
thread, rather than jumping in on an existing thread.

Regards

Jeff Boyce
Microsoft Office/Access MVP

bluefalcon904 said:
HI ... Can you also help? Im such a newbie in Ms Access and I need
desperate
help.

Can you please help me translate below SQL statement into an IIF
statement?

Select
A.SecIDFlag, A.SecID as MasterSecID,
case when A.SecIDFlag = 1 then
(select top 1 SecID from SecRefCode where RefCodeType='12' and
RefCode=A.Secid)
when A.SecIDFlag = 8 then
(select top 1 SecID from SecRefCode where RefCodeType='06' and
RefCode=A.Secid)
when A.SecIDFlag = 30 then
(select top 1 SecID from SecRefCode where RefCodeType='08' and
RefCode=A.Secid)
else null
end as NewSecID
from SecMaster A

Im not so familar on how to use the functions/module in Ms Access.

Hope you can help.
Thanks

That would be one ugly nested IIf statement. May I suggest an alternative
using a fuction:
Put the functio below in a standard module. Then call it in your query
using a Calculated field:

PkQty: ShowQty([FieldName])

Public Function ShowQty(strPkg) as Long

Select Case
Case strpkg = "EA"
ShowQty = 1
Case strpkg = "CS/12 EA"
ShowQty = 12
Case strpkg = "CS/20 PK/4 EA"
ShowQty = 80
Case strpkg = "CS/20 PK/40 EA"
ShowQty = 800
Case strpkg = "CS/4 EA"
ShowQty = 4
Case strpkg = "CS/100 EA"
ShowQty = 100
Case strpkg = "BX/1000 EA"
ShowQty = 1000
Case strpkg = "KT/1 PK/100 EA"
ShowQty = 100
Case strpkg = "KT/1 BX/2 ST/1 EA"
ShowQty = 2
Case strpkg = "KT/10 BX/20 ST/10 EA"
ShowQty = 200
Case strpkg = "KT/100 BX/200 ST/1000 EA"
ShowQty = 200000
Case strpkg = "CS/10 BX/100 EA"
ShowQty = 1000
Case strpkg = "CS/10 PK/1000 EA"
ShowQty = 10000
Case strpkg = "CS/100 PK/1000 EA"
ShowQty = 100000
Case strpkg = "CS/1000 PK/1000 EA"
ShowQty = 1000000
Case Else
ShowQty = 0
End Select
End Function
OK, I have some sample data, not real good with IF statements in Access,
Need
to know if it can be done with the reult I need and how the iIF
statement
Click to expand...
[quoted text clipped - 16 lines]
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
Click to expand...
 
T

T Miller

Duane,

I just wanted to follow up and say THANK YOU, very much. That worked great.
--
Thomas


Duane Hookom said:
You can use this function almost anywhere that you would use any other
function. For instance if you needed the quantity in a query where you have
a field named "PackagingString" then you would create a column like:

PackedQty: GetQty([PackagingString])


--
Duane Hookom
MS Access MVP

T Miller said:
Duane,

So when this is saved, then I can use it any time? Just change the "field
name". Where is this done in the query? I am unsure about that part?
--
Thomas


Duane Hookom said:
From your database window, select Modules. Then click New. This will open
the VBA editor. Copy and paste the function from "Function Get..."
through
"End Function" into the window. Then click the save icon to save your
module with the name "modCalcs". Then press Ctrl+G to open the
immediate/debug window. You can test your function by typing in:

+----------------
|? GetQty("KT/100 BX/200 ST/1000")
|
Pressing enter on the above line should return a result of 20000000.

You can then use this function in queries, code, control sources,... like
GetQty([PackagingString])
where PackagingString is the name of your field.

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

--
Duane Hookom
MS Access MVP




Duane,

Sorry, I did not respond, Honesty I could not remember if I asked the
question or not. So yes, creating the function is what I need help
with.
Would you mind helping me out with this? Or, is it something that can
not
be
done on a message board more in person?
--
Thomas


:

I answered this same question for you Oct 24th
http://groups.google.com/group/micr...f1da9?lnk=st&q=&rnum=1&hl=en#0d58ceb0704f1da9

You didn't reply. Do you need assistance with creating a function?

--
Duane Hookom
MS Access MVP

OK, I have some sample data, not real good with IF statements in
Access,
Need
to know if it can be done with the reult I need and how the iIF
statement
looks. If this can be done, thank you in advance for the help.

PackagingString Result
EA 1
CS/12 EA 12
CS/20 PK/4 EA 80
CS/20 PK/40 EA 800
CS/4 EA 4
CS/100 EA 100
BX/1000 EA 1000
KT/1 PK/100 EA 100
KT/1 BX/2 ST/1 EA 2
KT/10 BX/20 ST/10 EA 200
KT/100 BX/200 ST/1000 EA 200000
CS/10 BX/100 EA 1000
CS/10 PK/1000 EA 10000
CS/100 PK/1000 EA 100000
CS/1000 PK/1000 EA 1000000
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

iIF statement in a query 3
seperate a value in a text string 10
sum in a report - please help 0
Using a text box with multiple values and inserting in a query 0
Date range and creating a formula to use in a second date range 0
Query total amount 1
Top n query tie problem... 3
Summing like data 5

Top