switch calc 2

[Guest]

In the tblEvaluations the peopleTypeID is a number:
However on a data input form it is attached to a value list.
There is a SQL query for the field:
SELECT tblPeopleType.PeopleID, tblPeopleType.PeopleType FROM tblPeopleType
ORDER BY [PeopleType];
This is probably why it doesn't work with the switch statement since the
value is stored in the field not the number.
Here is my switch statement in a query for a report.

My switch calc doesn't work:
People:SWITCH ( PeopleID = 1, 'Party', PeopleID = 2, 'Attorney', PeopleID =
3, 'Insurance',TRUE, CHR(0) )

If the ID field is 1, I want the text Party. If the ID field is 2 I want
the work Attorney.
If it is 3 I want Insurance on the line in the report.

Is there a way to fix the SQL query on the form so that the switch statement
works on the report?

thanks,

 

[Guest]

Hi Janis,

In the tblEvaluations the peopleTypeID is a number:
However on a data input form it is attached to a value list.

I recommend changing this from a value list to a Table/Query row source type
with a SELECT statement. Value Lists are simply not very flexible. In order
to add or remove items from the list, you must open the object (form or
report) in design view. In addition, you must specify the items in the
correct order, since you cannot sort a value list.

There is a SQL query for the field:
SELECT tblPeopleType.PeopleID, tblPeopleType.PeopleType FROM tblPeopleType
ORDER BY [PeopleType];

A "SQL query for the field" sounds like you might have one of those nasty
Lookup fields defined at the table level. If so, you should perform an
exocism! The second Commandment of Access lists some of the reasons why you
want to avoid Lookup fields:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

You can download and install a nifty add-in tool (free) that will help
pinpoint all Table Lookups in your database:

CSD Tools
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

This is probably why it doesn't work with the switch statement since the
value is stored in the field not the number.

Are you sure about that? The SQL statement that you showed looks pretty
standard. It should be storing the number if the Bound Column property is set
to 1 and Column Count is set to 2. In any case, I recommend removing the
table lookup, by setting the Display Control for this field to a text box.
These properties (Display Control, Bound Column, and Column Count) are found
on the Lookup tab in the lower half of table design view, when you have the
applicable field selected.

Now would be a good time to make a back-up of your database, before making
any alterations, just in case something goes seriously wrong.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

In the tblEvaluations the peopleTypeID is a number:
However on a data input form it is attached to a value list.
There is a SQL query for the field:
SELECT tblPeopleType.PeopleID, tblPeopleType.PeopleType FROM tblPeopleType
ORDER BY [PeopleType];
This is probably why it doesn't work with the switch statement since the
value is stored in the field not the number.
Here is my switch statement in a query for a report.

My switch calc doesn't work:
People:SWITCH ( PeopleID = 1, 'Party', PeopleID = 2, 'Attorney', PeopleID =
3, 'Insurance',TRUE, CHR(0) )

If the ID field is 1, I want the text Party. If the ID field is 2 I want
the work Attorney.
If it is 3 I want Insurance on the line in the report.

Is there a way to fix the SQL query on the form so that the switch statement
works on the report?

thanks,

 

[Arvin Meyer [MVP]]

It looks like it should work, but if you aren't feeding it the correct data,
it won't work. Add a PeopleID column in the query and check to see if the
values are being correctly applied.

Since Access also allows the use of VBA functions you can also build a
function to return your values. Something like this in a standard module
ought to work:

Public Function Foo(lngIn As Long) As String
On Error Resume Next
Select Case lngIn
Case 1
Foo = "Party"
Case 2
Foo = "Attorney"
Case 3
Foo = "Insurance"
Case Else
End Select
End Function

Then in your query ass a column like:

Result: Foo([PeopleID])