Strange behavior Function / query!!! 1

[LuRSH]

Hi Folks,

I have the following function on a Access 2000 based system:

Function fncstrIdentifierNumber(strFuncIdentifierDate As String, strFuncIdentifierType As String) As String
On Error GoTo Err_fncstrIdentifierNumber

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim dblRecordCount As Double

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '" & strFuncIdentifierDate & strFuncIdentifierType & "*';"

Select Case rst.RecordCount
Case Is < 1
fncstrIdentifierNumber = &quot;00&quot;
Case Is < 10
fncstrIdentifierNumber = &quot;0&quot; & CStr(rst.RecordCount)
Case Else
fncstrIdentifierNumber = CStr(rst.RecordCount)
End Select

rst.Close
Set rst = Nothing

Exit_fncstrIdentifierNumber:
Exit Function

Err_fncstrIdentifierNumber:
MsgBox Err.Description
Resume Exit_fncstrIdentifierNumber

End Function


The function above is used to determine the next and a sequential number of an identifier of a transmittal sheet to be sent to the main office. As you can see the date (YYYYMMDD) and type are both parameters of the Function.

The number of identifiers of the same type in the same date determines the next one (up to 99) in the sequence.

Now the strange part (at least to me) rst.recordcount returns ZERO. Always! If I get the SQL expression and build a new query based on that expression it returns the right number of records (I tried with different days and types, always correct).

What am I missing? (Besides my mind, naturally )
Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies

 

[Junior1544]

put a break point in the middle of your code... and in the immediate window type this:

debug.print &quot;SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '&quot; & strFuncIdentifierDate & strFuncIdentifierType & &quot;*';&quot;

that's
debug.print then your sql statment...

The reason I want you to do this is that I have a feeling that your sql is a little off... But look and let us know on that...

Also... I notice this line of code:
rst.CursorType = adOpenStatic
are you doing theis against an sql server?? The connection code looks like this is staying inside of access... I know a cursor is used in SQL Server... I don't know if that's valid (or needed) to use an ADO recordset against an access db... I would try commenting that out if your not sure what it is or what it does...

(I wish i could tell you, but I was just reading a bit about SQL Server, and read a little bit about cursor's, so I realy don't know enough to help with em...)

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

You know, looking this over again, I realy think it's your sql...

specificly in your where statment...

where statment:
&quot;WHERE [fldstrTransmittalIdentifier] like '&quot; & strFuncIdentifierDate & strFuncIdentifierType & &quot;*';&quot;

this will return some thing like this:

WHERE [fldstrTransmittalIdentifier] like &quot;20020404type*&quot;;

where as it will treat the numbers and type as one field... is that what you want, or in the database is the field fldstrTransmittalIdentifier have that kind of format??

that's just my thougts...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[LuRSH]

Hi Junior,

Thanks for you trying to help but:

A) The cursor is right and can be used with ADO Access. Actually I have to establish a specific cursor because the default cursor type is adOpenForwardOnly and this default does not support RecordCount property.

B) I picked up ADO because I'm trying to speed up the process. Recounting records is already slow but being a property of a ADO record helps a lot.

C) There is no problem with the SQL expression. What is happening is you see a space where there is none! strFuncIdentifierType is a parameter to the function. There is no space between strFuncIdentifier and Type.

D) Concerning the SQL expression yet I used an AddWatch to the whole expression, exactly to be sure that it is correct (SQL is really picky). Than I copied that AddWatch value and created a new Query with that. That's why I’m sure the expression is OK and that it returns records when used out of my Function!!!

E) Anyway I'll try the PrintDebug you suggested to see what it shows compared with the same expression in a outside query. May be it brings some light to me!!!

Thanks!
Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies

 

[Junior1544]

ok, if that does bring some light great... but from what i'm gathering here now, there is a space within on of your variables... look up the fuction trim...

and try this bit of code...

rst.Open &quot;SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '&quot; & trim(strFuncIdentifierDate) & trim(strFuncIdentifierType) & &quot;*';&quot;


