Error Filtering on Lookup Field from a Query (1 Viewer)

[RicardWendelina]

I have a table that uses a lookup to get an item from a query. That query combines two fact tables (a category and its subcategory) in order to create a readable name for users doing the data entry/updating by joining them and concatenating the strings. The SQL checks out and the query runs fine.

When trying to filter that field in the datasheet view, it returns an error "Syntax error in query. Incomplete query clause." It only appears to do this when the stored value is the ID (which is obviously much more useful for a database). If the stored value is, for example, the concatenated string the field is filterable.

Does anyone know why that may be and how to either resolve it or build this setup in a way that works around that issue? A file that demonstrates the error is attached. Thank you!

 

[Gasman]

I can filter both columns without any issue?

 

[RicardWendelina]

I can filter both columns without any issue?
View attachment 116347
View attachment 116348

Well that's even more confusing. This is what I get:

 

[Gasman]

Accept the error, then open the table in design view and look at the Filter property.
This is mine for the StoreID

([Lookup_StoredID].[CompleteCostItem]="00.99 - Miscellaneous")

This is mine for the String

[data_Items].[String]="00.99 - Miscellaneous")

 

[raziel3]

I have a table that uses a lookup to get an item from a query.

This is poor database design though. Use forms and comboboxes to achieve this.

 

[mike60smart]

I have a table that uses a lookup to get an item from a query. That query combines two fact tables (a category and its subcategory) in order to create a readable name for users doing the data entry/updating by joining them and concatenating the strings. The SQL checks out and the query runs fine.

When trying to filter that field in the datasheet view, it returns an error "Syntax error in query. Incomplete query clause." It only appears to do this when the stored value is the ID (which is obviously much more useful for a database). If the stored value is, for example, the concatenated string the field is filterable.

Does anyone know why that may be and how to either resolve it or build this setup in a way that works around that issue? A file that demonstrates the error is attached. Thank you!

You appear to be doing Data Entry directly in the Table, using Lookup Fields to retrieve data from another table.

Google "The Evils of Lookup Fields in Access tables" to see why you should not be doing this.

 

[RicardWendelina]

Accept the error, then open the table in design view and look at the Filter property.
This is mine for the StoreID

([Lookup_StoredID].[CompleteCostItem]="00.99 - Miscellaneous")

This is mine for the String

[data_Items].[String]="00.99 - Miscellaneous")

When I filter from String, I get the same code as you see. When I filter by StoreID and accept the error, the field remains blank or unchanged if it was previously filtered by String.

When I add the code from your StoreID result and then try to filter it, I get a type mismatch message. The original type for the StoreID field was Short Text. Changing it to Number allows for the filter to execute, but the original syntax error message remains if I try to use the dropdown on the field afterwards.

So, it appears to be some kind of typing issue, although I'm not sure how to resolve that.

Full screenshot of the Property Sheet and Table Properties for the StoreID field if it's useful:

 

[Pat Hartman]

although I'm not sure how to resolve that.

Get rid of the lookup fields. They don't belong on tables. That functionality is a crutch for people who can't write queries or code.

 

[mike60smart]

When I filter from String, I get the same code as you see. When I filter by StoreID and accept the error, the field remains blank or unchanged if it was previously filtered by String.

When I add the code from your StoreID result and then try to filter it, I get a type mismatch message. The original type for the StoreID field was Short Text. Changing it to Number allows for the filter to execute, but the original syntax error message remains if I try to use the dropdown on the field afterwards.

So, it appears to be some kind of typing issue, although I'm not sure how to resolve that.

Like Gasman I get no errors when filtering

 

[Gasman]

Yes, I believe the table lookups are causing issues, but puzzled why I could filter on either column?

 

[RicardWendelina]

You appear to be doing Data Entry directly in the Table, using Lookup Fields to retrieve data from another table.

Google "The Evils of Lookup Fields in Access tables" to see why you should not be doing this.

This is the initial setup of a database for cost estimates and I'm transferring data from other sources while also integrating a new coding system for line items.

This field isn't intended to retrieve the data from another table--just to relate the data table items with the categorization facts table. The actual outputs use queries to join the tables together.

The reason for referencing the query is to provide a readable name for the codes since subcategories are often repeated or ambiguous without the category reference (e.g., "Other" doesn't mean much without knowing its parent category). To facilitate easier data entry (both in the initial phase and the future), I was using the query to join the category and subcategory tables as well as concatenate a single readable reference (i.e., category and subcategory codes with the subcategory name). I would like to store the subcategory's ID in the table, but that's where the filtering issue has appeared.

 

[RicardWendelina]

Get rid of the lookup fields. They don't belong on tables. That functionality is a crutch for people who can't write queries or code.

If you have some resources on how to build something that allows for a human readable entry for this case, then I'm all ears. But the Lookup is really just to store a foreign key in the table so they can be joined with queries for outputs.

The problem seems to be using the lookup on the query that I've made when it stores the primary key from another table...

 

[RicardWendelina]

Yes, I believe the table lookups are causing issues, but puzzled why I could filter on either column?

Yeah, I'm at a loss as to why I'm the only instance where this error seems to occur since two of you have now opened the sample file and found no issues...

I'm on Version 2409 (Build 18025.20126) if it matters.

 

[xavier.batlle]

I can filter both columns without any issue?
View attachment 116347
View attachment 116348

I can filter both columns without any issue too.

 

[Pat Hartman]

If you have some resources on how to build something that allows for a human readable entry for this case, then I'm all ears. But the Lookup is really just to store a foreign key in the table so they can be joined with queries for outputs.

When you use forms to do your data entry, combos provide the lookup so the user sees something he understands and the FK is what gets stored. Lookups are good. They just belong on forms, not tables. When you put a lookup on a table column, you obfuscate the actual stored value. You will never see it again. That may be OK but that hidden value is what the code needs to be searching on. Another example of bad practice is adding a format to a date field. Sure, your date is consistent and it never shows the time. However, the time is still there and will interfere with searching and sorting if you don't know the time element is possible.