Select Case with date range

  • Thread starter Thread starter LouD
  • Start date Start date
L

LouD

Hello,

I'm building a query and the if statement was too long so I had to create a
function with a select case statement. The problem I am having is with the
statement pertaining to a date range. I can get the MN portion to display
correctly in my crosstab query if i remove the D8 portion but I need it
broken down by the year as well.

Public Function MND8(MN, D8)

Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select

Select Case D8
Case Is = Year(#1/1/2005#): MND8 = MND8 & "2005"
Case Is = Year(#1/1/2006#): MND8 = MND8 & "2006"
Case Is = Year(#1/1/2007#): MND8 = MND8 & "2007"
Case Is = Year(#1/1/2008#): MND8 = MND8 & "2008"
Case Is = Year(#1/1/2009#): MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function

I also tried this statement:

Public Function MND8(MN, D8)

Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select

Select Case D8
Case Is >= 1 / 1 / 2005, Is <= 12 / 31 / 2005: MND8 = MND8 & "2005"
Case Is >= 1 / 1 / 2006, Is <= 12 / 31 / 2006: MND8 = MND8 & "2006"
Case Is >= 1 / 1 / 2007, Is <= 12 / 31 / 2007: MND8 = MND8 & "2007"
Case Is >= 1 / 1 / 2008, Is <= 12 / 31 / 2008: MND8 = MND8 & "2008"
Case Is >= 1 / 1 / 2009, Is <= 12 / 31 / 2009: MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function

I'm not sure if that works like the "Between" "And" statement.
 
Can you tell us any more about what you are trying to do?

On the surface this looks like an extremely convoluted way
to concatenate two fields, but maybe I'm missing something.

What is the data type of the two fields involved?
 
Hi Lou,

It seems to me that you should be placing these MN to MND8 conversions
in a table which you then use in your query.

tblMN_2_MND8

MN MND8
47U WS
01U SRV
etc.

As to the year part, why not just use Year(DB)? So your query would then
look something like this:

select MN, DB, MND8 & Year(DB) as MNDB8_YEAR
from tblYour_Table inner join tblMN_2_MND8
on tblMN_2_MND8.MN = tblYour_Table.MN;

No functions, much simpler, easier to add new values (no recoding) and
can have huge numbers of conversions.

Hope that helps,

Clifford Bass
 
Hi Lou,

Oops, forgot the "else" part. Do not include the values that convert
to N/A in the conversion table and try this instead:

select MN, DB, IIf(IsNull(MND8), "N/A", MND8 & Year(DB)) as MNDB8_YEAR
from tblYour_Table left outer join tblMN_2_MND8
on tblMN_2_MND8.MN = tblYour_Table.MN;

Clifford Bass
 
Hi Lou,

Seem to keep hitting that Post button too quick here. If the years
2005 through 2009 and the only "valid" years you can adjust as follows:

IIf(IsNull(MND8) Or Year(DB) < 2005 Or Year(DB) > 2009, "N/A", MND8 &
Year(DB))

Clifford Bass
 
It appears you could do this with a simple concatenation in a query

IIF(Instr(1,"47U UHU W7B 1OU 1AU ZO6 BJU",[MN])>0,"WS", IIF(Instr(1,""05U 11U
15U 31U AC1 F4U DBU",MN)>0,"SRV","N/A")) & D8

A better soultion would be to build a table with the pairs of values for MN
and use that in your query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Sorry for the confusion guys.
It was a very long if statement that would not fit into the query builder. I
will paste in the original if statement so it makes more sense. I just
simplified the model numbers here in this code but this is a fraction of what
the code really is. There are about 30 different models.

TYPEYEAR: IIf([Model Number] Like "1111*" And ([Ship Date] Between
#1/1/2007# And #12/31/2007#),"WS 2007", IIf([Model Number] Like "2222*" And
([Ship Date] Between #1/1/2007# And #12/31/2007#),"SRV 2007",IIf([Model
Number] Like "3333*" And ([Ship Date] Between #1/1/2008# And
#12/31/2008#),"WS 2008",IIf([Model Number] Like "4444*" And ([Ship Date]
Between #1/1/2008# And #12/31/2008#),"SRV 2008",IIf([Model Number] Like
"5555*" And ([Ship Date] Between #1/1/2009# And #12/31/2009#),"WS
2009",IIf([Model Number] Like "6666*" And ([Ship Date] Between #1/1/2009# And
#12/31/2009#),"SRV 2009","N/A"))))))

Obviously there are more fields in the crosstab query than this but this is
the only statement that was too long for the query builder so i had to make a
function. Which is what I originally posted.

The end result is a crosstab query with the months across the top and the
rows with the type and year, SRV 2005, SRV 2006 and so on. I have the rest of
the query working fine, its just the select case statement with a date range
that I can't figure out.
 
Hi Lou,

I still say to use a conversion table. Here is one possible solution
that allows for the start and end date to overlap calendar years.

tblModel_Conversions
Model_Prefix
Ship_Start_Date
Ship_End_Date
Type_Year (the resulting desired code and year)

If you other table is this:

tblModel_Information
Model_Number
Ship_Date
etc.

Then you can use this query to get the results. Again, no coding, not
modifying when you have new models and/or new years; just entering more data
into the coding table.

select Model_Number, Ship_Date, IIf(IsNull(Type_Year), "N/A", Type_Year) as
Type_Year_Adjusted
from tblModel_Information as A left join tblModel_Conversions as B
on Left$(A.Model_Number, Len(B.Model_Prefix)) = B.Model_Prefix and
A.Ship_Date >= B.Ship_Start_Date and A.Ship_Date <= B.Ship_End_Date;

Clifford Bass
 
Thanks for the advice but all I really need to know is if there is a way to
define some kind of date range in a select case statement.


Clifford Bass said:
Hi Lou,

I still say to use a conversion table. Here is one possible solution
that allows for the start and end date to overlap calendar years.

tblModel_Conversions
Model_Prefix
Ship_Start_Date
Ship_End_Date
Type_Year (the resulting desired code and year)

If you other table is this:

tblModel_Information
Model_Number
Ship_Date
etc.

Then you can use this query to get the results. Again, no coding, not
modifying when you have new models and/or new years; just entering more data
into the coding table.

select Model_Number, Ship_Date, IIf(IsNull(Type_Year), "N/A", Type_Year) as
Type_Year_Adjusted
from tblModel_Information as A left join tblModel_Conversions as B
on Left$(A.Model_Number, Len(B.Model_Prefix)) = B.Model_Prefix and
A.Ship_Date >= B.Ship_Start_Date and A.Ship_Date <= B.Ship_End_Date;

Clifford Bass

LouD said:
Sorry for the confusion guys.
It was a very long if statement that would not fit into the query builder. I
will paste in the original if statement so it makes more sense. I just
simplified the model numbers here in this code but this is a fraction of what
the code really is. There are about 30 different models.

TYPEYEAR: IIf([Model Number] Like "1111*" And ([Ship Date] Between
#1/1/2007# And #12/31/2007#),"WS 2007", IIf([Model Number] Like "2222*" And
([Ship Date] Between #1/1/2007# And #12/31/2007#),"SRV 2007",IIf([Model
Number] Like "3333*" And ([Ship Date] Between #1/1/2008# And
#12/31/2008#),"WS 2008",IIf([Model Number] Like "4444*" And ([Ship Date]
Between #1/1/2008# And #12/31/2008#),"SRV 2008",IIf([Model Number] Like
"5555*" And ([Ship Date] Between #1/1/2009# And #12/31/2009#),"WS
2009",IIf([Model Number] Like "6666*" And ([Ship Date] Between #1/1/2009# And
#12/31/2009#),"SRV 2009","N/A"))))))

Obviously there are more fields in the crosstab query than this but this is
the only statement that was too long for the query builder so i had to make a
function. Which is what I originally posted.

The end result is a crosstab query with the months across the top and the
rows with the type and year, SRV 2005, SRV 2006 and so on. I have the rest of
the query working fine, its just the select case statement with a date range
that I can't figure out.
Click to expand...
Click to expand...
 
Hi Lou,

Great, glad to hear it. You are welcome. I hope the other suggestions
I posted will help you down the road in designing a better solution in the
future.

Clifford Bass
 
Back
Top