Control Source character limit

[xicana]

Hi everyone,

I was soo happy because I had finally figured out a way to manipulate the data from my database onto the report - but as soon as I finished typing in my switch statement, i got an error message stating that the string exceeded character limit for that property.

Where do I place the switch code within the VBA Editor? Do I just create a general function and call it in the control source of the text box that I want the result displayed in? Do I need to pass a variable?

Here's the logic behind what I need:

If currentPeriod=02 then
   dlookup("[QtySoldPeriod1]","Table1","[year]= txtCurrYr and [whseCode] = txtWhseCode")

BREAK

if currentPeriod=03 then 
   dlookup("[QtySoldPeriod2]",".....

BREAK

if currentPeriod=04 then ....
   dlookup("[QtySoldPeriod3]","......

and so on (for a total of 12 periods)...





Sandy

 

[THWatson]

Sandy
You mention using the Switch function. Seems to me I remember running into a limit on the number of Switch statements that could be accomodated. But my memory also suggests it was 13, where you say you received an error at 12.

I'm assuming you would be using the Switch function in your query that populates the report. If so, you could try breaking up the Switch as follows...

=IIf(currentPeriod<=10, Switch([currentPeriod]=1, DLookup(...), 
[currentPeriod]=2, DLookup (...), etc. up to [currentPeriod]=10), DLookup
(...),Switch([currentPeriod]=11, DLookup(...) etc.))

I looked up where I had broken up the Switch statement in that manner, and post it below just as a sample.

IIf([Weekly]<=60,Switch([Weekly]<=2.5,"00.00-02.50",[Weekly]
<=5,"02.51-05.00",[Weekly]<=10,"05.01-10.00",[Weekly]<=15,"10.01-15.00",
[Weekly]<=20,"15.01-20.00",[Weekly]<=25,"20.01-25.00",[Weekly]<=30,"25.01-
30.00",[Weekly]<=35,"30.01-35.00",[Weekly]<=40,"35.01-40.00",[Weekly]
<=45,"40.01-45.00",[Weekly]<=50,"45.01-50.00",[Weekly]<=55,"50.01-55.00",
[Weekly]<=60,"55.01-60.00"),Switch([Weekly]<=65,"60.01-65.00",[Weekly]
<=70,"65.01-70.00",[Weekly]<=75,"70.01-75.00",[Weekly]<=80,"75.01-80.00",
[Weekly]<=85,"80.01-85.00",[Weekly]<=90,"85.01-90.00",[Weekly]<=95,"90.01-
95.00",[Weekly]<=100,"95.01-100.00",[Weekly]>100,"Over 100.00"))

Tom

 

[dhookom]

I would seriously question your table structure if you actually have fields named after time periods. You could create function that accepts the value for currentPeriod, CurrYr, WhseCode, and all 12 other fields and returns the correct period value.

I would try not to use DLookup() in the code if at all possible. We would need to know more about your structure and what you would expect to return and why.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xicana]

Well, I really don't have a choice when it comes to the table structure. These tables are being imported from MAS200 (accounting software). I just need to get the correct information on the report.

Among other things, my report needs to display the total number of an item sold in the previous 3 periods(months).
There is a table in MAS200 Inventory Module that lists the periods and the amount sold each period - but there is information for about 3 years in there. The key for that table is Item#, whse code & year.

I did not include this table in my query because it was messing up my data for some reason.

This was my solution:
I would put 3 invisible text boxes on my report - one for prevMonth1, prevMonth2, prevMonth3. Then if the current period is 2, then prevMonth1 = qtySoldPeriod1, prevMonth2 = qtySoldPeriod12 ,prevMonth3 = qtySoldPeriod11 . Notice that Period 12 and 11 are of the previous year. I would have a seperate textbox where I add these three numbers up and display the total. Maybe there's an easier way to get what I want - but I'm not finding it.

Sandy

 

[xicana]

Thanks Tom - I'll try your suggestion.

Sandy