Like Query Question

[Ken Kazinski]

I am programming a query with the string. I started in the query editor

SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes
FROM (tblMSC_ClassID INNER JOIN tblUnits ON tblMSC_ClassID.ID =
tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON tblUnits.UnitID =
tblAnnex03_Notes.UnitID
WHERE (((tblMSC_ClassID.GroupName) Like "skiff*"));

and converted the query editor to a string to be used in my VB module.

strSql = "SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes"
strSql = strSql & " FROM (tblMSC_ClassID INNER JOIN tblUnits ON
tblMSC_ClassID.ID = tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON
tblUnits.UnitID = tblAnnex03_Notes.UnitID"
strSql = strSql & " WHERE (((tblMSC_ClassID.GroupName) = " & Chr$(34) &
"skiff*" & Chr$(34) & "))"

The query from the query editor returns records but the query using VB did
not.

Any help would be great.

 

[fredg]

I am programming a query with the string. I started in the query editor

SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes
FROM (tblMSC_ClassID INNER JOIN tblUnits ON tblMSC_ClassID.ID =
tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON tblUnits.UnitID =
tblAnnex03_Notes.UnitID
WHERE (((tblMSC_ClassID.GroupName) Like "skiff*"));

and converted the query editor to a string to be used in my VB module.

strSql = "SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes"
strSql = strSql & " FROM (tblMSC_ClassID INNER JOIN tblUnits ON
tblMSC_ClassID.ID = tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON
tblUnits.UnitID = tblAnnex03_Notes.UnitID"
strSql = strSql & " WHERE (((tblMSC_ClassID.GroupName) = " & Chr$(34) &
"skiff*" & Chr$(34) & "))"

The query from the query editor returns records but the query using VB did
not.

Any help would be great.

Why did you change Like to = ?
And you don't need all of those parenthesis.

strSql = strSql & " WHERE tblMSC_ClassID.GroupName Like 'skiff*';"

 

[Duane Hookom]

What happened to the " Like " in your strSQL. I now see " = ".

 

[Ken Kazinski]

Sorry copied a piece of test code:

From the Query Editor:
SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes
FROM (tblMSC_ClassID INNER JOIN tblUnits ON tblMSC_ClassID.ID =
tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON tblUnits.UnitID =
tblAnnex03_Notes.UnitID
WHERE (((tblMSC_ClassID.GroupName) Like "skiff*"));

This query works when I run it in.

Then I converted the code to be used in my VB module:
strSql = "SELECT tblAnnex03_Notes.UnitID, tblAnnex03_Notes.Notes"
strSql = strSql & " FROM (tblMSC_ClassID INNER JOIN tblUnits ON
tblMSC_ClassID.ID = tblUnits.ClassID) LEFT JOIN tblAnnex03_Notes ON
tblUnits.UnitID = tblAnnex03_Notes.UnitID"
strSql = strSql & " WHERE (((tblMSC_ClassID.GroupName) Like " & Chr$(34) &
"skiff*" & Chr$(34) & "))"

This query returns nothing.

Any help would be appreciated.

 

[Ken Kazinski]

I experimented with removing the "like skiff* with "= Skiffs" and the query
worked. The reason I need the like command is that I have multiple
"Skiff..." in the field.