Trying to add Len criteria

[vipa2000]

Please see attached image. I am using an unmatched query - displaying all those records in table IH06 where no record exists in IP18. In image 2 you can see the actual data. I need to limit records in IH06 where the characters <9 don't show. I have tried adding additional criteria columns with the Len command, but it keeps saying I have an extra ) or bounces it: The specified field functional loc# could refer to more than one table listed in the FROM clause of your SQL statement. I have specified the table! Not sure this is because i am specifying null across two same fields and trying to then apply a Len restriction? Thanks in advance.

[Welshgasman]

This should be all you need to do?

[vipa2000]

Hi, I tried that. I have attached a further image. Am i trying to query the same data field twice?

[orange]

vipa2000,

Access does not work well with fields with names containing spaces and/or special characters eg #.
May not be related to your current issue, but is known fact and best to avoid.

Can you switch to SQL view in your query; then copy the SQL and post it in the forum.

[Welshgasman]

No need to do it twice anyway, use the original field.
The display appears to have a ] at the end?

The len() should emcompass the whole name of the field, especially if prefixed with table name.

[Micron]

No need to do it twice anyway, use the original field.
The display appears to have a ] at the end?

The len() should emcompass the whole name of the field, especially if prefixed with table name.

It has a leading [ as well, so not an issue?
I agree, expression (Right, Left, Mid, whatever) does not know which table the field refers to. Another reason to not have the same field name in multiple tables. It should be FunctionalLocPK and FunctionalLocFK or some derivative of that - assuming it is a pk / fk relationship.

[vipa2000]

Hi, I did wonder about this. This is a data extract from a system called SAP so I was trying to use data as is; SAP is also unique. I think I fell foul of this before. I'll re-tweak the tables.

[vipa2000]

Hi Micron, the table is defined in the query table? The data tables are raw outputs from SAP so I am trying not to tweak columns if I can.

[vipa2000]

I have just noticed the SAP raw data contains the period symbol at the end. Access has converted these to #. I have found this - Names of fields, controls, and objects in Microsoft Access desktop databases: Can be up to 64 characters long. Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]). Can't begin with leading spaces.

[vipa2000]

Right, I have removed the period (.) and I have also removed the column called Functional Loc from table IH06 and just left the Len in. Issue remains. Perhaps to clarify the requirements here. The query (without the Len field embedded) produces a table that shows me where I have Functional locations IH06 without SAP plans IP17 (in the original screen shot I include the field - far right: it's blank). The issue I have is, I know that certain functional locations of a known size (length - 8 characters) do not have SAP plans so all I am trying to do is exclude the locations <9. Hope this make sense

[Welshgasman]

The Len() should include the table name I would have thought? Len(IH06![Functional Loc#])
The display shows IH06.[Len(.....
I have no clue how Access would interpret that?

[vipa2000]

Sorry missed table ref out - added in

[Micron]

IMO you're not getting it. Functional Loc is in both tables, so your expression/calculation does not know which table you're referring to. So
Len(IHO6.[Functional Loc#]) not

IH06.Len([Functional Loc]
AFAIC, the # is not an issue, especially considering how you ended up with it. Access would require [Field Name] wherever field contains spaces, allowed special characters, or Heaven forbid, the field name starts with a number.

EDIT - to clarify, not the issue, but not a good design choice either if the developer does it on purpose.

[vipa2000]

Bingo Micron. Thank you. Totally get what you have specified but thought I was specifying the table in the grid: see elements in green below. Does this not work for user defined queries?

[Welshgasman]

You have been asked to show the SQL generated, not the design window?