Limit to Expression Size in Query ?

D

dhstein

I have a very complex expression that I need to code in a query. It uses 6
fields from the table to calculate a value that I will display for now.
Eventually that value will be compared to another field as a criteria.

1) Is there a limit to the size of the query?

2) I wrote a VB function that provides the result I need - Is there a way to
use that function in the query?

3) Is there some other approach that might work for this?

Thanks for any help you can provide
 
P

pietlinden

I have a very complex expression that I need to code in a query.  It uses 6
fields from the table to calculate a value that I will display for now.  
Eventually that value will be compared to another field as a criteria.

1)  Is there a limit to the size of the query?

2) I wrote a VB function that provides the result I need - Is there a wayto
use that function in the query?

3)  Is there some other approach that might work for this?

Thanks for any help you can provide
Click to expand...

base a second query on the first one that does this calculation.
 
K

Ken Sheridan

A function would be the obvious solution. Pass the values of the six columns
into the function as its arguments and return the result of the computation a
the function's return value. In query design view you'd put something like
this in the 'field' row of a blank column in the design grid:

MyComputedColumn:
MyFunction([Field1],[Field2],[Field3],[Field4],[Field5],[Field6])

substituting the real field names for Field1 etc. If its as complex as you
say performance may not be good, although the number of rows being processed
will have a significant bearing on this.

Ken Sheridan
Stafford, England
 
J

John W. Vinson

I have a very complex expression that I need to code in a query. It uses 6
fields from the table to calculate a value that I will display for now.
Eventually that value will be compared to another field as a criteria.

1) Is there a limit to the size of the query?
Click to expand...

Yes: 64KBytes compiled. There are also limits on the size of expressions in a
field.
2) I wrote a VB function that provides the result I need - Is there a way to
use that function in the query?
Click to expand...

Sure, just call it as a calculated field.
3) Is there some other approach that might work for this?
Click to expand...

If you've got the function, bring it into VBA and use it directly.
 
D

dhstein

John W. Vinson said:
Yes: 64KBytes compiled. There are also limits on the size of expressions in a
field.


Sure, just call it as a calculated field.


If you've got the function, bring it into VBA and use it directly.
Click to expand...

Thanks for your responses. Although my function works when I call it from
a VBA module, it can't run from the query ( I don't even get the "Run"
exclamation point. Any ideas?
 
J

John W. Vinson

Thanks for your responses. Although my function works when I call it from
a VBA module, it can't run from the query ( I don't even get the "Run"
exclamation point. Any ideas?
Click to expand...

Please post the SQL of the query.
 
D

dhstein

John W. Vinson said:
Please post the SQL of the query.
Click to expand...


Here is the SQL of the query that works - with calculations done in the
expression:

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]
AS [Minimum Level],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier] AS [Current Level]
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier],
tblWarehouseLocation.WarehouseLocationQty,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel])), tblProduct.ProductVendor1ID
HAVING
(((([tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier])-(IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]))<0))
ORDER BY tblProduct.ProductVendor1ID;






Below is an attempt to use function in Query - This query is not the same as
above - just trying to use the function in the query field:

SQL:
SELECT tblProduct.ProductSKU, tblProduct.ProductName
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;


Query Field:

My Function:
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])

VBA Function:
Public Function GetStockingLevel(LowBitMap As String, NormalBitMap As
String, HighBitMap As String, LowLevel As String, NormalLevel As String,
HighLevel As String)

' This function returns the stocking level based on the bit map fields.
There are 6 fields all Strings
' Low Stocking Level Bit Map - example 110000000111 - would indicate Jan,
Feb, Oct, Nov, Dec
' Normal Stocking Level Bit Map
' High Stocking Level Bit Map
' Low Stocking Level - level to be used in Low Month
' Normal Stocking Level - level to be used in Normal Month
' High Stocking Level - level to be used in High Month

Dim BitIndex
Dim LowBit
Dim NormalBit
Dim HighBit

BitIndex = CStr(Month(Now()))

LowBit = Mid(LowBitMap, BitIndex, 1)
NormalBit = Mid(NormalBitMap, BitIndex, 1)
'HighBit = Mid(HighBitMap, BitIndex, 1)

GetStockingLevel = IIf(NormalBit = "1", NormalLevel, IIf(LowBit = "1",
LowLevel, HighLevel))

End Function

Thanks
 
J

John W. Vinson

Here is the SQL of the query that works - with calculations done in the
expression:

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]
AS [Minimum Level],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier] AS [Current Level]
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier],
tblWarehouseLocation.WarehouseLocationQty,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel])), tblProduct.ProductVendor1ID
HAVING
(((([tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier])-(IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]))<0))
ORDER BY tblProduct.ProductVendor1ID;
Click to expand...

IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],
IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))
*[tblWarehouseLocation].[WarehouseLocationMultiplier]

