Access 2007 and Alike

[james]

yes

Dirk, I'm just not getting this. What does the ALIKE keyword look like in
an actual SQL Statement?

 

[vito]

I am using access 2007 , and they change the like with Alike , but why alike
doesn't work properly with iif(.......) if i use alike "%" by himself it
works but if it is in middle of iif(.......;alike "%";......) i don'get any
result. I am miissing something ??

 

[Douglas J. Steele]

Are you saying that you're trying to set a condition to use Alike in one
case, but not in another? You can't do that: the operator (Alike, =, <>,
whatever) must be outside of the IIf statement.

 

[Danny Lesandrini]

Doug:

Are you saying there is a new keyword in Access 2007 named "ALIKE" ?

I just haven't used Access 2007 that much yet in production to have found
new features.

 

[Douglas J. Steele]

I believe it actually exists in prior versions of Access as well, Danny. You
need to change the SQL Version option (sorry, I can't check the settings at
the moment)

 

[Danny Lesandrini]

Thanks ... I'll look into it out of curiosity, if nothing else.

 

[vito]

Are you saying that you're trying to set a condition to use Alike in one
case, but not in another? You can't do that: the operator (Alike, =, <>,
whatever) must be outside of the IIf statement.

Thanks for the reply
I found the mistake, like as you say must be outside the iff..., and for
the Alike is because i am using Ansi92 (like is still available for access) ,
I am preparing a conversion form access2003 to 2007 and I am going out of
mind.

 

[Dirk Goldgar]

I believe it actually exists in prior versions of Access as well, Danny.
You need to change the SQL Version option (sorry, I can't check the
settings at the moment)

IIRC, ALike works in Jet SQL regardless of the of the selected SQL-syntax
option. It always uses the ANSI wild-card characters ('%' and '_' instead
of '*' and '?'). Thus, it lets you write version-neutral queries.

 

[Danny Lesandrini]

Dirk, I'm just not getting this. What does the ALIKE keyword look like in
an actual SQL Statement?

 

[hor vannara]

I am using access 2007 , and they change the like with Alike , but why
alike
doesn't work properly with iif(.......) if i use alike "%" by himself it
works but if it is in middle of iif(.......;alike "%";......) i don'get
any
result. I am miissing something ??

 

[hor vannara]

I am using access 2007 , and they change the like with Alike , but why
alike
doesn't work properly with iif(.......) if i use alike "%" by himself it
works but if it is in middle of iif(.......;alike "%";......) i don'get
any
result. I am miissing something ??

 

[Dirk Goldgar]

Dirk, I'm just not getting this. What does the ALIKE keyword look like in
an actual SQL Statement?

Same as "Like", but with different wild cards. For example, this SQL
statement:

SELECT * FROM Table1 WHERE TextField ALike "_ecord %3"

.... is equivalent to this one:

SELECT * FROM Table1 WHERE TextField Like "?ecord *3"

Both would return records with TextField values of:

Record Number 3
Record Number 23
Xecord 4563

.... etc. However, the "Like" version won't work if the query is executed
using ADO (which uses ANSI 92 syntax), or if the "SQL Server Compatible
Syntax (ANSI 92) option is checked in the Tools -> Options... dialog. The
"ALike" version will work in DAO, ADO, and ANSI-syntax mode.

I imagine that the "ALike" name stands for "ANSI Like".

 

[Danny Lesandrini]

Ahhh. Got it. That makes great sense. I guess I just always knew if my
SQL call was going through ADO and wrote the WHERE clause accordingly.

 

[Dirk Goldgar]

Ahhh. Got it. That makes great sense. I guess I just always knew if my
SQL call was going through ADO and wrote the WHERE clause accordingly.

I think the only time I've used ALike was when I was writing queries in a
database that was going to be uploaded to a website and used by ASP pages.
In order for those pages -- using ADO -- to run the queries, the ANSI wild
cards had to be used. But I also wanted to be able to run the queries
directly in Access, without fooling around with the SQL-syntax option
setting.

 

[David W. Fenton]

IIRC, ALike works in Jet SQL regardless of the of the selected
SQL-syntax option.

You recall incorrectly, according to the Access help files -- ALIKE
is SQL 92 syntax only.

 

[Dirk Goldgar]

You recall incorrectly, according to the Access help files -- ALIKE
is SQL 92 syntax only.

I've since checked it, and I do recall correctly -- ALike works, using ANSI
wild-card characters, even when the "SQL Server Compatible Syntax (ANSI 92)"
option is unchecked. Could you quote the exact passage in the help file?
Maybe you're misconstruing it, or maybe it's just wrong.