Help needed with "where syntax" in filter lookup

Hi can someone give me a hand with this please?
I'm trying to build a search filter that scans through a list of
client names in a database as you type into a text box and filters the
form records accordingly. The problem is my database structure is
making it difficult: I need the filter to first lookup the partially
inputted name in the clients table and then filter my invoices
according to the clientID from that table. Here's what I have so far
but it's not working:

Private Sub lookup_AfterUpdate()
DoCmd.ApplyFilter , "ClientID=3D(SELECT ClientID from clients WHERE
CompanyName LIKE '%" & lookup.Value & "%')"
End Sub

Thanks
Ciar=E1n

Re: Help needed with "where syntax" in filter lookup

Hi -

First, the after update event does not fire after every keystroke as you are
typing in the textbox; it only fires after the control loses the focus.

Your "WHERE" should be "ClientID IN (Select .....)

Can I ask what the % signs are for?

Why do you want to refilter the form after every keystroke? How would you
know what records the form was going to display?

HTH

John


Cron wrote:
>Hi can someone give me a hand with this please?
>I'm trying to build a search filter that scans through a list of
>client names in a database as you type into a text box and filters the
>form records accordingly. The problem is my database structure is
>making it difficult: I need the filter to first lookup the partially
>inputted name in the clients table and then filter my invoices
>according to the clientID from that table. Here's what I have so far
>but it's not working:
>
>Private Sub lookup_AfterUpdate()
>DoCmd.ApplyFilter , "ClientID=(SELECT ClientID from clients WHERE
>CompanyName LIKE '%" & lookup.Value & "%')"
>End Sub
>
>Thanks
>Ciarán

--
Message posted via http://www.accessmonster.com

Re: Help needed with "where syntax" in filter lookup

On Nov 16, 5:50 pm, "J_Goddard via AccessMonster.com"
wrote:
> Hi -
>
> First, the after update event does not fire after every keystroke as you a=
re
> typing in the textbox; it only fires after the control loses the focus.
>
> Your "WHERE" should be "ClientID IN (Select .....)
>
> Can I ask what the % signs are for?
>
> Why do you want to refilter the form after every keystroke? How would you
> know what records the form was going to display?
>
> HTH
>
> John
>
>
>
> Cron wrote:
> >Hi can someone give me a hand with this please?
> >I'm trying to build a search filter that scans through a list of
> >client names in a database as you type into a text box and filters the
> >form records accordingly. The problem is my database structure is
> >making it difficult: I need the filter to first lookup the partially
> >inputted name in the clients table and then filter my invoices
> >according to the clientID from that table. Here's what I have so far
> >but it's not working:
>
> >Private Sub lookup_AfterUpdate()
> >DoCmd.ApplyFilter , "ClientID=3D(SELECT ClientID from clients WHERE
> >CompanyName LIKE '%" & lookup.Value & "%')"
> >End Sub
>
> >Thanks
> >Ciar=E1n
>
> --
> Message posted viahttp://www.accessmonster.com

Hi John,
Thanks for the response - i actually only have the after update event
on for testing - I was planning to switch to on key up later.

I want it to filter the records in realtime so you only need enter the
first few letters of a client name to get what you're looking for.

The % sign is a wildcard character in SQL is it not?

Thanks again,
Ciar=E1n

Re: Help needed with "where syntax" in filter lookup

The SQL wildcard character is *, not %. I just tried select * where .. like
"%A%" - I didn't get an error, but I didn't get any data either!

Also, you have your wildcard on both ends of your search value, which means
it will find records with the string *anywhere* in the company name, not just
at the beginning.

HTH

John


Cron wrote:
>On Nov 16, 5:50 pm, "J_Goddard via AccessMonster.com"
>wrote:
>> Hi -
>>
>[quoted text clipped - 31 lines]
>> --
>> Message posted viahttp://www.accessmonster.com
>
>Hi John,
>Thanks for the response - i actually only have the after update event
>on for testing - I was planning to switch to on key up later.
>
>I want it to filter the records in realtime so you only need enter the
>first few letters of a client name to get what you're looking for.
>
>The % sign is a wildcard character in SQL is it not?
>
>Thanks again,
>Ciarán

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200711/1

Re: Help needed with "where syntax" in filter lookup

On Nov 16, 7:42 pm, "J_Goddard via AccessMonster.com"
wrote:
> The SQL wildcard character is *, not %. I just tried select * where .. li=
ke
> "%A%" - I didn't get an error, but I didn't get any data either!
>
> Also, you have your wildcard on both ends of your search value, which mean=
s
> it will find records with the string *anywhere* in the company name, not j=
ust
> at the beginning.
>
> HTH
>
> John
>
>
>
> Cron wrote:
> >On Nov 16, 5:50 pm, "J_Goddard via AccessMonster.com"
> >wrote:
> >> Hi -
>
> >[quoted text clipped - 31 lines]
> >> --
> >> Message posted viahttp://www.accessmonster.com
>
> >Hi John,
> >Thanks for the response - i actually only have the after update event
> >on for testing - I was planning to switch to on key up later.
>
> >I want it to filter the records in realtime so you only need enter the
> >first few letters of a client name to get what you're looking for.
>
> >The % sign is a wildcard character in SQL is it not?
>
> >Thanks again,
> >Ciar=E1n
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forum=
s.aspx/databases-ms-access/2007...


Thanks John - i figured that out the hard way too. Apparantly, Access
uses the * character while most other SQL clients use the %.
My final working syntax if anyone wants it is:

Private Sub lookup_KeyUp(KeyCode As Integer, Shift As Integer)
DoCmd.ApplyFilter , "ClientID IN (SELECT ClientID from clients WHERE
CompanyName LIKE '" & lookup.Value & "*')"
End Sub