• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

SQL (Variable) using LIKE

Hello,

I want to search all records that start with the letter "A", but with a variable.

Dim strA As String
strA = "A"

'With Doesn't Work.......

sSql = "Select * FROM tbl_Vendors " _
        & "WHERE(tbl_Vendors.fld_Vendor <> NULL) " _
        & "Add tbl_Vendors.fld_Vendor Like ' strA ' " _
        & "ORDER BY fld_Vendor ASC;"

Thanks, ADawn


0
ADawn
Asked:
ADawn
1 Solution
 
mdouganCommented:
Concatenate your string variable with the SQL and make sure you put a percent sign after the string variable, but inside of the single quotes.  Also, you had "ADD tbl_Vendors" instead of "AND tbl_Vendors"

sSql = "Select * FROM tbl_Vendors " _
       & "WHERE(tbl_Vendors.fld_Vendor <> NULL) " _
       & "AND tbl_Vendors.fld_Vendor Like '" & strA & "%'" _
       & "ORDER BY fld_Vendor ASC;"
0
 
mdouganCommented:
Also, be careful of extra spaces.  Here was your code:

Like ' strA '  

You have a space between the first single quote and the strA, and then a space after - before the next single quote.  The database would be forced to try to find records that include those spaces in those positions.  So, you'd want to make sure your code looked more like:

Like 'strA'

But, again, this is going to look for an exact match on the search string, so, follow it with a percent sign if you want to match strings that "start with" your search criteria:


Like 'strA%'

I know that strA was the name of your variable, but I'm just using it here as an example search criteria.
0
 
puranik_pCommented:
If you are using ACCESS, make it
Like 'strA*'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mdouganCommented:
Later versions of Access use the percent sign (%) instead of the asterisk (*)
0
 
dboeroCommented:
I use this code with Access Databases:

" WHERE Descripcion Like '" & Text1.Text & "%' order by Descripcion"

0
 
ADawnAuthor Commented:
Great Thanks, ADawn
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now