Is there something like Cond and In in Access?

[Vladimír Cvajniga]

#1) Cond
In old PC FAND, which was base on Pascal, we had the following:

SomeValue:=cond(SomeField=1 And OtherField="A": "Excelent",
SomeField=2 And OtherField="A": "Almost
excelent",
SomeField=3: "Hmmm...",
else: "Ooops")

Is there an equivalent in Access 2007?
IMO:
1) Select Case will not do that.
2) Select Case can't be used in computed fields.
3) With IIf it's quite complicated.
4) The easiest way: Public Function. But do I really want a function only
for one field?... :-/
———————————————————————————————————————
#2) In (PC FAND again)
SomeValue:=cond(SomeField in (1..34, 37, 50..59, 99): "Baltimore",
SomeField in (35, 36, 60): "New York",
SomeField in (100..199): "Los Angeles",
else: "Praha")
Note that there are intervals, ie. 1 to 34, 50 to 59, 100 to 199.
Is there an equivalent in Access2007?

A2002: The only function I was able to create doesn't handle intervals:
Public Function fncIn(varValue As Variant, ParamArray arrArray()) As Boolean
Dim u As Integer

For u = LBound(arrArray) To UBound(arrArray)
fncIn = varValue = arrArray(u)
If fncIn Then Exit Function
Next u

End Function
-----------------------------------------------------------------------------
Example:
dim bExist as Boolean

bExist = fncIn(SomeValue, 1, 2, 3, 8, 50)
or
bExist = fncIn(SomeValue, "17", "32", "50")
or
bExist = fncIn(SomeValue, Value1, Value2, Value3, Value4, Value5,
Value6)
-----------------------------------------------------------------------------
As to intervals:
1) Am I doing something wrong?
2) Do I need to study Access arrays? ;-)

TIA

Vlado

 

[Susie DBA [MSFT]]

this right here:

SomeValue:=cond(SomeField in (1..34, 37, 50..59, 99): "Baltimore",
SomeField in (35, 36, 60): "New York",
SomeField in (100..199): "Los Angeles",
else: "Praha")


that is called a TABLE, kid

learn to make a table, populate the values you need
and then join to that table

 

[KARL DEWEY]

#1 --
Build a translation table. Use an update query with both tables. You will
need three fields - SomeField, OtherField, and translation. Join the tables
on the SomeField And OtherField.

#2 --
Note that there are intervals, ie. 1 to 34, 50 to 59, 100 to 199.
Is there an equivalent in Access2007?
You can use BETWEEN function for the ranges.

 

[Susie DBA [MSFT]]

you could even make a table that would work for this; it would be
called a non-equi-join

make a table

create table mycities
(
LowRange INT,
HighRange INT,
CityName
)


INSERT INTO mycities
Values (1,34,'Baltimore')

INSERT INTO mycities
Values (37,37,'Baltimore')

INSERT INTO mycities
Values (50,59,'Baltimore')


create table mycitiesdata
(mycity int)

insert into mycitiesdata
(1)

insert into mycitiesdata
(2)

insert into mycitiesdata
(37)

insert into mycitiesdata
(54)


select mycitiesdata.mycity, mycities.cityname
from mycitiesdata inner join mycities on mycitiesdata.mycity between
mycities.lowrange and mycities.highrange


hope that helps bro

I love helping people that help to push MS to start STFU stop conning
us into .NET _CRAP_ and STFU and start fixing bugs instead of selling
us on a new buggy version

I applaud your courage, bro

-Susie

 

[Susie DBA [MSFT]]

please do note that Access MDB will puke on this SQL Syntax; so if you
need a design-view then you might need to use either Access Data
Projects or SQL Server Management Studio

 

[John W. Vinson]

#1) Cond
In old PC FAND, which was base on Pascal, we had the following:

SomeValue:=cond(SomeField=1 And OtherField="A": "Excelent",
SomeField=2 And OtherField="A": "Almost
excelent",
SomeField=3: "Hmmm...",
else: "Ooops")

Is there an equivalent in Access 2007?

The Switch() function is pretty close. It takes arguments in pairs; evaluates
each pair left to right; and returns the second argument when it first finds a
True value in the first argument. In your example:

SomeValue:Switch(SomeField=1 And OtherField="A", "Excellent",
SomeField=2 And OtherField="A", "Almost excellent",
SomeField=3, "Hmmm...",
True, "Ooops")