Ok... so you're trying to parse out the n'th bit of these bitmapped fields
where n is the month number? I'd use

IIF(Mid([tblProduct].[ProductNormalMonths], Month(Date()) = "1", ...

Now() does not return the date, it returns the date and time to microseconds -
you don't need it; and the second argument to Mid is just an integer, it's not
clear what you're doing with the CStr().

That will shorten your expression considerably.

Secondly... you're not Summing anything and you're not Counting anything -
you're not using ANY aggregate function that I can see! Why the additional
complexity of a Totals query??
Below is an attempt to use function in Query - This query is not the same as
above - just trying to use the function in the query field:

SQL:
SELECT tblProduct.ProductSKU, tblProduct.ProductName
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
Click to expand...

But you're NOT using the function in the query.
Query Field:

My Function:
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
Click to expand...

What happens if you use

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
AS StockingLevel
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
VBA Function:
Public Function GetStockingLevel(LowBitMap As String, NormalBitMap As
String, HighBitMap As String, LowLevel As String, NormalLevel As String,
HighLevel As String)

' This function returns the stocking level based on the bit map fields.
There are 6 fields all Strings
' Low Stocking Level Bit Map - example 110000000111 - would indicate Jan,
Feb, Oct, Nov, Dec
' Normal Stocking Level Bit Map
' High Stocking Level Bit Map
' Low Stocking Level - level to be used in Low Month
' Normal Stocking Level - level to be used in Normal Month
' High Stocking Level - level to be used in High Month

Dim BitIndex
Click to expand...

I'd Dim this as Integer rather than the default Variant
Dim LowBit
Dim NormalBit
Dim HighBit
Click to expand...

I'd Dim these as Strings rather than the default Variant
BitIndex = CStr(Month(Now()))
Click to expand...

Again... you DON'T need the CStr(). That's just forcing Mid to convert the
string back to an integer!

BitIndex = Month(Date)

will work just fine.
LowBit = Mid(LowBitMap, BitIndex, 1)
NormalBit = Mid(NormalBitMap, BitIndex, 1)
'HighBit = Mid(HighBitMap, BitIndex, 1)

GetStockingLevel = IIf(NormalBit = "1", NormalLevel, IIf(LowBit = "1",
LowLevel, HighLevel))
Click to expand...

What if NormalBit and LowBit are BOTH equal to 1? It will return NormalLevel:
is that OK?
 
D

dhstein

John W. Vinson said:
Here is the SQL of the query that works - with calculations done in the
expression:

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]
AS [Minimum Level],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier] AS [Current Level]
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier],
tblWarehouseLocation.WarehouseLocationQty,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel])), tblProduct.ProductVendor1ID
HAVING
(((([tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier])-(IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]))<0))
ORDER BY tblProduct.ProductVendor1ID;
Click to expand...

IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],
IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))
*[tblWarehouseLocation].[WarehouseLocationMultiplier]

Ok... so you're trying to parse out the n'th bit of these bitmapped fields
where n is the month number? I'd use

IIF(Mid([tblProduct].[ProductNormalMonths], Month(Date()) = "1", ...

Now() does not return the date, it returns the date and time to microseconds -
you don't need it; and the second argument to Mid is just an integer, it's not
clear what you're doing with the CStr().

That will shorten your expression considerably.

Secondly... you're not Summing anything and you're not Counting anything -
you're not using ANY aggregate function that I can see! Why the additional
complexity of a Totals query??
Below is an attempt to use function in Query - This query is not the same as
above - just trying to use the function in the query field:

SQL:
SELECT tblProduct.ProductSKU, tblProduct.ProductName
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
Click to expand...

But you're NOT using the function in the query.
Query Field:

My Function:
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
Click to expand...

What happens if you use

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
AS StockingLevel
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
VBA Function:
Public Function GetStockingLevel(LowBitMap As String, NormalBitMap As
String, HighBitMap As String, LowLevel As String, NormalLevel As String,
HighLevel As String)

' This function returns the stocking level based on the bit map fields.
There are 6 fields all Strings
' Low Stocking Level Bit Map - example 110000000111 - would indicate Jan,
Feb, Oct, Nov, Dec
' Normal Stocking Level Bit Map
' High Stocking Level Bit Map
' Low Stocking Level - level to be used in Low Month
' Normal Stocking Level - level to be used in Normal Month
' High Stocking Level - level to be used in High Month

Dim BitIndex
Click to expand...

I'd Dim this as Integer rather than the default Variant
Dim LowBit
Dim NormalBit
Dim HighBit
Click to expand...

I'd Dim these as Strings rather than the default Variant
BitIndex = CStr(Month(Now()))
Click to expand...

Again... you DON'T need the CStr(). That's just forcing Mid to convert the
string back to an integer!

BitIndex = Month(Date)

will work just fine.
LowBit = Mid(LowBitMap, BitIndex, 1)
NormalBit = Mid(NormalBitMap, BitIndex, 1)
'HighBit = Mid(HighBitMap, BitIndex, 1)

GetStockingLevel = IIf(NormalBit = "1", NormalLevel, IIf(LowBit = "1",
LowLevel, HighLevel))
Click to expand...

What if NormalBit and LowBit are BOTH equal to 1? It will return NormalLevel:
is that OK?

End Function
Click to expand...
Click to expand...

Thanks so much for taking the time to look at this. It's early here
(EST) and I just woke up and read your post. You have many great suggestions
and explanations. I'll be able to look at it in more detail later today or
tonight and let you know how it goes. Thanks again.

David
 
D

dhstein

dhstein said:
John W. Vinson said:
Here is the SQL of the query that works - with calculations done in the
expression:

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]
AS [Minimum Level],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier] AS [Current Level]
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier],
tblWarehouseLocation.WarehouseLocationQty,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel])), tblProduct.ProductVendor1ID
HAVING
(((([tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier])-(IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]))<0))
ORDER BY tblProduct.ProductVendor1ID;
Click to expand...

IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],
IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))
*[tblWarehouseLocation].[WarehouseLocationMultiplier]

Ok... so you're trying to parse out the n'th bit of these bitmapped fields
where n is the month number? I'd use

IIF(Mid([tblProduct].[ProductNormalMonths], Month(Date()) = "1", ...

Now() does not return the date, it returns the date and time to microseconds -
you don't need it; and the second argument to Mid is just an integer, it's not
clear what you're doing with the CStr().

That will shorten your expression considerably.

Secondly... you're not Summing anything and you're not Counting anything -
you're not using ANY aggregate function that I can see! Why the additional
complexity of a Totals query??
Below is an attempt to use function in Query - This query is not the same as
above - just trying to use the function in the query field:

SQL:
SELECT tblProduct.ProductSKU, tblProduct.ProductName
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
Click to expand...

But you're NOT using the function in the query.
Query Field:

My Function:
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
Click to expand...

What happens if you use

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
AS StockingLevel
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
VBA Function:
Public Function GetStockingLevel(LowBitMap As String, NormalBitMap As
String, HighBitMap As String, LowLevel As String, NormalLevel As String,
HighLevel As String)

' This function returns the stocking level based on the bit map fields.
There are 6 fields all Strings
' Low Stocking Level Bit Map - example 110000000111 - would indicate Jan,
Feb, Oct, Nov, Dec
' Normal Stocking Level Bit Map
' High Stocking Level Bit Map
' Low Stocking Level - level to be used in Low Month
' Normal Stocking Level - level to be used in Normal Month
' High Stocking Level - level to be used in High Month

Dim BitIndex
Click to expand...

I'd Dim this as Integer rather than the default Variant
Dim LowBit
Dim NormalBit
Dim HighBit
Click to expand...

I'd Dim these as Strings rather than the default Variant
BitIndex = CStr(Month(Now()))
Click to expand...

Again... you DON'T need the CStr(). That's just forcing Mid to convert the
string back to an integer!

BitIndex = Month(Date)

will work just fine.
LowBit = Mid(LowBitMap, BitIndex, 1)
NormalBit = Mid(NormalBitMap, BitIndex, 1)
'HighBit = Mid(HighBitMap, BitIndex, 1)

GetStockingLevel = IIf(NormalBit = "1", NormalLevel, IIf(LowBit = "1",
LowLevel, HighLevel))
Click to expand...

What if NormalBit and LowBit are BOTH equal to 1? It will return NormalLevel:
is that OK?

End Function
Click to expand...
Click to expand...

Thanks so much for taking the time to look at this. It's early here
(EST) and I just woke up and read your post. You have many great suggestions
and explanations. I'll be able to look at it in more detail later today or
tonight and let you know how it goes. Thanks again.

David
John,
Click to expand...

Thanks so much - everything worked great and I learned quite a lot also.
I used your sql statement and got exactly the information I need. Also, you
showed me how to call a function from the "design" screen in the query and I
used that also. My next task is to read a file and update the database
records one at a time. I'm sure I'll be back here with more questions when I
get stuck on that one. Thanks again.

David
 
D

dhstein

dhstein said:
John W. Vinson said:
Here is the SQL of the query that works - with calculations done in the
expression:

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]
AS [Minimum Level],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier] AS [Current Level]
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProduct.ProductSKU, tblProduct.ProductName,
tblWarehouseLocation.WarehouseLocation,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier],
[tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier],
tblWarehouseLocation.WarehouseLocationQty,
IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel])), tblProduct.ProductVendor1ID
HAVING
(((([tblWarehouseLocation].[WarehouseLocationQty]*[tblWarehouseLocation].[WarehouseLocationMultiplier])-(IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))*[tblWarehouseLocation].[WarehouseLocationMultiplier]))<0))
ORDER BY tblProduct.ProductVendor1ID;
Click to expand...

