Solved

Access Query Error

Posted on 2013-12-22
10
1,858 Views
Last Modified: 2013-12-28
I have created the following query in Access 2010:
 
PARAMETERS [Entrer le mot clé / Enter Search Keyword] Text ( 255 );
 SELECT TBLteintage.*
 FROM TBLteintage
 WHERE TBLteintage.[code] & TBLteintage.[desc_fr] & TBLteintage.[desc_an] & TBLteintage.[code_source]
 LIKE "*" & [Entrer le mot clé / Enter Search Keyword] & "*";

Open in new window



However, I when I run it, I get the criteria box in which I enter the keyword and then I get the following error:
 
"The setting you entered isn't valid for this property. To see the valid settings for this property, search the Help index for the name of the property".
 
When I click on OK, I get the accurate results
 
What changes do I need to make to not get the error message

Thank you for your help

Jeanne
0
Comment
Question by:data-informatique
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39735194
Your query works perfectly for me.

It could be some mis-set property in your query's property sheet causing the error (but at this point it would only be guessing).

- Try creating a brand new query in design view
- Click 'close' in the Show Table dialog box to close the dialog box without adding any tables
- Right-click the query window and select SQL View
- Copy/Paste the SQL into the SQL Window, and try running it again

 PARAMETERS [Entrer le mot clé / Enter Search Keyword] Text ( 255 );
 SELECT TBLteintage.*
 FROM TBLteintage
 WHERE TBLteintage.[code] & TBLteintage.[desc_fr] & TBLteintage.[desc_an] & TBLteintage.[code_source]
 LIKE "*" & [Entrer le mot clé / Enter Search Keyword] & "*";

Open in new window

0
 

Author Comment

by:data-informatique
ID: 39735209
I followed your instructions and still got the error between entering the keyword and the query results.
I did a test where instead of entering a word, I entered a number as the keyword and I did not get the error and did get the accurate results
I checked the table that the query is based on and all the fields are a "text" type
Any ideas?
Thanks
Jeanne
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39735245
Try compact/repairing your database.

If that doesn't help, upload a small sample, including only the table and the query.

if necessary, mask or remove any personal or confidential information.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 39735248
Actually... try this.  You need to use single-quote delimiters for text:

PARAMETERS [Entrer le mot clé / Enter Search Keyword] Text ( 255 );
 SELECT TBLteintage.*
 FROM TBLteintage
 WHERE TBLteintage.[code] & TBLteintage.[desc_fr] & TBLteintage.[desc_an] & TBLteintage.[code_source]
 LIKE "'*" & [Entrer le mot clé / Enter Search Keyword] & "*'";

Open in new window

0
 

Author Comment

by:data-informatique
ID: 39735809
I tried both solutions and they did not work
Attached is a small sample of the database with only the tables and the query.  Please note that in the real database, the tables are in a back-end database and the query is in the front end database
Thank you for your help
Jeanne
teintage-test-be.accdb
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39735930
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39736876
You might want to consider an alternate approach -- a form with a combo box for selecting the search keyword (this eliminates possible errors when users type in a keyword).  After selecting a keyword, a subform or the form itself displays the search results.  See my Access Archon articles on Fancy Filters:

http://www.helenfeddema.com/Files/accarch129.zip

Simple Filters:

http://www.helenfeddema.com/Files/accarch160.zip

and Selecting or Filtering a form:

http://www.helenfeddema.com/Files/accarch223.zip
0
 

Author Comment

by:data-informatique
ID: 39740618
I will try the forms, however, when I first tried them, I would get an error regarding a field entitled "entrepot" like that field did not exist, but it does
I will get back to you shortly
Thank you
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39741640
Your query is fine -- there is NOTHING wrong with it.

The problem is *somewhere* in your table's design and/or relationships.

You can prove this by simply trying the following query, which should work fine against any table, but which for some reason causes this error when run against this specific table.

SELECT * FROM TBLteintage

Open in new window


I would suggest trying to rebuild your table from scratch... step by step... testing this simple query after each step until either a) your table is built so that the query does not error, or b) you isolate the field or property that is causing the error,
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39741703
Hmmm...

The issue seems to be with the PLOMB field in your table, which is defined as Text/255, but with a format property of "True/False"

Was this field defined like this for any particular reason?

You can resolve this problem in a number of different ways.  A couple that come to mind are:

1.  Remove the True/False format from the PLOMB field, and leave it as Text/255

OR

2.  Remove the True/False format, and change the field type to Boolean (Yes/No) datatype.

The True/False typed into the format property is unneeded and potentially problematic in either case.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question