Query to select data in one single table displays all datas in the table!Help please! (1 Viewer)

KissBambi · Nov 29, 2019

Hello everyone from Switzerland.

I created a table of job offers and when I create an SQL query for Access to return the records to me according to well-defined search criteria, Access returns all the data from the table!
Can not display only the results of this or that category of professions, same thing to display allelements ads in an interval between 2 dates! I must obligatorily use Access 2007 and I ream completely!

Would someone have a solution?
Here is the SQL code of my request

PARAMETERS [Geben Sie ein Beruf ein :] Text ( 255 );
SELECT Anzeige.Beschreibung, Anzeige.Kundenname, Anzeige.Publikationname, Anzeige.Position, Anzeige.Pensum, Anzeige.Rubrik, Anzeige.PLZ, Anzeige.Ort, Anzeige.Datum, Anzeige.Kontakt, Anzeige.Sprache, Anzeige.[E-mail], Anzeige.Webseite, Anzeige.Telefonnummer
FROM Anzeige
WHERE (((Anzeige.Publikationname) Like [Publikationname]));

vba_php · Nov 29, 2019

KissBambi said:
PARAMETERS [Geben Sie ein Beruf ein :] Text ( 255 );
SELECT Anzeige.Beschreibung, Anzeige.Kundenname, Anzeige.Publikationname, Anzeige.Position, Anzeige.Pensum, Anzeige.Rubrik, Anzeige.PLZ, Anzeige.Ort, Anzeige.Datum, Anzeige.Kontakt, Anzeige.Sprache, Anzeige.[E-mail], Anzeige.Webseite, Anzeige.Telefonnummer
FROM Anzeige
WHERE (((Anzeige.Publikationname) Like [Publikationname]));

this part of the statement:

Code:

WHERE (((Anzeige.Publikationname) Like [Publikationname]));

is what's causing the full recordset to be returned, isn't it? you are essentially asking access to return records where each record's field [Publikationname] = the same field's value! so of course you will get everything cuz that's always a true statement. I might have been working too much today so maybe my words don't make sense in this post. I might have to test your sql out to write a coherent answer for you. let me know if I'm making sense so far.

what is the criteria supposed to be for [Publikationname] in the WHERE clause? if you were to hard code a string in there, what would you write?

KissBambi · Nov 29, 2019

Hello Adam, yes you are perfectly right. Publikationname is the category of profession and the user using Database should type a string and Access should send back all records containing this string for example Baker or Cleaner.

All records about the category of profession should be sent back by Access and displayed by the user.

Same thing for all advertisements between 2 different dates, sort by the most recent date first.

I record on Access the advertisements of different employers and my colleagues should use this Database to find all job advertisements in the land in different categories of profession or to search all records between 10/14/2019 and 11/14/2019. Thank you to help me to find the good SQL query and to explain me the query!

vba_php · Nov 29, 2019

check this out....

there are many ways to do this, like always. but what I did is create 2 different popup forms that open depending on which button is clicked. you'll notice that they both have the same source. and query is needed because you want parameters. the dialogs that popup are just canned ones provided by access, but you of course change it and make your own forms. that's always better than access's crap. to get any returned records in these popup forms, you'll have to look at the table first to see what is valid data and what isn't. notice too that I used the cstr() function no dates. that really isn't necessary, as another way to do this would be to apply filters to a popup form, or even a subform for that matter. so hopefully you can use this an adopt your own creation.

KissBambi · Nov 29, 2019

Hello Adam, I am back to the Office on Monday morning in Switzerland. I hope you are not offended if I don't work at Home when I am at my place.
Anyway Thank you for your precious collaboration and for your availability I am at 7th sky I will have the possibility to make my job thank you very much for your precious help and if one day you need me tell me I will see how I can assist you too

Best reguards

Tripodi Giovanni
Switzerland

vba_php · Nov 29, 2019

KissBambi said:
if one day you need me tell me I will see how I can assist you too

that might be possible. i'll store your username in my database so i don't forget who you are. good luck with it!

KissBambi · Nov 29, 2019

Thank you Adam thank you very much you are a gift of life and a very gentle kind good person I wish you more than the best of the best in your life and to have your wings of Glory and to fly fly away

KissBambi · Dec 2, 2019

Here the new code for the query :

