?
Solved

Access Query Error

Posted on 2013-12-22
10
Medium Priority
?
1,983 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 43

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 1500 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

Independent Software Vendors: 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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 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