Case when equivalent in MS access using VB.NEt code

Question

Hi all,

I am currently working on a project that "calculates" scores. I currently have 4 tables (I have more, but I don't need them now):

PARTICIPANTS tables
(
participantID (primary key, autonumber)
....
)

PROGNOSTIC table
(
prognosticID (primary key, autonumber)
MatchID (foreign key to MATCHES, number)
ParticipantID (foreign key to PARTICIPANTS, number)
HomeScore (number)
OutScore
)

MATCHES table
(MatchID (primary key, autonumber)
TournamentID (foreign key to tournaments)
HomeScore
Outscore
)

SCORES table
(ScoreId (primary key, autonumber)
Matchid (foreign key to MATCHES)
ParticipantID (foreign key to PARTICIPANTS)

The idea is to do the following:

1) Delete from SCORES where MatchID in (select matchid from matches where tournamentID = 1) (i can do this)
2) Insert into SCORES (Participant, Match, Score)
select a.participantID, b.Matchid,
case when a.homescore = b.homescore and a.outscore = b.outscore then '10'
when a.homescore > a.outscore and b.homescore > b.outscore then '5'
.....
from MATCHES a, PROGNOSTICS b
where a.matchid = b.matchid

However, the code above would work in Oracle, but apparantly in access it does not. I have searched the internet, but I only come across "Switch" or "Iif" functions. Doing the above with an if function seems like A LOT of work, and the Switch function doesn't work the way I hoped it does :-)

I tried the following:

Select Switch(a.homescore = b.homescore, 1, 0) from .... but that already gave an error. It just displays #FOUT (fout = error in Dutch) in the outcome.

Does anyone have an idea on how to handle this? I'm just a newbie in VB.NET/ACCESS :-)

Sven · Answer

Hi Vulpes, I haven't figured out what the problem was yet, but I used a different solution. I am just selecting all fields into a datatable and calculating everything in VB itself, and afterwards updating/inserting everything to Access. This seems to work for now :-) Thanks for the advice nevertheless.

Vulpes · Answer

Although you can get 'too complex' errors with Switch, I can't really understand why it shouldn't be able to cope with simple expressions involving columns from more than one table :-/ I'd see if a nested 'iif' would be any better: iif(a.homescore = b.homescore and a.outscore = b.outscore, "10", iif(a.homescore > a.outscore and b.Homescore > b.outscore, "5", ""))

Sven · Answer

Hi Vulpes, I tried the Switch like this, but it still gives errors: Switch(a.homescore = b.homescore and a.outscore = b.outscore, "10", a.homescore > a.outscore and b.Homescore > b.outscore, "5") However, if I try the following, I don't get an error: switch(a.homescore > 1 and a.outscore AND Switch(a.homescore > a.outscore, "1") So I only seem to be getting errors when comparing the values from a and b. If I use one table then all is well, but if I use two tables I don't get any output. Any ideas what can be wrong with that?

Vulpes · Answer

I don't think you're using the Switch function correctly as it will always have an even number of arguments - expression/value pairs. There's some documentation for it here: http://www.techonthenet.com/access/functions/advanced/switch.php So, I'd try replacing: case when a.homescore = b.homescore and a.outscore = b.outscore then '10' when a.homescore > a.outscore and b.homescore > b.outscore then '5' with: switch(a.homescore = b.homescore and a.outscore = b.outscore, '10', a.homescore > a.outscore and b.homescore > b.outscore, '5')