Solved

Access Query Error

Posted on 2013-12-22
10
1,880 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

695 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