SWITCH Command Between function???

[travismp]

In my query I have

Exp1: Switch([Kits]=1,"1Box",[Kits]=2,"10Box",[Kits]=3,"10Box",[Kits]=4,"10Box",[Kits]=5,"10Box")

I have an orders table where I list how many KITS each customer orders. Can be any number from 1 to 100. Well I use one of 4 boxes to send the orders and I am trying to get a better use of the amount of boxes I am sending based on the number of kits per order. I know if they order 15 kits I use my 24Box. With the switch command can I do a BETWEEN type of function?

I want to say:

Exp1: Switch([Kits]=1,"1Box",[Kits]>2<11,"10Box",[Kits]>12<25,"25Box",[Kits]>26<49,"50Box")

Can this be done? Thanks.

 

[boblarson]

With that many, why not just create a function which you can then call from the query?

 

[travismp]

because I don't know how. I am happy to learn learn if that is the best way to do it.

Where would I find a sample code of a similar situation?

 

[boblarson]

You can create a function like this (in a standard module, not form module):

Public Function WhatKit(intKitCode As Integer) As String
   Select Case intKitCode
       Case  1 
          WhatKit = "1Box"
       Case 2 To 11
           WhatKit = "10Box"
       Case 12 To 25
           WhatKit = "25Box"
       Case 26 To 49
           WhatKit = "50Box"
       Case Else
           WhatKit = "Undefined"
    End Select
End Function

And then you just call the function in the query by something like:

MyNewFieldName: WhatKit([Kits])

 

[travismp]

OK great I will give that a go and see if I can make it work. THANK YOU BOB!!!

 

[travismp]

I have everthing working great via the sample provided. Now when I try to filter anything I get "Data type mismatch in criteria expression" is there any fix to that? Did I do anything wrong?

 

[boblarson]

I have everthing working great via the sample provided. Now when I try to filter anything I get "Data type mismatch in criteria expression" is there any fix to that? Did I do anything wrong?

What is your SQL string for the query?

 

[travismp]

This is what I have...

SELECT orders.DateOrdered, orders.[Kits], WhatKit([Kits]) AS KITS2
FROM orders
ORDER BY orders.DateOrdered DESC;

~~~

Public Function WhatKit(intKitCode As Integer) As String
Select Case intKitCode
Case 1
WhatKit = "1 Count Box"
Case 2 To 10
WhatKit = "6 Count Box"
Case 11 To 25
WhatKit = "25 Count Box"
Case 26 To 50
WhatKit = "50 Count Box"
Case Else
WhatKit = "Undefined"
End Select
End Function

 

[boblarson]

Make sure that [Kits] is an Integer. If it is a Long Integer, change the part in the VBA code of:
intKitCode As Integer

to

lngKitCode As Long

and change the Select Case intKitCode to

Select Case lngKitCode

 

[travismp]

it is a Long Integer so I made the recommended changes and I still get the same error when I try to filter the results. I can filter the KIT column but not the KIT2 column that has the new data from the module.

 

[boblarson]

it is a Long Integer so I made the recommended changes and I still get the same error when I try to filter the results. I can filter the KIT column but not the KIT2 column that has the new data from the module.

What are you using for criteria in the KIT2 column?

 

[travismp]

WhatKit([Kits]) AS KITS2.

Sorry KITS2 not KIT2. My entire SQL code is listed above. That is exactly how it looks.

 

[boblarson]

But I don't see any criteria. What are you trying to "FILTER" as you mentioned?

 

[travismp]

If I execute the query as it it looks great. Shows all my orders, my original column, and then the new column feeding data from the module. All information looks good including the module information provided above.

As soon as I try to highlight a field and filter the "50 Count" boxes to get a rough idea of how many 50 counts I filles last month I get the error.

 

[boblarson]

I know this can be a pain, but try this out. Try creating a new query from the query that has the function in it. Then filter on that field in the NEW query.

 

[travismp]

OK, just tried it. I named this query Z for test reasons. Created a new query ZZ with only the KITS2 column. Executed, everything looks great, right click one column and chose filter. Same error. "Data type mismatch in criteria expression"

 

[boblarson]

OK, just tried it. I named this query Z for test reasons. Created a new query ZZ with only the KITS2 column. Executed, everything looks great, right click one column and chose filter. Same error. "Data type mismatch in criteria expression"

Any chance on getting the database uploaded so I can look?

 

[travismp]

I can easily make a sample. However I have to pick up my dogs in 10 minutes. I can build a sample tonight and upload first thing in the morning when I return.

 

[raskew]

Hi -

Here is a working/tested example based on the [Quantity] field of Northwind's OrderDetails table. [Quantity] is a number (Integer) field.

SELECT
    OrderDetails.OrderID
  , OrderDetails.ProductID
  , OrderDetails.Quantity
  , Switch([Quantity]=1,"1 x 1",[Quantity]<=10,
"1 x 10",[Quantity]<=25,"1 x 25",[Quantity]<=50,
"1 x 50",[Quantity]=51,"1 x 50; 1 x 1",[Quantity]<=60,
"1 x 50; 1 x 10",[Quantity]<=75,"1 x 50;1 x 25",[Quantity]<=100
,"2 x 50",True,"Oops") AS Boxes
FROM
   OrderDetails
ORDER BY
   OrderDetails.Quantity;

You might copy/paste it to a new query then, in SQL view, replace table/field names with your own. Ensure the substitute for [Quantity] is a number field.

HTH - Bob

 

[travismp]

That last code worked PERFECT! Thank you very much.