Switch not working

[Student Databaser]

So after trying about a million things, i figured out how to use the switch
function, but now for some reason it is excluding some records. When i run
the switch query it comes up with 2464 records, and when i run the query just
of that field i get 4786. I did include null entries and zeros and i just
cant figure out what might be causing this. Is there a common error that i
should be aware of?

Here is my SQL for the 2 queries

Returns 2464 records

SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION,
DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID
FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID =
DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID =
STUDENT_COURSE.STUDENT_ID
WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR
(((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR
(((DEMOGRAPHIC.WORK) Is Null)) OR
(((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8,19,8,19,9,19,10,19,11,19,12,19,13,19)
And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19,18,19,19,19)));

Here is the one that returns 4789 records

SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK,
STUDENT_COURSE.DATE_OF_REGISTRATION
FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID =
DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID =
STUDENT_COURSE.STUDENT_ID;

 

[Michel Walsh]

The way you use Switch is not very common, You could have replace them with
the number 19.


Furthermore, having:

WHERE ... FieldName = something AND FieldName = somethingElse


won't return any record at all, unless something = somethingElse (as it
does, here, since your Switch-s both always return 19 ). Indeed, if the
value in the field is equal to, say something = 21, then it cannot be ALSO
equal to, say, something else = 22, because, well, if it is equal to 21, it
cannot be equal to 22.


What do you WANT, rather than HOW you did implement it?



Vanderghast, Access MVP

 

[Student Databaser]

I want to show records that have a 1,2, or 3, and i want records with 4-19 to
be displayed as 19.

Is this even possible without creating another table?

 

[John Spencer]

PERHAPS what you want is the following

SELECT DEMOGRAPHIC.WORK
, STUDENT_COURSE.DATE_OF_REGISTRATION
, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID
, IIF(Demographic.Work Between 4 and 19,19,Demographic.Work) as CalcValue
FROM (STUDENT INNER JOIN DEMOGRAPHIC
ON STUDENT.STUDENT_ID =
DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Student Databaser]

Thanks John, but unfortunately this isn't working either I don't think i
understand what you mean by CalcValue.

 

[Bob Barrows [MVP]]

OK, we don't understand what you mean by "not working". You are going to
have to be a lot clearer about what you want. The best way to do this is
to show us a few rows of sample data (relevant fields only), followed by
the rows of data you would like a successfully created query to return
from that sample data.

 

[Michel Walsh]

SELECT DEMOGRAPHIC.WORK,
iif( demographic.work BETWEEN 4 AND 19, 19, demographic.work)

FROM (STUDENT INNER JOIN DEMOGRAPHIC
ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID)
INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID

WHERE DEMOGRAPHIC.WORK IN("1", "2", "3")




should do.



Vanderghast, Access MVP

 

[Student Databaser]

Ok, sorry i wasn't clear. Here are a few rows of what the query returned.
What didn't work is that it didn't make numbers 4-19 appear as 19 for the
work field, and under CalcValue it says #Error. I assume this is because i
should have entered in some other field or expression but i was sure what
John meant.

Thanks so much for your help! I am obviously a novice at this and these
forums really help me to learn.

Expr1000 DATE_OF_REGISTRATION WORK STUDENT_ID CalcValue
4 2/13/2008 4 aa0703go
#Error
2 8/28/2007 2 ab0101ja
#Error
2 9/10/2007 2 ab0101ja
#Error
2 9/10/2007 2 ab0101ja
#Error
8 7/18/2006 8 ab0109na
#Error
18 10/17/2007 18 ab0601sa #Error

 

[Bob Barrows [MVP]]

We also need to see what the data looks like in the table (before
running the query), and what you want it to look like when the query
runs.

 

[John Spencer]

Is demographic.Work a number field or a text field. In one place you treat it
as a number field 14 and elsewhere as a text field "1". I am going to guess
it is a text field.


SELECT DEMOGRAPHIC.WORK
, STUDENT_COURSE.DATE_OF_REGISTRATION
, DEMOGRAPHIC.WORK
, STUDENT.STUDENT_ID
, IIF(Demographic.Work in ("1","2","3") or Demographic.Work is
null,Demographic.Work,"19") as CalcValue
FROM (STUDENT INNER JOIN DEMOGRAPHIC
ON STUDENT.STUDENT_ID =
DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID

CalcValue is the name of the value that is calculated in the expression.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Student Databaser]

It worked!!!!!! Thank you Thank you Thank you!

Is demographic.Work a number field or a text field. In one place you treat it
as a number field 14 and elsewhere as a text field "1". I am going to guess
it is a text field.


SELECT DEMOGRAPHIC.WORK
, STUDENT_COURSE.DATE_OF_REGISTRATION
, DEMOGRAPHIC.WORK
, STUDENT.STUDENT_ID
, IIF(Demographic.Work in ("1","2","3") or Demographic.Work is
null,Demographic.Work,"19") as CalcValue
FROM (STUDENT INNER JOIN DEMOGRAPHIC
ON STUDENT.STUDENT_ID =
DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE
ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID

CalcValue is the name of the value that is calculated in the expression.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County