Query Problem with Left Joint

[rockyjr]

Hi everyone,

I have created a query that queries many tables. The relationships between them and the main table are left joints.

The query works fine, I see all my data. The issue that I'm having is when I put a Like statement on a field that has nulls.

ex: Like "*" & [txtname] & "*"

When I do that, it filters out the whole line where there's a null.

How can I do it so that it doesnt filters it out?

Thanks in advance!!

 

[Alc]

Does it work if you make the criteria?

(Is Not Null) or (Like "*" & [txtname] & "*")

 

[rockyjr]

Yes I did try,

But when I do that, it comes back with what I searched for and all the "not" nulls.

 

[Alc]

Sorry, i think I'm missing something here.

If you want to see only those records that contain the value of the field you mention, why do you also want it to not filter out the records where that field is null?

 

[rockyjr]

Here, I have made an example of what I'm trying to do.

When you double click on the query without the criteria, you get all the info (3 entries). But, when you double click on the one that has the criteria, and leave the field blank (just click ok when it ask for [hnumber]), it will have filtered a number because it is blank (now 2 entries).

What I want is, even though nothing is entered in the field, it should go get everything or... whatever you enter.

 

[Alc]

Sorry, I can't view the file.
It has an accdb extension and the work PC doesn't recognize it.

 

[rockyjr]

Oups sorry!!!

Here you go.

 

[Alc]

How's this?

The results looked the same to me, but I only tested it with a couple of numbers

SELECT Customer.ID, Customer.[Last Name], Customer.[First Name], HomeNumber.HomeNumber, MobileNumber.MobileNumber
FROM (Customer LEFT JOIN HomeNumber ON Customer.ID = HomeNumber.CustomerID) LEFT JOIN MobileNumber ON Customer.ID = MobileNumber.CustomerID
WHERE (((HomeNumber.HomeNumber) Like "*" & [hnumber] & "*")) OR (((HomeNumber.HomeNumber) Is Null));

 

[rockyjr]

That is so close.... when entering nothing, everything comes up... that awesome.... but when entering lets say "555", I get that number, plus the null values.

Close though.... I thought you had it.

 

[Alc]

Sorry, this one has got me stumped for the moment.

I'll carry on looking at it, as I get a chance, but in case I don't get any further 'Good Luck'.

 

[rockyjr]

You and me bud

Thanks for trying....

 

[boblarson]

Might I suggest you use a form for the input and at least there you can specify two completely different SQL statements depending on what is entered.

 

[rockyjr]

Ya, I was thinking about that. The only thing is that I have about 9 fields on that form and that's a lot of "IFs" to create.....

But, this shouldnt be this hard to do???

 

[Alc]

Might I suggest you use a form for the input and at least there you can specify two completely different SQL statements depending on what is entered.

Purely for my own ego's sake - is this possible as a simple query?
It's not the fact that it won't work that's bugging me, it's that I don't understand why?

 

[rockyjr]

I was able to do it with a simple table... but when I do it with LEFT JOINs... it doesnt work.... dont understand??

 

[rockyjr]

Forget about that.... I cant do it with a simple table anymore?????

 

[boblarson]

If you have 9 fields to search on then a form is the best way to go. You can then build your SQL statement on the fly to include whatever has been filled out, or not.

See here for a sample:
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[rockyjr]

Ya... it's a lot of work... a lot of combinations... but buildable......!!

Ok boss!!

Thanks guys.... if you have other ideas.... let me know.... for now I'll start with the IFs..... lol

 

[boblarson]

Ya... it's a lot of work... a lot of combinations... but buildable......!!

Ok boss!!

Thanks guys.... if you have other ideas.... let me know.... for now I'll start with the IFs..... lol

If you have 9 fields then it is only 9 If statements to build the query in code. Not what I would consider a lot.

 

[rockyjr]

Dont you have to do all the combinations....

Like if field 1 and 2 have text in them... or field 4 and 5... and so on???