SQL and null value problems

[HRN_SciTech]

Hi there,

I have a database in which I am building up an inventory of all the chemicals in our laboratory. There is a table in which all the data is stored (Chemical), a query (ChemQuery) and a form for searching (ChemSearch). There is also one more form for data entry (ChemicalEntry) but that's irrelevant I think.

Anyway, the problem is that chemicals that are missing information are not showing up in the query. I need these to show up. In ChemicalEntry, you can search for Chemical, Synonyms & Category.

Currently, if you run a search it does not return any chemicals which are missing the synonym (not applicable for some) and the category (left blank deliberately for some).

The SQL code currently reads:

SELECT Chemicals.Chemical, Chemicals.Synonyms, Chemicals.Category, Chemicals.Location, Chemicals.[Amount present (g/mL)], Chemicals.[CAS number], Chemicals.Classification, Chemicals.Class, Chemicals.[Shelf Life], Chemicals.[Expiry Date]
FROM Chemicals
WHERE (((Chemicals.Chemical) Like "*" & [Forms]![ChemSearch]![Chemical] & "*") AND ((Chemicals.Synonyms) Like "*" & [Forms]![ChemSearch]![Synonyms] & "*") AND ((Chemicals.Category) Like "*" & [Forms]![ChemSearch]![Chemical] & "*"));


I noticed on another thread someone had the same problem and they used "Like IFF(IsNull" but I can't get this code to work for the 'Category' field, though it was working for the 'Synonym' field.

Help??

Thank you!

 

[MarkK]

Where and how are you using that SQL you posted? When using the LIKE operator I would expect for your final string to be enclosed in quotes, but you are only enclosing your wild-cards in quotes.

Like '*searchtext*'
[COLOR="Green"]'not [/COLOR]
Like '*'searchtext'*'

In addition, is it your intention to AND those terms together? When you use AND you only return records where ALL the terms are true. Do you mean to OR the terms together?

Like Expression1 AND Like Expression2
[COLOR="Green"]'vs[/COLOR]
Like Expression1 OR Like Expression2

Cheers,

 

[ButtonMoon]

Hi there,

I have a database in which I am building up an inventory of all the chemicals in our laboratory. There is a table in which all the data is stored (Chemical), a query (ChemQuery) and a form for searching (ChemSearch). There is also one more form for data entry (ChemicalEntry) but that's irrelevant I think.

Anyway, the problem is that chemicals that are missing information are not showing up in the query. I need these to show up. In ChemicalEntry, you can search for Chemical, Synonyms & Category.

Currently, if you run a search it does not return any chemicals which are missing the synonym (not applicable for some) and the category (left blank deliberately for some).

SciTech,

Are you familiar with Three-Value Logic (3VL)? Because of 3VL, expressions in SQL involving nulls don't evaluate as they would in everyday maths, logic or reality. Either you can learn the 3VL rules and work around them to get the desired result, or you can avoid using nulls in your database. Unfortunately even if you rigorously avoid nulls in tables they sometimes crop up in queries.

Here's an informative video about nulls and missing information by Chris Date:
http://www.youtube.com/watch?v=kU-MXf2TsPE

 

[HRN_SciTech]

Where and how are you using that SQL you posted? When using the LIKE operator I would expect for your final string to be enclosed in quotes, but you are only enclosing your wild-cards in quotes.

Like '*searchtext*'
[COLOR=green]'not [/COLOR]
Like '*'searchtext'*'

In addition, is it your intention to AND those terms together? When you use AND you only return records where ALL the terms are true. Do you mean to OR the terms together?

Like Expression1 AND Like Expression2
[COLOR=green]'vs[/COLOR]
Like Expression1 OR Like Expression2

Cheers,

Hi there. The instructional video that I watched on YouTube said to put the Like operator in as such so that the query reads it as including the search word.. e.g. if someone looks up a chemical and just types 'hydro', the query will read it as *hydro* so any chemical including the word 'hydro' will display.

 

[HRN_SciTech]

SciTech,

Are you familiar with (3VL)? Because of 3VL, expressions in SQL involving nulls don't evaluate as they would in everyday maths, logic or reality. Either you can learn the 3VL rules and work around them to get the desired result, or you can avoid using nulls in your database. Unfortunately even if you rigorously avoid nulls in tables they sometimes crop up in queries.

Here's an informative video about nulls and missing information by Chris Date:

Hi there. Unfortunately nulls will have to be a part of this database as a lot of the information is lacking, especially on older chemicals. I don't know 3VL sorry, I'm an absolute newbie when it comes to Access.. I will watch the video

 

[HRN_SciTech]

Just to clarify, I want any person to be able to use the search form, type in a chemical and have the information come up regardless of whether some of that information is missing or not

 

[MarkK]

I don't think you understood my post. Your quotes are like this . . .

"*"searchtext"*"
[COLOR="Green"]'but should be[/COLOR]
"*searchtext*"

Do you see the difference?

 

[HRN_SciTech]

I don't think you understood my post. Your quotes are like this . . .

"*"searchtext"*"
[COLOR=green]'but should be[/COLOR]
"*searchtext*"

Do you see the difference?

Yeah I see the difference but I don't know how I would put it in in conjunction with the [Forms]![ChemSearch]![nameOfthingy]...

 

[MarkK]

So then I re-pose the question . . .

Where and how are you using that SQL you posted?

Are you assigning this SQL to the recordsource property of a form or listbox, are you opening a recordset, are you creating a query in the design grid? In these different contexts i would handle the form references and delimiters in different ways.

 

[HRN_SciTech]

So then I re-pose the question . . .

Are you assigning this SQL to the recordsource property of a form or listbox, are you opening a recordset, are you creating a query in the design grid? In these different contexts i would handle the form references and delimiters in different ways.

