Please Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[jdwm2310]

Hi,

My query was working but now it doesn't work. I get an error of "Undefined Business Unit in expression"
My expression is
Field: Business Unit: BusinessUnit([CostCenter])
Table: Blank
Total: Expression
Sort: Blank
Show: Blank
Critiera: [Enter Business Unit]

I created a module that expression is pulling the Business Unit information. Below is my module:

[tt]Option Compare Database
Option Explicit

Function BusinessUnit(CostCenter As String)
If CostCenter = "3413" Or CostCenter = "3418" Or CostCenter = "3100" Or CostCenter = "3232" _
Or CostCenter = "3240" Or CostCenter = "3250" Or CostCenter = "3310" Or CostCenter = "3830" _
Or CostCenter = "3800" Or CostCenter = "3260" Or CostCenter = "3280" Or CostCenter = "3821" _
Or CostCenter = "3270" Or CostCenter = "3810" Or CostCenter = "3822" Or CostCenter = "3400" _
Or CostCenter = "3411" Or CostCenter = "3412" Or CostCenter = "3414" Or CostCenter = "3415" _
Or CostCenter = "3416" Or CostCenter = "3417" Or CostCenter = "3420" Then
BusinessUnit = ("Corporate Services&quot
ElseIf CostCenter = "3600" Or CostCenter = "3620" Or CostCenter = "3632" Or CostCenter = "3633" _
Or CostCenter = "3640" Or CostCenter = "3641" Or CostCenter = "3642" Or CostCenter = "3643" _
Or CostCenter = "3644" Or CostCenter = "3645" Or CostCenter = "3646" Or CostCenter = "3647" _
Or CostCenter = "3660" Or CostCenter = "3670" Or CostCenter = "3671" Or CostCenter = "3650" _
Or CostCenter = "3651" Or CostCenter = "3652" Or CostCenter = "3653" Or CostCenter = "3654" _
Or CostCenter = "3655" Or CostCenter = "3610" Or CostCenter = "3630" Or CostCenter = "3900" _
Or CostCenter = "3996" Or CostCenter = "3940" Or CostCenter = "3959" Or CostCenter = "3901" _
Or CostCenter = "3903" Or CostCenter = "3904" Or CostCenter = "3909" Or CostCenter = "3924" _
Or CostCenter = "3926" Or CostCenter = "3949" Or CostCenter = "3920" Or CostCenter = "3931" _
Or CostCenter = "3944" Or CostCenter = "3945" Or CostCenter = "3946" Or CostCenter = "3947" _
Or CostCenter = "3948" Or CostCenter = "3951" Or CostCenter = "3952" Or CostCenter = "3953" _
Or CostCenter = "3954" Or CostCenter = "3955" Or CostCenter = "3956" Or CostCenter = "3957" _
Or CostCenter = "3958" Or CostCenter = "3980" Or CostCenter = "3985" Or CostCenter = "3990" _
Or CostCenter = "3996" Or CostCenter = "3907" Or CostCenter = "3908" Or CostCenter = "3915" _
Or CostCenter = "3927" Or CostCenter = "3960" Or CostCenter = "3965" Or CostCenter = "3902" _
Or CostCenter = "3995" Or CostCenter = "3994" Or CostCenter = "3997" Or CostCenter = "3998" _
Or CostCenter = "3999" Then
BusinessUnit = ("SECTOR&quot
ElseIf CostCenter = "4001" Or CostCenter = "4100" Or CostCenter = "4200" Or CostCenter = "4205" _
Or CostCenter = "4210" Or CostCenter = "4220" Or CostCenter = "4230" Or CostCenter = "4231" _
Or CostCenter = "4232" Or CostCenter = "4240" Or CostCenter = "4241" Or CostCenter = "4242" _
Or CostCenter = "4243" Or CostCenter = "4244" Or CostCenter = "4245" Or CostCenter = "4250" _
Or CostCenter = "4251" Or CostCenter = "4252" Or CostCenter = "4253" Or CostCenter = "4260" _
Or CostCenter = "4270" Or CostCenter = "4300" Or CostCenter = "4310" Or CostCenter = "4320" _
Or CostCenter = "4321" Or CostCenter = "4323" Or CostCenter = "4324" Or CostCenter = "4330" _
Or CostCenter = "4331" Or CostCenter = "4332" Or CostCenter = "4333" Or CostCenter = "4334" _
Or CostCenter = "4335" Or CostCenter = "4340" Or CostCenter = "4326" Or CostCenter = "4341" _
Or CostCenter = "4342" Or CostCenter = "4343" Or CostCenter = "4344" Or CostCenter = "4347" _
Or CostCenter = "4349" Or CostCenter = "4383" Or CostCenter = "4384" Or CostCenter = "4350" _
Or CostCenter = "4345" Or CostCenter = "4348" Or CostCenter = "4351" Or CostCenter = "4352" _
Or CostCenter = "4353" Or CostCenter = "4360" Or CostCenter = "4002" Or CostCenter = "4380" _
Or CostCenter = "4381" Or CostCenter = "4386" Or CostCenter = "4399" Or CostCenter = "4685" _
Or CostCenter = "4600" Or CostCenter = "4601" Or CostCenter = "4609" Or CostCenter = "4610" _
Or CostCenter = "4620" Or CostCenter = "4625" Or CostCenter = "4630" Or CostCenter = "4635" _
Or CostCenter = "4640" Or CostCenter = "4650" Or CostCenter = "4680" Or CostCenter = "4690" _
Or CostCenter = "4693" Or CostCenter = "4700" Or CostCenter = "4710" Or CostCenter = "4720" _
Or CostCenter = "4730" Or CostCenter = "4740" Or CostCenter = "4750" Or CostCenter = "4800" _
Or CostCenter = "4830" Or CostCenter = "4801" Or CostCenter = "4841" Or CostCenter = "4840" _
Or CostCenter = "4842" Or CostCenter = "4870" Or CostCenter = "4811" Or CostCenter = "4805" _
Or CostCenter = "4810" Or CostCenter = "4820" Or CostCenter = "4850" Or CostCenter = "4890" _
Or CostCenter = "4860" Or CostCenter = "4861" Or CostCenter = "4862" Or CostCenter = "4900" _
Or CostCenter = "4910" Or CostCenter = "4920" Or CostCenter = "4921" Or CostCenter = "4922" _
Or CostCenter = "4930" Or CostCenter = "4941" Or CostCenter = "4940" Or CostCenter = "4942" Then
BusinessUnit = ("NYSE SERVICES&quot
ElseIf CostCenter = "4943" Or CostCenter = "4944" Or CostCenter = "4802" Then
BusinessUnit = ("NYSE SERVICES&quot
ElseIf CostCenter = "5200" Or CostCenter = "5210" Or CostCenter = "5220" Or CostCenter = "5230" _
Or CostCenter = "5240" Or CostCenter = "5241" Or CostCenter = "5250" Or CostCenter = "5260" _
Or CostCenter = "5270" Or CostCenter = "5280" Or CostCenter = "5300" Or CostCenter = "5380" _
Or CostCenter = "5303" Or CostCenter = "5310" Or CostCenter = "5320" Or CostCenter = "5330" _
Or CostCenter = "5340" Or CostCenter = "5341" Or CostCenter = "5360" Or CostCenter = "5370" _
Or CostCenter = "5390" Or CostCenter = "5395" Then
BusinessUnit = ("AMEX SERVICES&quot
ElseIf CostCenter = "5401" Or CostCenter = "5490" Or CostCenter = "5492" Or CostCenter = "5493" _
Or CostCenter = "5495" Or CostCenter = "5494" Or CostCenter = "5497" Or CostCenter = "5498" _
Or CostCenter = "5402" Or CostCenter = "5410" Or CostCenter = "5431" Or CostCenter = "5441" _
Or CostCenter = "5464" Or CostCenter = "5470" Or CostCenter = "5482" Or CostCenter = "5484" _
Or CostCenter = "5420" Or CostCenter = "5422" Or CostCenter = "5423" Or CostCenter = "5403" _
Or CostCenter = "5425" Or CostCenter = "5430" Or CostCenter = "5450" Or CostCenter = "5404" _
Or CostCenter = "5480" Or CostCenter = "5496" Or CostCenter = "5466" Or CostCenter = "5467" Then
BusinessUnit = ("Shared Data Center&quot
ElseIf CostCenter = "0" Then
BusinessUnit = "No Cost Center Info"
Else
BusinessUnit = Null
End If
End Function [/tt]

 

[r937]

i would place all these values into a separate table

it will be much easier to make changes

okay?????????????????????????????????????????

 

[zerosandones]

Just a few comments:

You need to seriously look at redesigning your solution to this problem ... your use of a function to provide for this type of comprehensive data validation is sinful at best ... look at creating a two-column table having BusinessUnit and CostCenter as the primary key and then incorporate that new lookup table into your driving query.

Nonetheless, faced with what you have to deal with today, it makes no sense that on the one hand you have writtent a function to return a business unit based upon a passed parameter for cost center when, at the same time, your query has criteria which prompts you to [Enter Business Unit] .... remove that criteria and let us know if that fixes things.

P.S. I don't meant to be overly critical ... my comments are meant to be constructive ... if you're looking for the details for a better over-all solution, please post details of your existing tables and exactly what you are trying to accomplish.

Good luck.

00001111s

 

[jdwm2310]

zerosandones,

Your comments were well put, I appreciate your help and suggestions. Now I created a table with CostCenter and BusinessUnit. How would I go apply it to my query with a Dlookup function..I want the user to enter the Business Unit. And allow the query to pull up all the CostCenter within that business unit. Is this possible. If so I would I go about doing this?

 

[jdwm2310]

zerosandones,

I removed the criteria and it works. How could i insert a parameter that will allow my users to enter the business unit of their choice?

 

[zerosandones]

First, let me list the assumptions I need to make before I give you one solution that you might want to consider:

ASSUMPTIONS:

1) Create a lookup table entitled tblBusinessUnit with two fields: BusinessUnitID (AutoNumber) and BusinessUnitName (Text)

NOTE: The primary key is BusinessUnitID

2) Create a second lookup table entitled tblCostCenter with three fields: CostCenterID (Text), BusinessUnitID (Long Integer), CostCenterName (Text)

NOTE: Primary key is CostCenterID

3) Create a table relationship between tblBusinessUnit and tblCostCenter (select "Relationships" from the Tools pull-down menu) linking on BusinessUnitID and select "Enforce Referential Integrity&quot. NOTE: it's your call, but, generally speaking, you would not choose to cascade updates or deletes unless you "changed" primary key values frequently AND its the way business happens.

4) Now, lets go back to look at the design of the tblCostCenter table. Highlight the BusinessUnitID field, select the "Lookup" tab at the bottom, change the "Display Control" to be a combo box, and enter the following SQL into the field entitled "Row Source":

SELECT tblBusinessUnit.BusinessUnitName, tblBusinessUnit.BusinessUnitID
FROM tblBusinessUnit
ORDER BY tblBusinessUnit.BusinessUnitName;

Then, set the "Bound Column" = 2, "Column Count" = 2, "Column Widths" = 2";0" and "Limit List" = Yes.


5) Next, enter all of your business units into the tblBusinessUnit table.

6) Now, open the tblCostCenter table in datasheet view and enter your cost centers ... notice that when you move into the BusinessUnitID field, the field will appear as a combo-box and you can select a value from your list of valid Business Units.