PARAMETERS Publikationname Text ( 255 );
SELECT Anzeige.Beschreibung, Anzeige.Kundenname, Anzeige.Publikationname, Anzeige.Position, Anzeige.Pensum, Anzeige.Rubrik, Anzeige.PLZ, Anzeige.Ort, Anzeige.Datum, Anzeige.Kontakt, Anzeige.Sprache, Anzeige.[E-mail], Anzeige.Webseite, Anzeige.Telefonnummer
FROM Anzeige
WHERE Anzeige.Publikationname LIKE [Publikationname];

The query should return the records of the single table "Anzeige" which contain the string that the user types. SO Access should analyse the field Anzeige.Publikationname if it contains the string typped by the user (the POarameters of the query).

In the example you gave me Adam it perfectly functions. I changed the operator" = " with like because Access should search a characters string in Field Anzeige.Publikationname.

Problem is that now Access don't send me back on display any records! So from all records in the table, I arrived to NO RECORDS DISPLAYED!HELP!!!:banghead:

vba_php · Dec 2, 2019

KissBambi said:
Problem is that now Access don't send me back on display any records! So from all records in the table, I arrived to NO RECORDS DISPLAYED!HELP!!!:banghead:

no problem my dear. in access, if you use LIKE without any "*" symbols attached to it, I believe that's what happens. LIKE can be used in 2 situations:

1) When you are searching for the parameter string within another string and the string in which you are searching HAS spaces in it but you do not want the query to consider those spaces when looking for your string, you would write:

Code:

SELECT field FROM table WHERE field LIKE [COLOR="red"][B]"*LiteralStringHere*"[/B][/COLOR]

However, I don't think you can write this syntax in a parameter query. So, you would have to go with option 2:

2) When you are searching for the parameter string within another string and the string in which you are searching HAS spaces in it and you DO want the query to consider those spaces when looking for your string, you would write:

Code:

SELECT field FROM table WHERE field LIKE [COLOR="red"][B]"*" & [parameter here] & "*"[/B][/COLOR]

thus, your sql is:

Code:

PARAMETERS Publikationname Text ( 255 );
SELECT Anzeige.Beschreibung, Anzeige.Kundenname, Anzeige.Publikationname, Anzeige.Position, 
Anzeige.Pensum, Anzeige.Rubrik, Anzeige.PLZ, Anzeige.Ort, Anzeige.Datum, Anzeige.Kontakt, 
Anzeige.Sprache, Anzeige.[E-mail], Anzeige.Webseite, Anzeige.Telefonnummer
FROM Anzeige
WHERE Anzeige.Publikationname LIKE [COLOR="Red"][B]"*" & [Publikationname] & "*";[/COLOR][/B]

isladogs · Dec 2, 2019

If using the LIKE operator you should include wildcards at the end of your string

Code:

WHERE Anzeige.Publikationname LIKE [Publikationname] & "*";

Or at the start

Code:

WHERE Anzeige.Publikationname LIKE "*" & [Publikationname];

Or both

Code:

WHERE Anzeige.Publikationname LIKE "*" & [Publikationname] & "*";

You should also index the search field to improve performance

EDIT
I seem to have largely repeated Adam's answer which was posted at the same time

KissBambi · Dec 2, 2019

ADam, Moderator, you are wonderful human beings! YOu are precious to me with your help you were like angels of God with me! YOu gave me joy and happyness, the problem IS SOLVED! And you explained me how to do a SQL Code query on Access Database!

A big thank you to both for the MEGA HELP I received!!

I am very grateful to you, you have helped me very effectively, and I thank you very much on behalf of all my colleagues. Thank you so much.

vba_php · Dec 2, 2019

......................

KissBambi · Dec 2, 2019

www.team-solutions.ch

Query to select data in one single table displays all datas in the table!Help please! (1 Viewer)

KissBambi

Registered User.

vba_php

Forum Troll

KissBambi

Registered User.

vba_php

Forum Troll

Attachments

KissBambi

Registered User.

vba_php

Forum Troll

KissBambi

Registered User.

KissBambi

Registered User.

vba_php

Forum Troll

Attachments

isladogs

MVP / VIP

KissBambi

Registered User.

vba_php

Forum Troll

KissBambi

Registered User.

Users who are viewing this thread