Solved

Access Query Error

Posted on 2013-12-22
10
1,834 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
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 …

735 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