Solved

Access Query Error

Posted on 2013-12-22
10
1,674 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 41

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now