Microsoft Access Help

[mawby]

I'm writing a simple little database for someone using Access. I don't normally use Access and I normally connect to databases from a separate program, so I'm a bit unfamiliar with how some stuff works.

 

I have a couple of tables like this;

 

Symptoms

========

Magic - AutoNumber

Symptom - Text

Condition - Text

 

Procedures

========

Magic - AutoNumber

Symptom - Linked to Symptoms.Magic

Procedure - Text

Value - Text

 

Which contains data like;

 

Symptoms

========

1, Asthma, Review

2, Asthma, Severe

3, Acne,

4, Acne, Painful

 

Procedures

========

1, 1, Nurse, Y

2, 2, Duty Doctor, N

3, 3, Nurse, N

4, 4, Nurse, N

5, 4, Duty Doctor, T

 

(Actually the Procedures table above is the result of three related tables, but for simplicity in this example I've merged them into one)

 

I then have a form which has two combo boxes on it; Symptom and Condition, and a sub-form which displays a query on the Procedures table based on the value of the combo boxes.

 

The Symptom combo box is filled with unique symptoms from the Symptoms.Sympton column. When a user selects something from this combo box the Conditions combo box is then filled with all of the conditions for the given symptom.

 

When the user then selects something from the conditions combo box the Procedures sub-form is then filled.

 

Now I have all of this working nicely with a mixture or queries, macros and events, but I have a problem when a NULL condition column is encountered. My query is as follows;

 

SELECT DISTINCTROW Procedures.Desc, ProcedureValues.Desc
FROM Symptoms 
INNER JOIN (ProcedureValues 
   INNER JOIN (Procedures 
       INNER JOIN ProcedureMap ON Procedures.Magic = ProcedureMap.Procedure)
   ON ProcedureValues.Magic = ProcedureMap.Value) 
ON Symptoms.Magic = ProcedureMap.Symptom
WHERE (((Symptoms.Condition)=[Forms]![search Symptoms]![comboCondition])
AND ((Symptoms.Symptom)=[Forms]![search Symptoms]![comboSymptom]));

 

But when (using the example data above) symptom Acne is selected and the blank condition is selected, this query returns zero rows. I first thought it was because the combo box wasn't returning NULL for the blank entry but was instead an empty string, so I changed the WHERE clause to include something like...

 

WHERE (((Symptoms.Condition)=iif(Len([Forms]![search Symptoms]![comboCondition]) = 0, Null, [Forms]![search Symptoms]![comboCondition]))
AND ((Symptoms.Symptom)=[Forms]![search Symptoms]![comboSymptom])

 

...but that didn't work either. I've put a MsgBox in the macro to confirm that the Len is zero when the blank condition is selected, and it is.

 

So what am I doing wrong?

 

If I execute this simple query...

 

SELECT Symptoms.Condition, Symptoms.Magic, Symptoms.Symptom
FROM Symptoms
WHERE (((Symptoms.Condition) = Null));

 

...it will return the one row which has a NULL Condition value, although I've noticed that if I save and reload this query the = Null is changed to Is Null. Is this the problem?

 

Any ideas of how to get this to work?

 

This is very annoying!

[mawby]

No one. I was sure someone on here would be an expert with Access.

[markssupra]

Can you email it to me and I'll check it out. I'll pm you my email address

[markssupra]

I've had a look Lee and saved 2 queries (procedures and tmp) to updated versions, the originals are still intact.

I hope I found the problem and it appeared to be an error in the SQL code although when in design view of query using the QBE grid it shows it as IsNull but its not updating the SQL view code which is still = Null.

 

SELECT Symptoms.Condition, Symptoms.Magic, Symptoms.Symptom

FROM Symptoms

WHERE (((Symptoms.Condition) = Null));

 

query works when its:

 

SELECT Symptoms.Condition, Symptoms.Magic, Symptoms.Symptom

FROM Symptoms

WHERE (((Symptoms.Condition) is Null));

 

Also the procedures query works if you include an OR in the SQL code from this

 

SELECT DISTINCTROW Procedures.Desc, ProcedureValues.Desc

FROM Symptoms INNER JOIN (ProcedureValues INNER JOIN (Procedures INNER JOIN ProcedureMap ON Procedures.Magic = ProcedureMap.Procedure) ON ProcedureValues.Magic = ProcedureMap.Value) ON Symptoms.Magic = ProcedureMap.Symptom

WHERE (((Symptoms.Condition)=[Forms]![search Symptoms]![comboCondition]) AND ((Symptoms.Symptom)=[Forms]![search Symptoms]![comboSymptom]));

 

 

to this:

 

SELECT DISTINCTROW Procedures.Desc, ProcedureValues.Desc

FROM Symptoms INNER JOIN (ProcedureValues INNER JOIN (Procedures INNER JOIN ProcedureMap ON Procedures.Magic = ProcedureMap.Procedure) ON ProcedureValues.Magic = ProcedureMap.Value) ON Symptoms.Magic = ProcedureMap.Symptom

WHERE (((Symptoms.Condition)=[Forms]![search Symptoms]![comboCondition]) AND ((Symptoms.Symptom)=[Forms]![search Symptoms]![comboSymptom])) OR (((Symptoms.Condition) Is Null));

 

Hope its how you wanted it, if not I'll try again

[mawby]

Ah ha, you've reached where I got to a few days ago.

 

The problem with the SQL you've posted is although it does return the correct single row when Symptom = Acne and Condition is Null, if you select Condition = Paintful then it also returns the row where Condition = Null and that's not correct.

 

The results should be;

 

If Symptom = Acne And Condition= Null Then Procedure Map = Minor Illness

 

If Symptom = Acne And Condition= Painful Then Procedure Map = Duty Doctor

 

Make sense?

[markssupra]

Yes, I realised after I sent it back to you. I'll have another look, as its not so simple after all.

[mawby]

Having been stuck in SQL land this lunchtime working on stuff on this site, I've had another crack at this when I got home and came up with this less than elegant, but working, solution.

 

SELECT DISTINCTROW Procedures.Desc, ProcedureValues.Desc
FROM Symptoms INNER JOIN (ProcedureValues INNER JOIN (Procedures INNER JOIN ProcedureMap ON Procedures.Magic = ProcedureMap.Procedure) ON ProcedureValues.Magic = ProcedureMap.Value) ON Symptoms.Magic = ProcedureMap.Symptom
WHERE (Symptoms.Symptom) = [Forms]![search Symptoms]![comboSymptom]
AND ( IsNull([Forms]![search Symptoms]![comboCondition]) = -1 OR (Symptoms.Condition) = [Forms]![search Symptoms]![comboCondition] )
AND ( IsNull([Forms]![search Symptoms]![comboCondition]) = 0 OR (Symptoms.Condition) Is Null);

 

Now onto the next problem!