Karl's suggestion of a translation table would be a good idea and a lot more
efficient; it's much easier to edit values in a table than to dig into some
arcane code and tweak the conditions!

John W. Vinson [MVP]

 

[Vladimír Cvajniga]

Thx for your respond, Karl.

#1 --
Build a translation table. Use an update query with both tables. You will
need three fields - SomeField, OtherField, and translation. Join the
tables
on the SomeField And OtherField.

a) Are translation tables available in A2002, too? Can't find anything about
them in help...
b) I'd like to do it in code too.

c) At the moment I'm waiting for a trial version of MS Office 2007 Pro.
(BTW, in Czech Republic we can't download trial version of MSO 2007 CZ,
instead we have to order a CD and pay for it. It's just some 5 bucks, but,
compared to EN or US, we're discriminated here...)

#2 --
Note that there are intervals, ie. 1 to 34, 50 to 59, 100 to 199.
Is there an equivalent in Access2007?
You can use BETWEEN function for the ranges.

BETWEEN can't be used in code...

 

[Vladimír Cvajniga]

TYVM for your respond, John.

At the moment I'm using A2002, see my answer to Karl's respond. I haven't
heard about translation tables yet :-/. Neither I can find anything about
them in A2002 help/MSDN (???) ... I'm lost... :-(

Vlado

 

[Vladimír Cvajniga]

TYVM for the Switch function. It seems that it does all I need. There's just
a bit of difference between Cond and Switch so I think I'll get used to it
soon.

Vlado

 

[John W. Vinson]

TYVM for your respond, John.

At the moment I'm using A2002, see my answer to Karl's respond. I haven't
heard about translation tables yet :-/. Neither I can find anything about
them in A2002 help/MSDN (???) ... I'm lost... :-(

There's not a type of table called a "translation table" - it's just a
descriptive name for how the table is used. For example, you could have a
table with three fields SomeField, OtherField, Ranking, with the values in
your Cond function. You can then create a Query joining this table to your
data table, joining Somefield to Somefield, Otherfield to Otherfield, and
displaying the Ranking value. As stated, it will require some extra work if
you want Otherfield to be ignored for certain values of Somefield, but with
some tweaking it can do what you ask.

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

Suzie is an alias of A a r o n K e m p f and that he is not an employee of
Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Albert D. Kallal]

TYVM for your respond, John.

At the moment I'm using A2002, see my answer to Karl's respond. I haven't
heard about translation tables yet :-/. Neither I can find anything about
them in A2002 help/MSDN (???) ... I'm lost... :-(

No, a translocation table is simply a table you setup, and use a sql "join"
to pull in the values from antoher table. It is not a access term...

The problem with switch, or you old approach is that you are hard coding the
values in the code, and you should not. To change your values...you have to
go into your code and find all the places where you done this. This
considered a bad programming practice, and for values to be changed, they
have to hire you back to change them!

If you build a table with the city name, and rnage, like:

tblCityRange

city startR EndR
Baltimore 1 34
Baltimore 37 37
Baltimore 50 59
Los Angeles 100 199


Then, in your query, you can go:

select FirstName, LastName, Company, CityR,
(select City from tblRange where CityR >= StartR and CityR <= EndR) as
CityText)
from tblCustomers

Now, you never need hard code your values, and further, you can add new
Cities and ranges to this table, and not have to modify your existing code
or reports. You could even build a "edit" screen for the above table to
allow you, or users to add, or modify the values....

 

[Albert D. Kallal]

No, a translocation table

That should read translation!!

 

[Vladimír Cvajniga]

I'd like to thank all for their responds.
For simple parameters that can change I always use "parameter" tables with
only one record. In other cases I use lookup tables.

But the idea of translation tables is clear now! It'll really need some
tweaks if I want to exclude some fields for some conditions. I think it
could be done with several queries which should join in a union query. I'll
try translation tables if I'll have more complicated conditions to solve.
Originally I wanted Cond especially for validation criteria which I could
dynamically change from code - Switch can do most of what I need.

I think Microsoft could do some more coding on validation criteria. I'll
always compare Access to PC FAND (DOS platform). In PC FAND user could not
skip un-filled fields in new record and new record couldn't be stored if it
didn't meet validation criteria. There were two types of validation rules:
soft (warning) and hard (critical). Critical criteria must have been
fulfilled. And, if necessary, programmer could switch validation off using a
simple parameter.

Vlado

 

[David W. Fenton]

There's not a type of table called a "translation table"

He could do it as a collection or an array if he doesn't want to
persist it.