You'll have to excuse me, I'm a newbie....

That SQL was what came up when I clicked SQL View on the Query (ChemQuery). I had already input the Like "*" etc. stuff in design view on the query.

It's not in the properties of a form or listbox, it's the actual code that the query is run on.

I partially solved the problem by assigning default values to 'category' and 'synonym' but this is not ideal.


Just to reiterate: Problem= null values do not show up in query. Solution is wanted in either design view or SQL of query. Solution= exact coding or function or text that will allow null values to show up.

 

[jdraw]

Can you elaborate on this?

Anyway, the problem is that chemicals that are missing information are not showing up in the query. I need these to show up. In ChemicalEntry, you can search for Chemical, Synonyms & Category.

When you say "... are missing information...", do you mean nothing about that Chemical shows in the query?

If an attribute is NULL/empty/missing, what would you expect to show for that attribute (field) of the Chemical table?

You can say things such as (vba) If (IsNull([CAS Number]),"CAS missing",[Cas Number])

which means if the CAS Number Is Null then put up a string "CAS Missing". If the CAS number is not null, then show the [CAS Number]

 

[HRN_SciTech]

Can you elaborate on this?


When you say "... are missing information...", do you mean nothing about that Chemical shows in the query?

If an attribute is NULL/empty/missing, what would you expect to show for that attribute (field) of the Chemical table?

You can say things such as (vba) If (IsNull([CAS Number]),"CAS missing",[Cas Number])

which means if the CAS Number Is Null then put up a string "CAS Missing". If the CAS number is not null, then show the [CAS Number]

Sorry if I'm confusing everyone. I've only introduced myself to Access yesterday.

No, nothing shows up in the query. I will give an example because I can't really explain it well.

I want to search for 'Hydrochloric Acid'. In the search form, there are three different things I can search for including Chemical (the name of it), Synonym (any different names for it) or Category (type of chemical). I type "Hydrochloric" in 'Chemical' and it doesn't show up because it doesn't have a synonym entered in the actual table data (same goes for it it didn't have a category entered).

However, if I search for 'Calcium Hydroxide', it has both a synonym 'Slaked Lime' and a category 'Inorganic Compound' entered and thus it shows up in the query.

If I don't enter anything into the search and run the query, all data comes up that has both synonym and category entered. Any chemicals missing these fields do not show up.

 

[jdraw]

Try changing your AND conditions to OR and see if that helps.

 

[HRN_SciTech]

Try changing your AND conditions to OR and see if that helps.

Yeah I tried that first and the query just brings up every record

 

[MarkK]

What happens if you use this as your where clause?

WHERE (Chemical Like "* & [Forms]![ChemSearch]![Chemical] & *") 
   OR (Synonyms Like "* & [Forms]![ChemSearch]![Synonyms] & *") 
   OR (Category Like "* & [Forms]![ChemSearch]![Chemical] & *");

 

[HRN_SciTech]

What happens if you use this as your where clause?

WHERE (Chemical Like "* & [Forms]![ChemSearch]![Chemical] & *") 
   OR (Synonyms Like "* & [Forms]![ChemSearch]![Synonyms] & *") 
   OR (Category Like "* & [Forms]![ChemSearch]![Chemical] & *");

Just put that in.. the query now returns nothing

 

[MarkK]

Amend as follows (change is in red) . . .

WHERE (Chemical Like "* & [Forms]![ChemSearch]![Chemical] & *") 
   OR (Synonyms Like "* & [Forms]![ChemSearch]![Synonyms] & *") 
   OR (Category Like "* & [Forms]![ChemSearch]![[COLOR="Red"]Category[/COLOR]] & *");

And the form ChemSearch is open, you get no error, and you've got what data entered in the controls Chemical, Synonym, Category???

 

[HRN_SciTech]

Amend as follows (change is in red) . . .

WHERE (Chemical Like "* & [Forms]![ChemSearch]![Chemical] & *") 
   OR (Synonyms Like "* & [Forms]![ChemSearch]![Synonyms] & *") 
   OR (Category Like "* & [Forms]![ChemSearch]![[COLOR=red]Category[/COLOR]] & *");

And the form ChemSearch is open, you get no error, and you've got what data entered in the controls Chemical, Synonym, Category???

Hi there. Amended as instructed.

I entered nothing in 'synonym' & 'category' but entered 'Amm" (for ammonium), nothing came up. I tried entering 'fer' for "ferric" and nothing came up. I entered nothing and ran the query, nothing came up. I opened the query manually, nothing came up..

 

[MarkK]

So try this where clause . . .

WHERE Chemical Like "*Amm*"

Also, narrow down the possible failures. Delete the where clause entirely. Keep narrowing until something works. Then start adding things back one at a time and see which specific part fails.

 

[HRN_SciTech]

So try this where clause . . .

WHERE Chemical Like "*Amm*"

Also, narrow down the possible failures. Delete the where clause entirely. Keep narrowing until something works. Then start adding things back one at a time and see which specific part fails.

Hi there.

Thanks for helping by the way.

The search form works with the current clause which is:

WHERE (((Chemicals.Chemical) Like "*" & Forms!ChemSearch!Chemical & "*") And ((Chemicals.Synonyms) Like "*" & Forms!ChemSearch!Synonyms & "*") And ((Chemicals.Category) Like "*" & Forms!ChemSearch!Category & "*"));


However, it's only problem is it doesn't return records if they have any of the query fields missing i.e. chemical, synonym or category.

Obviously there won't be any fields missing for the 'chemical' part but sometimes I am unable to supply a synonym or category and subsequently they don't show up.

What I am really needing is to add the IIF(IsNull) function or the Nz function into the 'Where' clause so that chemicals show up even if they have a synonym or category missing