Using Like/ALike with "*" at the end of a filter dos not longer wo

[Guest]

I have always used "Like" to filter records in queryes, but Like changes to
ALike and it does not work the same way as before. For instance if I have
made a selection form to a report then in the report query I could refer to
these fields in the "where" statement. By adding "*" at the end of the
statemend i whould recieve all records when the no data had been entered in
the selection form.

For instance: ALike [forms]![myForm]![myField] & "*"

This filter should return data equal to myfield and all records when myField
is empty. Does anywone know a solution to this problem? Please respond!

 

[John Vinson]

I have always used "Like" to filter records in queryes, but Like changes to
ALike and it does not work the same way as before. For instance if I have
made a selection form to a report then in the report query I could refer to
these fields in the "where" statement. By adding "*" at the end of the
statemend i whould recieve all records when the no data had been entered in
the selection form.

For instance: ALike [forms]![myForm]![myField] & "*"

This filter should return data equal to myfield and all records when myField
is empty. Does anywone know a solution to this problem? Please respond!

Sounds like you've gotten autocorrection set inappropriately. Select
Tools... Options... Spelling... AutoCorrect Options and either remove
"Alike" as a correction for "Like", or (better, IMHO) uncheck the
"replace text as you type" option so it doesn't try to "help" you
spell.

I just answered a similar question in this newsgroup, with a different
From - not sure if it was you; if not it's an odd coincidence!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks! But I have already tryed that and it did not work. My Autocorrect is
on Norwegian and "Like" is not on the list. PS! "Alike" works but ignores the
"*" character(the source is a number field but the "*" should work as any
number, any true / false value and any string). Any other suggestion?

John Vinson skrev:

I have always used "Like" to filter records in queryes, but Like changes to
ALike and it does not work the same way as before. For instance if I have
made a selection form to a report then in the report query I could refer to
these fields in the "where" statement. By adding "*" at the end of the
statemend i whould recieve all records when the no data had been entered in
the selection form.

For instance: ALike [forms]![myForm]![myField] & "*"

This filter should return data equal to myfield and all records when myField
is empty. Does anywone know a solution to this problem? Please respond!

Sounds like you've gotten autocorrection set inappropriately. Select
Tools... Options... Spelling... AutoCorrect Options and either remove
"Alike" as a correction for "Like", or (better, IMHO) uncheck the
"replace text as you type" option so it doesn't try to "help" you
spell.

I just answered a similar question in this newsgroup, with a different
From - not sure if it was you; if not it's an odd coincidence!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks! But I have already tryed that and it did not work. My Autocorrect is
on Norwegian and "Like" is not on the list. PS! "Alike" works but ignores the
"*" character(the source is a number field but the "*" should work as any
number, any true / false value and any string). Any other suggestion?

I'm baffled! Alike is not a recognized keyword in (English) ACCESS SQL
to my knowledge, and I've never seen Access change what's entered into
SQL. I'll ask my colleagues to look in on this thread and see if
anyone has heard of this problem.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks! But I have already tryed that and it did not work. My Autocorrect is
on Norwegian and "Like" is not on the list. PS! "Alike" works but ignores the
"*" character(the source is a number field but the "*" should work as any
number, any true / false value and any string). Any other suggestion?

Please post the actual SQL of your query, as well. If I see anything
there I might be able to help, if not, another MVP may see something I
missed.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Sylvain Lafontaine]

Can you make the test with a field of character type instead of number?

S. L.

Thanks! But I have already tryed that and it did not work. My Autocorrect
is
on Norwegian and "Like" is not on the list. PS! "Alike" works but ignores
the
"*" character(the source is a number field but the "*" should work as any
number, any true / false value and any string). Any other suggestion?

John Vinson skrev:

I have always used "Like" to filter records in queryes, but Like changes
to
ALike and it does not work the same way as before. For instance if I
have
made a selection form to a report then in the report query I could refer
to
these fields in the "where" statement. By adding "*" at the end of the
statemend i whould recieve all records when the no data had been entered
in
the selection form.

For instance: ALike [forms]![myForm]![myField] & "*"

This filter should return data equal to myfield and all records when
myField
is empty. Does anywone know a solution to this problem? Please respond!

Sounds like you've gotten autocorrection set inappropriately. Select
Tools... Options... Spelling... AutoCorrect Options and either remove
"Alike" as a correction for "Like", or (better, IMHO) uncheck the
"replace text as you type" option so it doesn't try to "help" you
spell.

I just answered a similar question in this newsgroup, with a different
From - not sure if it was you; if not it's an odd coincidence!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Allen Browne]

The ALike operator is undocumented AFAIK. It behaves like the Like in SQL
Server, e.g. it recognizes % instead of * as the wildcard.

Jan, do you have the box checked under:
Tools | Options | Tables/Queries | SQL Server Compatible Syntax (ANSI 92).

 

[Guest]

Thanks! The "%" wildcard seem to work the same way with ALike as "*" do with
Like.

Allen Browne skrev:

 

[Guest]

Thanks for replying! The tips from Allen Browne seem to work in this problem.
Se the SQl example below:

SELECT tblSysRapport.Rapportbeskrivelse, tblSysRapport.Rapportnavn
FROM tblSysRapport
WHERE (((tblSysRapport.Kilde) ALike [forms]![frmRapport]![Kilde] & "%"));

This select statement will filter records only when a string is entered in
the [frmRapport][Kilde] - field. If its left blan it will return records
where the record source tblSysRapport.Kilde also also is blank. So it dos not
work exactly the same way as "*" but it did solve this spesific problem.

Here is how I solved a simular problem in a more messy way:

SELECT tblObjektRegister.*,
IIf(Forms!frmObjektRegister!F_Lokalisjon,[f_LokalisjonID],-1) AS Filter1
FROM tblObjektRegister
WHERE
(((IIf([Forms]![frmObjektRegister]![F_Lokalisjon],[f_LokalisjonID],-1)) ALike
IIf([Forms]![frmObjektRegister]![F_Lokalisjon],[Forms]![frmObjektRegister]![F_Lokalisjon],-1)));

In this SQL I made a "Dummy" field (Filter1) and added the value -1 if
[Forms]![frmObjektRegister]![F_Lokalisjon] was empty and then filtered on -1
so all record will show. When a value in F_Lokalisjon is entered then the
filter will work ordinary. This metod works but is messy.



John Vinson skrev: