Like Operator not working

[ANP]

Hello,
I am trying to create a query in VBA to extract data for a report.

I am using the "Like" operator for one of the variable fields.

It works correctly if you use the full value, however when using a wildcard it returns no records. Example below.

MOP - Works returning several records.
M* - returns no records.

I am getting very frustrated - I am sure its something simple.

I hope I have uploaded a screen dump of the SQL statement that is being produced.

I would appreciate any help.

ANP

 

[BlueIshDan]

Could you paste the query in code tags?

 

[pr2-eugin]

If you copy and paste the generated SQL into a normal Query window, can you get what you want?

 

[ANP]

Hi Paul,

Thanks for your quick reply.

Yes, I have tried coping the query into the SQL view of Query Design and when executed it finds 1609 records. With two different Group Names returned.

Hope this helps.

ANP

 

[pr2-eugin]

Okay as BluIshDan has requested, it would be helpful if we saw the code you are using. As I am more interested in how you are trying to "execute" this SELECT query.

 

[vbaInet]

I am using the "Like" operator for one of the variable fields.

And also, what field?

 

[ANP]

Once again thanks for the quick reply,

I have pasted the code below which generates the SQL statement.

Thanks
ANP


' Extract Service User Details
[Forms]![frmGroupAttendanceReport]![lblExtractText].Caption = "Extracting SU Data."
Set con = Application.CurrentProject.Connection
stsql = "SELECT Services.NameOfGroup, ServiceUsers.ServiceUserID, ServiceUsers.Title, ServiceUsers.Forenames, ServiceUsers.Surname, Services.ActivityDate, ServiceUsers.InActive "
stsql = stsql & "FROM (ServiceAttendeesUsers LEFT JOIN Services ON ServiceAttendeesUsers.ServiceCode = Services.ServiceID) LEFT JOIN ServiceUsers ON ServiceAttendeesUsers.ServiceUser = ServiceUsers.ServiceUserID "
stsql = stsql & "WHERE Services.NameOfGroup Like " & Grp & " And Services.ActivityDate Between #" & VBA.Format(SDate, "yyyy/mm/dd") & "# and #" & VBA.Format(EDate, "yyyy/mm/dd") & "# "
stsql = stsql & "ORDER BY Services.NameOfGroup, ServiceUsers.ServiceUserID;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stsql, con, 1 ' 1 = adOpenKeyset

VBA.MsgBox stsql

 

[BlueIshDan]

For our eyes.

SELECT 
	Services.NameOfGroup, 
	ServiceUsers.ServiceUserID, 
	ServiceUsers.Title, 
	ServiceUsers.Forenames, 
	ServiceUsers.Surname, 
	Services.ActivityDate, 
	ServiceUsers.InActive 

FROM 
	(
	ServiceAttendeesUsers 
	LEFT JOIN Services 
		ON ServiceAttendeesUsers.ServiceCode = Services.ServiceID
	)
	 LEFT JOIN ServiceUsers 
		ON ServiceAttendeesUsers.ServiceUser = ServiceUsers.ServiceUserID 

WHERE 
	Services.NameOfGroup Like " & Grp & " 
	And Services.ActivityDate Between #" & VBA.Format(SDate, "yyyy/mm/dd") & "# and #" & VBA.Format(EDate, "yyyy/mm/dd") & "# "

ORDER BY 
	Services.NameOfGroup, 
	ServiceUsers.ServiceUserID;

 

[vbaInet]

Thanks for that Dan!

Are you sure Grp variable is returning the right value? Can you show us a Debug.Print of the SQL string.

 

[ANP]

My initial post had an attached screen dump which showed an msgbox popup with the translation of the variable used to open the recordset.

I have reattached it,

Thanks
ANP

 

[namliam]

For my eyes (I prefer the actual string, sorry dan)

stsql = stsql & "FROM     (ServiceAttendeesUsers "
stsql = stsql & "LEFT JOIN Services              ON ServiceAttendeesUsers.ServiceCode = Services.ServiceID) "
stsql = stsql & "LEFT JOIN ServiceUsers          ON ServiceAttendeesUsers.ServiceUser = ServiceUsers.ServiceUserID "
stsql = stsql & "WHERE Services.NameOfGroup Like " & Grp & " "
stsql = stsql & "  And Services.ActivityDate Between #" & VBA.Format(SDate, "yyyy/mm/dd") & "# "
stsql = stsql & "                                and #" & VBA.Format(EDate, "yyyy/mm/dd") & "# "
stsql = stsql & "ORDER BY Services.NameOfGroup, ServiceUsers.ServiceUserID;"

My guess, it needs to be:

stsql = stsql & "WHERE Services.NameOfGroup Like """ & Grp & """ "

And/Or the grp actually is entered as Sat instead of Sat*

and/or I can suggest using a proper format for the dates of MM/DD/YYYY to prevent any issues on that front

 

[ANP]

Thanks for your comments namliam.

However, ref you comments re dates - I have removed the date selection and get the same results.

The screen dumps I have posted show that the quotation marks are being put around the selection string. I do this by the statement below after population the variable Grp from the user enter string on the governing form.

Grp = chr(34) & Grp & chr(34)

Thanks
ANP

 

[namliam]

I will take "What is the problem with providing partial code?" for 20 points Dan!

What is filling Grp then?

 

[ANP]

Hi Namlian,

Grp contains "M*" when the sql statement is constructed.

I have attached a screen dump including the debug Watch for Grp.

Thanks ANP

 

[ANP]

Hi Guys,

I have found the Answer.

I have to convert the * to a % !!!

All now works as it should

Many Thanks
ANP

 

[vbaInet]

It would have been helpful if you mentioned you were connecting to a SQL Server.

 

[namliam]

Or oracle

 

[vbaInet]

Or oracle

I think Access users are too scared to touch Oracle.

 

[BlueIshDan]

I think Access users are too scared to touch Oracle.

Lol XD Tried to hold laugh in at work, that didn't work... D:

 

[namliam]

I use Oracle as my main database all the time, Oracle FTW

Access didnt much like working with 10 million records per month for some reason