Switch is overloaded!

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi there! Using A02 in XP. Need to import some text with
Division numbers and need to convert them to the Contract
numbers so I can run an extract file. I have:

GP: switch([Div]="813","2466", [Div]="814","2467", [Div]
="815","2468", [Div]="816","2469", [Div]="817","2470",
[Div]="818","2471", [Div]="819","2472", [Div]
="930","2455", [Div]="931","2456", [Div]="932","2457",
[Div]="933","2458", [Div]="934","2459", [Div]
="935","2460", [Div]="936","2461", [Div]="938","2462",
[Div]="939","2463", [Div]="940","2464", [Div]
="942","2465",true,[Div])

I get an error that the expression I entered is too
complex. Am I doing this in the wrong place?

Thanks in advance for any help or advice. I luv u guys!!!
 
Yes, you are doing this in the wrong place. I would create a lookup table of
Div values and related values. Maintain the table rather than maintaining
your expression. If you can't do it the "data-driven" way, then create a
function:
Public Function GetGP(pstrDiv as String) as String
Select Case pstrDiv
Case "813"
GetGP = "2466"
Case "814"
GetGP = "2467"
'etc
End Select
End Function

You can then use an expression in your query like
GP:GetGP([Div])
 
if both the Div values and the Contract values are sequential - with no
gaps - as in your example below, try using the following public function:

Public Function isContract(ByVal lngDiv As Long) As String

Dim lngCnt As Long

lngCnt = CLng(lngDiv) - 813
isContract = CStr(2466 + lngCnt)

End Function

if either (or both) Div/Contract values are not absolutely sequential, use a
Select Case statement instead:

Public Function isContract(ByVal strDiv As String) As String

Select Case strDiv
Case "813"
isContract = "2467"
Case "814"
isContract = "2468"
'etc, etc, etc.
'you may want to use a Case Else line also, to assign a Contract
value
'to any "missed" Div value.
End Select

End Function

*make sure you test on a copy of your db before running it on live data*

hth
 
Thank you SO much for the info. Duh-Uh!!! I was really
trying to make it rough, wasn't I? I took your advice and
created a lookup table. Thanks for pulling me back in! It
works great!
-----Original Message-----
Yes, you are doing this in the wrong place. I would create a lookup table of
Div values and related values. Maintain the table rather than maintaining
your expression. If you can't do it the "data-driven" way, then create a
function:
Public Function GetGP(pstrDiv as String) as String
Select Case pstrDiv
Case "813"
GetGP = "2466"
Case "814"
GetGP = "2467"
'etc
End Select
End Function

You can then use an expression in your query like
GP:GetGP([Div])

--
Duane Hookom
MS Access MVP
--

Hi there! Using A02 in XP. Need to import some text with
Division numbers and need to convert them to the Contract
numbers so I can run an extract file. I have:

GP: switch([Div]="813","2466", [Div]="814","2467", [Div]
="815","2468", [Div]="816","2469", [Div]="817","2470",
[Div]="818","2471", [Div]="819","2472", [Div]
="930","2455", [Div]="931","2456", [Div]="932","2457",
[Div]="933","2458", [Div]="934","2459", [Div]
="935","2460", [Div]="936","2461", [Div]="938","2462",
[Div]="939","2463", [Div]="940","2464", [Div]
="942","2465",true,[Div])

I get an error that the expression I entered is too
complex. Am I doing this in the wrong place?

Thanks in advance for any help or advice. I luv u
Click to expand...
guys!!!


.
Click to expand...
 
Back
Top