Access 2007 and Alike

  • Thread starter Thread starter james
  • Start date Start date
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 ??
 
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.
 
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.
 
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)
 
Douglas J. Steele said:
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.
Click to expand...
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.
 
Douglas J. Steele said:
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)
Click to expand...


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.
 
Dirk, I'm just not getting this. What does the ALIKE keyword look like in
an actual SQL Statement?
 
vito said:
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 ??
Click to expand...
 
vito said:
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 ??
Click to expand...
 
Danny Lesandrini said:
Dirk, I'm just not getting this. What does the ALIKE keyword look like in
an actual SQL Statement?
Click to expand...


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".
 
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.
 
Danny Lesandrini said:
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.
Click to expand...


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.
 
IIRC, ALike works in Jet SQL regardless of the of the selected
SQL-syntax option.
Click to expand...

You recall incorrectly, according to the Access help files -- ALIKE
is SQL 92 syntax only.
 
David W. Fenton said:
You recall incorrectly, according to the Access help files -- ALIKE
is SQL 92 syntax only.
Click to expand...


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.
 
Back
Top