[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Syntax

Posted on 2014-07-13
24
Medium Priority
?
153 Views
Last Modified: 2014-07-13
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.
0
Comment
Question by:thandel
  • 16
  • 6
  • 2
24 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 40193025
You need to use the like keyword.

Where CL like "ACU Blue*"

http://www.techonthenet.com/access/queries/like.php
0
 

Author Comment

by:thandel
ID: 40193041
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
 

Author Comment

by:thandel
ID: 40193044
Should have clarified... issues with SQL as in proper syntax.. I am always  getting " and ' wrong.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 25

Expert Comment

by:SStory
ID: 40193045
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
 

Author Comment

by:thandel
ID: 40193148
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40193291
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
 

Author Comment

by:thandel
ID: 40193646
Thanks I think that would work can you assist with the SQL syntax?
0
 

Author Comment

by:thandel
ID: 40193655
TEST = "SELECT * From tCl Where CL Like "Acu Blue  [0-9]"*'" does't work, improper syntax.
0
 

Author Comment

by:thandel
ID: 40193656
If Me.ODBrand =  "SELECT * From tCl Where CL Like "Acu Blue  [0-9]"*'"  then.....
0
 

Author Comment

by:thandel
ID: 40193674
Perhaps better if

sTest = "SELECT * From tCl Where CL Like Me.ODBrand &   [0-9]"*'"
Me.ODBrand = sTest
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40193678
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
 

Author Comment

by:thandel
ID: 40193681
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
 

Author Comment

by:thandel
ID: 40193684
How can I pull the result from the SQL command so I can compare/process/use the results of that?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40193698
How can I pull the result from the SQL command
I do not understand your question.
0
 

Author Comment

by:thandel
ID: 40193701
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
 

Author Comment

by:thandel
ID: 40193708
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40193716
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
 

Author Comment

by:thandel
ID: 40193725
I am not famliar with a record set or SQL statements... have very little experience with them.  Can you clarify?
0
 

Author Comment

by:thandel
ID: 40193736
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40193737
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
 

Author Comment

by:thandel
ID: 40193738
Does this seem like a good solution?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40193740
while my preference is dbengine(0)(0), CurrentDb will do.
0
 

Author Comment

by:thandel
ID: 40193743
Sorry our messages crossed.
0
 

Author Comment

by:thandel
ID: 40193744
OK thanks for your clarification and help.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question