[Solved] Query Does Not Recognize Field Name for Filter

[naomih]

Hello! I am a beginner with OO but have some database experience. I made an OO database with a field called "client." This field has two choices, "clark" and "wichita."

I am trying to build a query with only wichita data in the query. The query either gives me all data for both cities or only clark, with no wichita. I have tried doing this with the Wizard and in Design View. I have tried using <> 'clark' and using = 'wichita' and neither one works.

What am I doing wrong? Thanks so much for whatever help you can offer.

~naomih.

[Villeroy]

- Your operating system?
- OOo version? Menu:Help>About...
- Type of Base database? See statusbar of the main window of your database.
-

... and neither one works.

does not tell anything. Does the program crash? Do you get error messages (which)? Do you get unexpected results (what instead of what else)?

Right-click your query and open it in SQL view. Paste your query string between code-tags like this:

Code: Select all

SELECT * FROM "myTable" WHERE "client"<> 'clark'

This will tell us all about your query.

[naomih]

Hi and thanks for your help!

The operating system is Microsoft Vista.

The OO version is 2.3.1

I’m not sure what status bar to look at to find out what kind of Base database I have. The bottom status bar says Embedded Database. Then it says HSQL database engine. Is this the information you mean?

The computer does not crash or give error messages when I run the query.

If I set up the query with CLIENT = ‘wichita’ it returns zero records and there are over 100 records with the client name wichita which should be listed.

If I set up the query with CLIENT <> ‘clark’ it returns 14 clark records and no wichita records.

This is the string you are asking for (I think).

SELECT FROM "Training Evaluations Table" AS "Training Evaluations Table" WHERE ( "Training Evaluations Table"."Client" = 'wichita' )

Thank you again.

~naomi.

[Villeroy]

Yes, "Embedded HSQL" means, that the database tables are embedded in the Base document itself. You could connect Base to many types of external databases (reading data from somewhere else), which may make a difference.
Your query does not include any fields (aka columns). It would certainly raise an error.
Did you miss the asterisk * which means "all columns of that table"?

Code: Select all

SELECT * FROM "Training Evaluations Table" AS "Training Evaluations Table" WHERE ( "Training Evaluations Table"."Client" = 'wichita' )

Another reason could be that your client's name is "Wichita" actually.

If this does not help, try a simplified query:

Code: Select all

SELECT * FROM "Training Evaluations Table"

Does this work?

[naomih]

Yes, that worked! Thank you so much!