asked on

MS Access VBA: I can't get WHERE LIKE clause (SQL) to work, and can't thus get one field's choice to change rowsource of downstream field

Background:
* I'm using Access 2010, VBA, and SQL linked to a SQL Server database.
* I want the choice made in an "upstream" field Clustered_Or_Standalone drop-down to change the choices available in the "downstream" field's Clusters_Nr_Needed drop-down Rowsource.
* I have changed the Clustered_Or_Standalone "After Update" event to issue a SQL query that is to update the Rowsource for Clusters_Nr_Needed using a WHERE LIKE clause that has the Clustered_Or_Standalone field's choice.

Problem:
* I can't get the WHERE LIKE clause to work using the '%' or '*' wildcard.

Rajeev

I think that the Selected_Str is going to have some number like 1/2/3.
And this is no way going to match with the text of Clustered_Or_Standalone.

You should use something like DISPLAYVALUE instead of VALUE for storing in Selected_Str and then try to match. That would work.

stuckp1

ASKER

I used the VBA debugger's Watch

SQL_Str = "SELECT Clusters_Nr_Needed FROM [dbo_Clusters_Nr_Needed] where [Clustered_Or_Standalone] LIKE '*Clustered*'"

Selected_Str = "Clustered"

The SQL_Str looks OK -- does one use single-quote or double-quote?

Peter

Rajeev

VBA uses double quote...

Rajeev

I mean MS Access uses double quote.

ASKER CERTIFIED SOLUTION

stuckp1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stuckp1

ASKER

I'm still puzzled why the Access Form Designer for ComboBox gets affected by having another than the displayed field be a Primary Key. But the problem is solved.