Problem filtering records of a table with SQL and forms (1 Viewer)

AccOUCH

Registered User.
Local time
Today, 02:45
Joined
Sep 27, 2018
Messages
25
Hello,

I have a form in Access where I can select some products. When I click a button, a new form is opened and I can see all my products of a brand. As you can see in this screen capture:
Screen1.jpg

Then, when the button "See" is clicked, a new form is opened and the table inside it is set up. It's called "Findproduct". In this form there are two text boxes called Filter and Filter2. They catch the values of KIND and PROVIDER that you can see I the screen capture. Also, the table of this new form is set up, in it's properties Data values, by the following SQL:

Code:
SELECT AL.codigo, AL.Nombre, AL.familia, AL.tipo, AL.Iva
FROM AL
WHERE (((AL.familia)=[forms]![findproduct]![filter2]) AND ((AL.tipo)="lentes") AND ((AL.Activo)=True) AND (([codigo] & " " & [nombre]) Like "*" & [forms]![findproduct]![filter] & "*"))
ORDER BY AL.Nombre;

With all this my aim is to show in that table information about only two kinds of products: The TypeA ones OR the rest of them.

Well. The problem is that I can not find the way to give filter2 an empty value so that the SQL shows all the results but type A. I've tested with an '*' but it crashes. And no idea of how to tell the program to show all products but the TypeA ones.

There are only these two possibilities: Show the TypeA products or all the others. Only if Type A are selected, this value is loaded in the filter2 textbox at the "FindProduct" form. Else, this textbox remains empty or loaded with an '*'

But still, it fails.

I would appreciate any help or clue to solve this intriguing problem.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:45
Joined
Jul 9, 2003
Messages
16,243
Shouldn't this line:-
ORDER BY AlmacenLentes.Nombre;

Be:-
'ORDER BY AL.Nombre;

????
 

AccOUCH

Registered User.
Local time
Today, 02:45
Joined
Sep 27, 2018
Messages
25
Shouldn't this line:-
ORDER BY AlmacenLentes.Nombre;

Be:-
'ORDER BY AL.Nombre;

????

Yes. I've just edited the message to correct it.

Still...the problem remains. How to properly do this filtering?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:45
Joined
Jul 9, 2003
Messages
16,243
Still...the problem remains. How to properly do this filtering?

I don't Know...

You don't seem to be getting much feedback on this question so I thought I would post what I've looked at so far. - It's easier to see a problem/solution if the code is written in your own language, so I've taken the liberty of converting it into English which may well not be entirely accurate! Also I'm currently learning Spanish so the conversion was of interest!

'AlmacenLentes = AL = Warehouses
'lentes = Glasses

'SELECT code, Name, Family, kind, VAT --- (Other Fields - Activo - AlmacenLentes)

'SELECT codigo, Nombre, familia, tipo, Iva
'FROM AL
'WHERE (((familia) = [Forms]![findproduct]![filter2])
'And ((tipo) = "lentes")
'And ((Activo) = True)
'And (([codigo] & " " & [nombre])
'Like "*" & [Forms]![findproduct]![Filter] & "*"))
'ORDER BY AL.Nombre;

I don't really like the look of this piece of code,it just doesn't look right:-

'And (([codigo] & " " & [nombre]) Like "*" & [Forms]![findproduct]![Filter] & "*"))

It seems to me that you are concatenating two Fields together and trying to find a match for the concatenation.

As to filtering records and not returning results, --- "show all products but the TypeA ones" --- the query wizard has an option to find unmatched records the selection is "Find Unmatched Query Wizard"... I'm thinking you write a query to find all of the records "TypeA" and then find the Unmatched Records, which will be all the other records! Thereby excluding the "TypeA" records...

I think if I were you I'd start and have a play with that, just see if you can get one aspect of your filter working with that. Then move on to the rest, I suspect you might end up having to create a set of queries.

Mind you there's an awful lot I don't know about SQL! Someone may show you a nice concise SQL Statement that will do it.

It might be an idea if you posted a demo version of your database containing sample data that you can share and that will give others the option of seeing if they can come up with something.

If you are unable to share your database then you could try one of these approaches:-

Nifty Access - Show Table Structure to a Forum
 
Last edited:

AccOUCH

Registered User.
Local time
Today, 02:45
Joined
Sep 27, 2018
Messages
25
Finally, I've decided to create new forms, each one with its own SQL-generated tables. So, I can call data easily, despite the creation of more forms...
 

Users who are viewing this thread

Top Bottom