That should trim any spaces off of the variables...

If i'm wrong in what i'm saying, feel free to tell me

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[LuRSH]

Hi Junior,

Again, the SQL is OK! there are no spaces inside the parameters because I'm the one giving values to them:

strIdentifierDate comes from:
strIdentifierDate = CStr(DatePart(&quot;yyyy&quot;, Me.txtflddatTransmittalDate.Value)) & CStr(DatePart(&quot;m&quot;, Me.txtflddatTransmittalDate.Value)) & CStr(DatePart(&quot;d&quot;, Me.txtflddatTransmittalDate.Value))

And strIdentifierType from:
Select Case Me.cmbfldstrTransmittalType.Value
Case &quot;Invoices / Sub Pay Apps&quot;
strIdentifierType = &quot;inv-&quot;
Case &quot;Deposit Documents&quot;
strIdentifierType = &quot;doc-&quot;
Case &quot;Budget Code Only&quot;
strIdentifierType = &quot;bud-&quot;
Case &quot;Credit Card Receipts / Invoices&quot;
strIdentifierType = &quot;ccr-&quot;
Case &quot;Other&quot;
strIdentifierType = &quot;oth-&quot;
End Select

And I call my function:
Me.txtfldstrTransmittalIdentifier.Value = strIdentifierDate & strIdentifierType & fncstrIdentifierNumber(strIdentifierDate, strIdentifierType)

Thanks for your interest! Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies

 

[Junior1544]

The reason i said this is because of:
&quot;There is no problem with the SQL expression. What is happening is you see a space where there is none!&quot;

I'm sorry if i miss understood... If the sql is perfect, Then how is the data stored for this to be producing no records no matter what your passing it?? is it in the format that i had put in further up, date then this string??

20020404oth-

being that you have a dash in there, i would think it's in the begining... where as you'd then need to switch the two variables in the sql statement...

I'm sorry if i'm not being of help, this is the last post i'm going to post on this thread since i seem to be of a bother.

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[LuRSH]

Hi Junior,

You are NOT a bother I'm just frustrated with this silly SQL! And your help makes me go to my code and look it from different angles witch is always good!

Lets put this: the final SQL I got after the running code substituting the parameters is:

SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '20021127inv-*';

Which is a perfect valid SQL expression once I can just copy and use it to build a query outside my code.

-----------


Pause to test


-----------

I used left( strIdentifierType,3) which takes out the - (dash) from the end of my identifier. Now the expression is

SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '20021127inv*';

Still working outside of my code without problems and still counting zero records inside my code!AHHAHAHAHAHAH!!!!!!!

I gotta go!! Talk to you tomorrow!!!!! Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies

 

[dysphoric]

ADO doesn't recognize the asterisk (*) as a wildcard symbol. ADO uses % as the multi-character wildcard. Try changing the * in your code to %.

 

[dysphoric]

Sorry, I realized after posting that I might not have been clear. Use this as your SQL statement:

&quot;SELECT * FROM tblTransmittal WHERE [fldstrTransmittalIdentifier] like '&quot; & strFuncIdentifierDate & strFuncIdentifierType & &quot;%';&quot;

I spent several hours battling the same problem before I figured it out.

 

[LuRSH]

THANK YOU, dysphoric, THANK YOU VERY MUCH!!!!!

This %*$(&^##&^ query was driving me crazy!

So the % works like the * in a normal query... It's the multi character wildcard. Is there a specific one for single char?

BTW, you were clear enough in the first post. Thank you also for you concern in being clear! Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies

 

[dysphoric]

I believe the single character wildcard is &quot;_&quot; (underscore), but I'm not certain about that. The note I made to myself says that ADO conforms to the SQL-92 standard, so anything you use for SQL (not Jet SQL) should work.

 

[LuRSH]

Thanks again, dysphoric! You saved my week!

FYI, I marked your post as a helpful. Luciano R. Humberto
IT Specialist - Support I
The Ryan Companies