Strange query behaviour in Access 2007

[Gordon]

I'm trying to create a simple query in a new Access 2007 database
using the "Like" parameter. When I type in the "Like" operator and
then a space, it automatically changes to "ALike" and of course the
query will not work.

I've tried it on a new database and it is the same, but when I did it
in an existing database, it works OK.

Am I losing it? Did I miss something in Access 2007?

Gordon

 

[strive4peace]

wildcards
~~~


Hi Gordon,

instead of letting Access correct your entry for quotes, type them in
yourself

criteria --> Like "pattern"

where
pattern is the pattern you are looking for and contains wildcards

Wildcards for ANSI-89

Wilcards give us the ability to create a pattern to look for (also
called a mask). Here are some symbols you can use in your mask:

*
Match zero or more characters; use as first or last character of a mask.

?
Match a single alphabetic character.

[charlist]
Match a single character specified in the character list within the
brackets.
[A,B,C,D] is the same as [A-D]

[!charlist]
Matches any character NOT in the character list within the brackets.

[x-y]
Match any character in a specified range from 'x' to 'y' (range must be
stated in ascending order, A-Z, 0-100)

#
Match a single numeric character.

If the data is stored in, for instance, Microsoft SQL Server instead of
Access, you will need to use ANSI-92 Wildcards.

~~~~~~~~~~~~~~~~~~~~~

pattern examples

"*word*" --> everything containing 'word'

"Start*" --> everything that starts with 'Start'

"*End" --> everything that ends with 'End'

"[a,b,c,d]?[1-9, a-c]*" -->
first character is a,b,c, or d
second character is anything
third character is 1,2,3,4,5,6,7,8, 9, a, b, or c
then, it can be anything or nothing

"??D*" --> third character is 'D'


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[strive4peace]

also, if, for some strange reason, your words are being
"auto-corrected", type your criteria into a NotePad document, then copy
and paste

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Gordon]

also, if, for some strange reason, your words are being
"auto-corrected", type your criteria into a NotePad document, then copy
and paste

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.accessmvp.com/Strive4Peace/Index.htm

  *
    have an awesome day
  *







- Show quoted text -

Hi Crystal,

THnaks for your lengthy response but it doesn't answer my question.
"Alike" is not an Access keyword, so why is Access 2007
"autocorrecting" the keyword "Like" when I type it into a query
parameter?

Gordon

 

[Tom Wickerath]

Hi Gordon,

This is expected behavior if you have the ANSI-92 SQL syntax option
selected. In Access 2000/2002/2003 this option is found here:

Tools | Options... | Tables/Queries tab (lower right corner)

I'm not using Access 2007, but I'm sure you can find it somewhere (good
luck). I'd start by clicking on that silly Office button thingy, and then
clicking on the Access Options... button. I think you will find it hidden
somewhere in that menu.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Gordon]

Hi Gordon,

This is expected behavior if you have the ANSI-92 SQL syntax option
selected. In Access 2000/2002/2003 this option is found here:

    Tools | Options... | Tables/Queries tab   (lower right corner)

I'm not using Access 2007, but I'm sure you can find it somewhere (good
luck). I'd start by clicking on that silly Office button thingy, and then
clicking on the Access Options... button. I think you will find it hidden
somewhere in that menu.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________








- Show quoted text -

Tom,

You were spot on ! I don't know how I had managed to flick that
switch. In Access 2007 it is found in Access Option\Object Designers
\Query Design. Curiously, once set for a database, there is no option
to turn it back off for that database, only for new databases.
Thanks for your help.

Gordon

 

[strive4peace]

thanks, Tom!!

Warm Regards,
Crystal

*
have an awesome day
*

 

[Tom Wickerath]

Hi Gordon,

Glad I was able to help you.

Curiously, once set for a database, there is no option
to turn it back off for that database, only for new databases.

I fired up a copy of Access 2007 tonight. I cannot reproduce this finding. I
was able to enable the ANSI-92 syntax in a test database, and then later
change back to ANSI-89 by unchecking this option.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________