7) Okay, now open up the tblBusinessUnit table in datasheet view and notice the (+) sign annotation to the immediate left of every record. Click on the + sign and get an immediate view of those cost centers you have already specified for that business unit ... you can also enter additional cost centers whilwe in this view.

Okay ... does this help or do you need more info ... I'm assuming that you have another main table which makes reference to these cost centers for the purpose of tracking some sort of information ... do you need more input?

00001111s

 

[jdwm2310]

Ok on this everything you said...now how can I get the users to enter the business unit name and see all the records for that particular unit?

 

[zerosandones]

It would be helpful if you divulged more of what the environment and processes were that are involved here.

In their absence, my guess is that you're wanting to provide a quick lookup mechanism for your users to see a list of valid cost centers that they can charge to if they belong to a specific business unit.

In that case, I would suggest a form named "frmValidCostCenter" whose record source is a query named "qryValidCostCenter" having the following SQL:

SELECT CostCenter, CostCentername
FROM tblCostCenter
WHERE BusinessUnitID = [forms]![frmValidCostCenter]![cboBusinessUnitID]
ORDER BY CostCentername;

Other properties of the form include:

Default View: Continuous Forms


Then, I would place a combo box named "cboBusinessUnitID" in the form's header or footer. The row source for the combo box would be:

