Query Design: 1) wild card (*) is not working; 2) query underlying tables no longer appear in list

[Chuck]

Win 10 + Access 365

1) For the first time since migrating to Access 365, I tried using an * as a wild card at the end of a string in the query design view grid. Couldn't be simpler, but it did not work. It did not filter the selection. I have been using Access for decades. The wildcard has never given me a problem before.

2) Underlying tables for the query I have been working with today do not appear in the list of tables anymore. If I work with the already constructed query I can add fields, but the two tables no longer appear in the list. By the way, I am seeing the list at the far right of the screen. The point is that the fields of these two particular tables are still available to me through the grid only in the previously created query (earlier today) but if I try to create a new query, these tables do not appear in the list and I am unable to access them by way of the query grid.

[Bob Fitz]

Maybe corrupted

[CJ_London]

1. suggest show the code you are using and some example data - presume the field is not a lookup field in your table?

2. suggest show a screenshot(s) to demonstrate the issue

Agree with Bob, might be corruption. Have you a) compact/repaired? b) decompiled the code if you are using VBA, c) created a new blank db and imported all objects?

[Chuck]

The first issue is solved. Having migrated from Access 2003, I did not realize that the current version of Access (when presenting the data sources available to a query) distinguishes between linked tables and tables that are not linked. In my case, I have a front end and backend setup, the backend being where relevant data resides. Once I switched to the "Linked" tab the tables I wanted to use became available. I am guessing that the default position must have been at "All" previously and somehow I inadvertently changed it to "Tables". I hope that other newbies coming from older versions of Access benefit from my mistake.

[CJ_London]

The first issue is solved

that's the second issue isn't it?

[Chuck]

Yes, you are correct. It's the second issue.

The first issue remains unsolved. Here is the SQL:

SELECT properties.TST
FROM properties
WHERE (((properties.TST)="bloor street*"));

Table name: properties
Field name: tst (as in "target street")
The full name of the particular street is: "Bloor Street West".

The query produces no results. (Entering the full name of the street produces very many results.)

[CJ_London]

you need

WHERE (((properties.TST) Like "bloor street*"));

[Chuck]

you need

WHERE (((properties.TST) Like "bloor street*"));

Thank you for the suggestion, but this does not help. The query does not produce any results.

[CJ_London]

when using wild cards, you need to use like.

presumes this is DAO and not ansi92 or sql server? If so you use % instead of * and sql srver is case sensitive

it may be your 'bloor street west' record has a hidden preceding character

try

WHERE (((properties.TST) Like "*bloor street*"));

[Chuck]

Thanks. Tried it and it does not work. There is something very quirky going on. This is a query that would work in Access 2003. It's a very basic query.

[CJ_London]

and regards ansi92 etc - have you checked?

'Does not work' tells me nothing

[Chuck]

I get it now. You are absolutely right. I guess Access 365 is set to Ansi 92 by default, in which case "%" is the wild card, not "*".

This SQL works:

SELECT properties.TST
FROM properties
WHERE (((properties.TST) ALike "bloor%"));

Thanks very much!!

[CJ_London]

Surprised if it is a default, it's a throwback from 20 odd years ago that never went anywhere. If you are working purely with access backends, you might want to change back to ansi-89. I experimented with it way back then and came to the conclusion there were no significant benefits and a number of drawbacks. The main one being that sql server has superceded ansi-92 by a long way and many of the basic actions such as 'case when' rather than 'iif(' don't work.

see this link which will give you an idea of the differences https://documentation.help/MS-Office...ISQLSyntax.htm

[Chuck]

I did not make any changes to the ANSI setting.
To be clear, what ANSI setting do you recommend and how can I change the ANSI setting from one standard to the other? I am wondering whether this setting is specific to ACCESS or did I acquire the current setting when I upgraded to Office 365.
Thank you for your assistance on this.

[CJ_London]

what ANSI setting do you recommend and how can I change the ANSI setting from one standard to the other?

not ansi 92

go to file>options>object designers - you will find the setting under query design - both boxes should be unticked

you might want to update your details - they say you are using 2003