SQL Syntax

I have a table: "tCl" in MS Access 2003, In that table we have a string ("CL") that can contain data like:

ACU Blue 6PK
ACU Blue 12PK
ACU Blue Large 24PK
ACU Blue Large 6PK

For various reason the user might have a form with an entry (Me.ODBrand) of "ACU Blue" or ACU Blue Large"

Was wondering how I can retrieve the full one of these matches of "CL".

For example if the user enters ACU Blue to return either ACU Blue 6PK or ACU Blue  12PK (Preferable the larger PK but I would be happy with either) for latter data handling on the form.

Thank you.
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
You need to use the like keyword.

Where CL like "ACU Blue*"

http://www.techonthenet.com/access/queries/like.php
0
thandelAuthor Commented:
Thanks but I am always having issues with SQL statements and wouldn't like pull up both ACU Blue Large and ACU Blue if a user just had entered ACU Blue?
0
thandelAuthor Commented:
Should have clarified... issues with SQL as in proper syntax.. I am always  getting " and ' wrong.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SStoryCommented:
You said, " if the user enters ACU Blue to return either ACU Blue 6PK or ACU Blue  12PK "
So I have no idea what you really want.
You can take what the  user enters and the LIKE statement and put an asterisk for wildcards to pull up anything starting with ending with, etc.
If you want the exact string, they'd have to type the exact string, you would use no like and just do = "their string"  I don't see any other options from what I am understanding that you said.
0
thandelAuthor Commented:
OK, let me clarify if a user had ACU Blue... if I use a like it will get ACU Blue and ACU Blue Large.... I would like to retrieve ACU Blue XX PK... perhaps something in the SQL to use LEFT with the LIKE?  

Ex. if left (CL, Len(ME.ODBRAND)  type of querrry in the SQL syntax?  Not sure how to do this or if this is the best approach.
0
aikimarkCommented:
To get the items that begin with "ACU Blue " and are followed by some package size, use this version of the SQL:
Select *
From tCl
Where CL like "ACU Blue [0-9]*" 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thandelAuthor Commented:
Thanks I think that would work can you assist with the SQL syntax?
0
thandelAuthor Commented:
TEST = "SELECT * From tCl Where CL Like "Acu Blue  [0-9]"*'" does't work, improper syntax.
0
thandelAuthor Commented:
If Me.ODBrand =  "SELECT * From tCl Where CL Like "Acu Blue  [0-9]"*'"  then.....
0
thandelAuthor Commented:
Perhaps better if

sTest = "SELECT * From tCl Where CL Like Me.ODBrand &   [0-9]"*'"
Me.ODBrand = sTest
0
aikimarkCommented:
When quote characters appear inside a string literal, they must be doubled up:
TEST = "SELECT * From tCl Where CL Like ""Acu Blue  [0-9]*""" 

Open in new window

You can also do this:
TEST = "SELECT * From tCl Where CL Like " & Chr(34) & "Acu Blue  [0-9]*" & Chr(34) 

Open in new window


==============
In your application, it might look something like this:
sTest = "SELECT * From tCl Where CL Like """ & Me.ODBrand &  " [0-9]*"""
Me.ODBrand = sTest

Open in new window

0
thandelAuthor Commented:
Like this to find value from the sql statement?

    sTest = "SELECT * From tCl Where CL Like """ & Me.ODBrand & " [0-9]*"""
    DoCmd.RunSQL sTest
msgbox sTest
0
thandelAuthor Commented:
How can I pull the result from the SQL command so I can compare/process/use the results of that?
0
aikimarkCommented:
How can I pull the result from the SQL command
I do not understand your question.
0
thandelAuthor Commented:
After I execute the SQL command I need to know if it found a match or note to the criteria.  How do I know if the SQL found a match?
0
thandelAuthor Commented:
My original question was to find a match.... so I'm hoping to somehow determine after executing the SQL statement if it did find a match.
0
aikimarkCommented:
I assumed you were opening a recordset, based on this SQL.  After you open the recordset, it will contain zero or more rows.

If your recordset variable is named rs, the following may help:
rs.EOF = End Of File (either no records or you have read past the last record)
rs.BOF = Beginning Of File (either no records or you have read past the first record)
rs.RecordCount = The number of records/rows in the recordset.
0
thandelAuthor Commented:
I am not famliar with a record set or SQL statements... have very little experience with them.  Can you clarify?
0
thandelAuthor Commented:
hold it hold it..... I believe I have it from another EE solution:

    Set rs = CurrentDb.OpenRecordset("Select * from tCl where CL Like """ & Me.ODBrand & " [0-9]*""", dbOpenForwardOnly)
    With rs
        If .EOF Then 'Not found
            MsgBox "NOT FOUND"
        Else
            MsgBox "FOUND " & !CL
        End If
    End With
    Set rs = Nothing
0
aikimarkCommented:
Dim rs as Recordset
set rs = dbEngine(0)(0).Openrecordset(sTest , dbopendynaset)

Open in new window


If you are only interested in the number of rows that meet the criteria and not the actual row data, you can do the following to your SQL
sTest = "SELECT Count(*) As MatchingCount From tCl Where CL Like """ & Me.ODBrand &  " [0-9]*"""

Open in new window

Your recordset will only have one column/field
debug.print "The number of matching rows is " & rs!MatchingCount

Open in new window

0
thandelAuthor Commented:
Does this seem like a good solution?
0
aikimarkCommented:
while my preference is dbengine(0)(0), CurrentDb will do.
0
thandelAuthor Commented:
Sorry our messages crossed.
0
thandelAuthor Commented:
OK thanks for your clarification and help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.