IIf((Mid([tblProduct].[ProductNormalMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductNormalStockingLevel],
IIf((Mid([tblProduct].[ProductLowMonths],(CStr(Month(Now()))),1))="1",[tblProduct].[ProductLowStockingLevel],[tblProduct].[ProductHighStockingLevel]))
*[tblWarehouseLocation].[WarehouseLocationMultiplier]

Ok... so you're trying to parse out the n'th bit of these bitmapped fields
where n is the month number? I'd use

IIF(Mid([tblProduct].[ProductNormalMonths], Month(Date()) = "1", ...

Now() does not return the date, it returns the date and time to microseconds -
you don't need it; and the second argument to Mid is just an integer, it's not
clear what you're doing with the CStr().

That will shorten your expression considerably.

Secondly... you're not Summing anything and you're not Counting anything -
you're not using ANY aggregate function that I can see! Why the additional
complexity of a Totals query??
Below is an attempt to use function in Query - This query is not the same as
above - just trying to use the function in the query field:

SQL:
SELECT tblProduct.ProductSKU, tblProduct.ProductName
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
Click to expand...

But you're NOT using the function in the query.
Query Field:

My Function:
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
Click to expand...

What happens if you use

SELECT tblProduct.ProductSKU, tblProduct.ProductName,
GetStockingLevel([ProductLowMonths],[ProductNormalMonths],[ProductHighMonths],[ProductLowStockingLevel],[ProductNormalStockingLevel],[ProductHighStockingLevel])
AS StockingLevel
FROM tblProduct INNER JOIN tblWarehouseLocation ON tblProduct.ProductSKU =
tblWarehouseLocation.WarehouseLocationSKU;
VBA Function:
Public Function GetStockingLevel(LowBitMap As String, NormalBitMap As
String, HighBitMap As String, LowLevel As String, NormalLevel As String,
HighLevel As String)

' This function returns the stocking level based on the bit map fields.
There are 6 fields all Strings
' Low Stocking Level Bit Map - example 110000000111 - would indicate Jan,
Feb, Oct, Nov, Dec
' Normal Stocking Level Bit Map
' High Stocking Level Bit Map
' Low Stocking Level - level to be used in Low Month
' Normal Stocking Level - level to be used in Normal Month
' High Stocking Level - level to be used in High Month

Dim BitIndex
Click to expand...

I'd Dim this as Integer rather than the default Variant
Dim LowBit
Dim NormalBit
Dim HighBit
Click to expand...

I'd Dim these as Strings rather than the default Variant
BitIndex = CStr(Month(Now()))
Click to expand...

Again... you DON'T need the CStr(). That's just forcing Mid to convert the
string back to an integer!

BitIndex = Month(Date)

will work just fine.
LowBit = Mid(LowBitMap, BitIndex, 1)
NormalBit = Mid(NormalBitMap, BitIndex, 1)
'HighBit = Mid(HighBitMap, BitIndex, 1)

GetStockingLevel = IIf(NormalBit = "1", NormalLevel, IIf(LowBit = "1",
LowLevel, HighLevel))
Click to expand...

What if NormalBit and LowBit are BOTH equal to 1? It will return NormalLevel:
is that OK?

End Function
Click to expand...
Click to expand...

Thanks so much for taking the time to look at this. It's early here
(EST) and I just woke up and read your post. You have many great suggestions
and explanations. I'll be able to look at it in more detail later today or
tonight and let you know how it goes. Thanks again.

David
One more question. Can I use a column in my query as a parameter in another column? For example, the column "Stocking Level" which invokes the "GetStockingLevel" function, can I have another column that does a calculation based on that, so I don't have to run the function again - like "Stocking Level" - "Quantity In Stock" Thanks.
Click to expand...
 
J

John W. Vinson

One more question. Can I use a column in my query as a parameter in another column?
Click to expand...
For example, the column "Stocking Level" which invokes the "GetStockingLevel"
function, can I have another column that does a calculation based on that, so
I don't have to run the function again - like "Stocking Level" - "Quantity In
Stock" Thanks.

Probably not, unfortunately! Just call the GetStockingLevel function again in
the calculation expression. The JET query optimizer is actually smart enough
to only call the function once in some circumstances, and it's actually a
simple enough function that it won't matter if it's called twice.
 

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

Use In() Expression as Query criteria with guts from Function 1
Query Expression Help! 26
Parameter Value error in a Subform 0
Access Query problem 1
Data type mismatch in criteria expression 8
Is it possible to pass an operator from a form to a query limit? 2
Access #Error & Excel Pivot 0
Query to complex? 5

Top