SELECT BusinessUnitName, BusinessUnitID
FROM tblBusinessUnit
ORDER BY BusinessUnitName;

Other properties of the combo box would be:

Control Source: (do NOT specify a value here)
Column Count: 2
Column Widths: 2";0"
Bound Column: 2
Limit to List: Yes

Finally, add a procedure to the "After Update" event of the combo box as follows:

Private Sub cboBusinessUnitID_AfterUpdate()

On Error GoTo Err_cboBusinessUnitID_AfterUpdate

Me.Requery

Exit_cboBusinessUnitID_AfterUpdate:
Exit Sub

Err_cboBusinessUnitID_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboBusinessUnitID_AfterUpdate

End Sub


This should just about do it for you ... good luck.

00001111s

 

[jdwm2310]

zerosandones,

On the second table, tblCostCenter, the BusinessUnitID is a number, right?
I entered the SQL in the RowSource but when I click to view datasheet there is nothing in the combobox. I entered the BusinessUnitName in tblBusinessUnit.

I did everything you wrote:
I created the two tables: tblCostCenter and tblBusinessUnit
tblCostCenter has a primarykey - CostCenterID (TEXT) and
tblBusinessUnit has a primarykey - BusinessUnitID (AutoNumber)

I created a Relationship between the BusinessUnitID - Enforce Referential Integrity.

