Limit to Expression Size in Query ?

[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

 

[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

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

 

[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

 

[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?

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?

Sure, just call it as a calculated field.

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

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

 

[dhstein]

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.

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?

 

[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?

Please post the SQL of the query.

 

[dhstein]

Please post the SQL of the query.

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

 

[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;

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;

But you're NOT using the function in the query.

Query Field:

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

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

I'd Dim this as Integer rather than the default Variant

Dim LowBit
Dim NormalBit
Dim HighBit

I'd Dim these as Strings rather than the default Variant

BitIndex = CStr(Month(Now()))

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))

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

 

[dhstein]

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;

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;

But you're NOT using the function in the query.

Query Field:

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

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

I'd Dim this as Integer rather than the default Variant

Dim LowBit
Dim NormalBit
Dim HighBit

I'd Dim these as Strings rather than the default Variant

BitIndex = CStr(Month(Now()))

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))

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

End Function

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

 

[dhstein]

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;

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;

But you're NOT using the function in the query.

Query Field:

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

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

I'd Dim this as Integer rather than the default Variant

Dim LowBit
Dim NormalBit
Dim HighBit

I'd Dim these as Strings rather than the default Variant

BitIndex = CStr(Month(Now()))

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))

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

End Function

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,

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

 

[dhstein]

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;

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;

But you're NOT using the function in the query.

Query Field:

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

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

I'd Dim this as Integer rather than the default Variant

Dim LowBit
Dim NormalBit
Dim HighBit

I'd Dim these as Strings rather than the default Variant

BitIndex = CStr(Month(Now()))

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))

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

End Function

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.

 

[John W. Vinson]

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.

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.