Use of the LIKE clause in VBA

[Art]

Hello,
I am writing a function to return a recordset
length.

Public Function RecordsetLength(StartDate As Date,
EndDate As Date) As Variant

Dim CurrDB As New ADODB.Connection
Dim abc As New ADODB.Recordset

Dim strSQL As String

Set CurrDB = CurrentProject.Connection

strSQL = "SELECT ALL CallLog.Topic, CallLog.[Call
Status],CallLog.[Internal / External] FROM CallLog WHERE
(" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and Left([Topic],3)= ""ECR"";"

abc.CursorType = adOpenKeyset
abc.LockType = adLockOptimistic
abc.Open strSQL, CurrDB, , , adOpenKeyset

abc.MoveFirst
abc.MoveLast


RecordsetLength = abc.RecordCount
End Function

This works but it does not return the correct amount of
records.It returns 150

I have a query on the same table as my function.It has the
following constraints:
LIKE "ECR*"
BETWEEN startdate AND enddate
This query returns 168.

I have tried to change the code to use the LIKE clause.
When I do so, however, it returns an empty recordset.

strSQL = "SELECT ALL CallLog.Topic, CallLog.
[Call Status],CallLog.[Internal / External] FROM CallLog
WHERE (" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and [Topic] Like ""ECR*"";"

I am probably messing something up. If someone can spot
where I went off the rails, they could make my weekend.

Thank you in advance for any efforts to help out.

Art

 

[Dirk Goldgar]

Hello,
I am writing a function to return a recordset
length.

Public Function RecordsetLength(StartDate As Date,
EndDate As Date) As Variant

Dim CurrDB As New ADODB.Connection
Dim abc As New ADODB.Recordset

Dim strSQL As String

Set CurrDB = CurrentProject.Connection

strSQL = "SELECT ALL CallLog.Topic, CallLog.[Call
Status],CallLog.[Internal / External] FROM CallLog WHERE
(" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and Left([Topic],3)= ""ECR"";"

abc.CursorType = adOpenKeyset
abc.LockType = adLockOptimistic
abc.Open strSQL, CurrDB, , , adOpenKeyset

abc.MoveFirst
abc.MoveLast


RecordsetLength = abc.RecordCount
End Function

This works but it does not return the correct amount of
records.It returns 150

I have a query on the same table as my function.It has the
following constraints:
LIKE "ECR*"
BETWEEN startdate AND enddate
This query returns 168.

I have tried to change the code to use the LIKE clause.
When I do so, however, it returns an empty recordset.

strSQL = "SELECT ALL CallLog.Topic, CallLog.
[Call Status],CallLog.[Internal / External] FROM CallLog
WHERE (" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and [Topic] Like ""ECR*"";"

I am probably messing something up. If someone can spot
where I went off the rails, they could make my weekend.

Thank you in advance for any efforts to help out.

For a SELECT statement passed via ADO, the "match anything" wild-card
character is the percent sign (%), not the asterisk (*). So change

& "#) and [Topic] Like ""ECR*"";"

to

& "#) and [Topic] Like ""ECR%"";"

 

[Art]

Dirk,
Thank you. That was the problem. I had read about
that difference but had forgotten all about it.

Have a great weekend

art

-----Original Message-----

Hello,
I am writing a function to return a recordset
length.

Public Function RecordsetLength(StartDate As Date,
EndDate As Date) As Variant

Dim CurrDB As New ADODB.Connection
Dim abc As New ADODB.Recordset

Dim strSQL As String

Set CurrDB = CurrentProject.Connection

strSQL = "SELECT ALL CallLog.Topic, CallLog.[Call
Status],CallLog.[Internal / External] FROM CallLog WHERE
(" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and Left([Topic],3)= ""ECR"";"

abc.CursorType = adOpenKeyset
abc.LockType = adLockOptimistic
abc.Open strSQL, CurrDB, , , adOpenKeyset

abc.MoveFirst
abc.MoveLast


RecordsetLength = abc.RecordCount
End Function

This works but it does not return the correct amount of
records.It returns 150

I have a query on the same table as my function.It has the
following constraints:
LIKE "ECR*"
BETWEEN startdate AND enddate
This query returns 168.

I have tried to change the code to use the LIKE clause.
When I do so, however, it returns an empty recordset.

strSQL = "SELECT ALL CallLog.Topic, CallLog.
[Call Status],CallLog.[Internal / External] FROM CallLog
WHERE (" & _
"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#) and [Topic] Like ""ECR*"";"

I am probably messing something up. If someone can spot
where I went off the rails, they could make my weekend.

Thank you in advance for any efforts to help out.

For a SELECT statement passed via ADO, the "match anything" wild-card
character is the percent sign (%), not the asterisk (*). So change

& "#) and [Topic] Like ""ECR*"";"

to

& "#) and [Topic] Like ""ECR%"";"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Bruce M. Thompson]

In addition to Dirk's suggestion, you may want to also consider that:

"[Opened Date]>#" & StartDate & _
"# And [Opened Date]<#" & EndDate _
& "#)

.... and ...

BETWEEN startdate AND enddate

.... may return more records in the latter because the "Between" operator returns
values *inclusive* of the provided limits while "><" returns values *exclusive*
of the provided limits.

 

[Chris]

I'm curious as to why you wouldn't use either DCOUNT or
COUNT SQL statement:

DCOunt("Topic","CallLog","[Opened Date] between #" &
startdate & "# AND #" & Enddate & "# AND left([Topic],3)
='ECR'")

Or
dim strSQL as string
strSQL = "Select Count(Topic) from CallLog where [Opened
Date] between #" & startdate & "# AND #" & Enddate & "#
AND left([Topic],3)='ECR'"
dim rst as new ADODB.Recordset
rst.open strsql, currentproject.connection

RecordsetLength=rst(0)


Chris