In tblCostCenter - the BusinessUnitID field change it to a combobox in the rowsource I added the SQL you provided. I set all the Columns, Counts, Width and Limit List as you indicated.

I entered the "5" Business Unit into tblBusinessUnit in field BusinessUnitName.

Then when I went to enter the CostNumbers in tblCostCenter in dataview a parameter pops up and there is no data in the combobox.

Any idea???

 

[zerosandones]

In answer to your first question, yes, BusinessUnitID is a number (long integer) in the tblCostCenter table. By default, any newly created number field is created as a long integert, however, verify in the field properties in table design view. It needs to be long integer to be "datatype-compatible" with the autonumber datatype used in the tblBusinessUnit table.

To get to the bottom of the rest of the symptoms you describe, let's take it step by step. It's unclear to me whether or not you are experiencing problems in a combo-box on a form (which refers to my last post) or when viewing tblCostCenter directly in datasheet view.

Please confirm.

00001111s

 

[jdwm2310]

Ok...I have a main table called tblHelpdesk..here is where the users enter information (i.e.; CostCenter)

I want to create a query using the tblHelpDesk and link it with the tblBusinessUnit and tblCostCenter.

In this query I want to have a criteria that states "Enter Business Unit". When I enter the Business Unit name then the result of the query will be all the records from the tblHelpDesk that have a costcenter from that particular business unit.

For example: I enter "marketing" then the result will show all those records that have a costcenter number that is related to "marketing".