Help needed with 'where syntax' in filter lookup

[Cron]

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

[J_Goddard via AccessMonster.com]

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

[Cron]

On Nov 16, 5:50 pm, "J_Goddard via AccessMonster.com" <u37558@uwe>
wrote:

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 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

[J_Goddard via AccessMonster.com]

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" <u37558@uwe>
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/For...ccess/200711/1

[Cron]

On Nov 16, 7:42 pm, "J_Goddard via AccessMonster.com" <u37558@uwe>
wrote:

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" <u37558@uwe>
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.comhttp://www.accessmonster.com/Uwe/Forums.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