Queer Query Behavior 
Author Message
 Queer Query Behavior

Does anyone here know the answer to this one?

I am trying to execute a query in VBA in which there is a call to a
user-defined function. Access complains that the function is undefined,
however, when I copy the same sql statement into the query window, it runs
flawlessly. See the two UDFs below:

Function CountDelimitersLocal(FullString As String, Optional ByVal Delimiter
As String = ",") As Integer
    Dim i As Integer

    Do Until InStr(1, FullString, Delimiter, 1) = 0
        CountDelimiters = CountDelimiters + 1
        FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1) + 1)
    Loop
End Function

Function GetFirstThreeNamesLocal(NamesString As String, Optional ByVal
Delimiter As String = ",") As String
    Dim i As Integer

    For i = 1 To 3
        GetFirstThreeNamesLocal = GetFirstThreeNamesLocal & Mid(NamesString,
1, InStr(1, NamesString, Delimiter, 1))
        NamesString = Mid(NamesString, InStr(1, NamesString, Delimiter, 1) +
1)
    Next i

    GetFirstThreeNamesLocal = Left(GetFirstThreeNamesLocal,
Len(GetFirstThreeNamesLocal) - 1)
End Function

These are the SQL statements that are complaining in VBA but not in QBSQL
window:

    Do
        db.Execute "UPDATE [brevard traffic1] AS BT SET BT.Name = BT.[Name]
& ',' WHERE CountDelimitersLocal(BT.Name,',') < 2;"
    Loop Until db.RecordsAffected = 0

    Do
        db.Execute "UPDATE [Brevard Traffic1] SET [Brevard Traffic1].Name =
GetFirstThreeNamesLocal([Name]) " _
            & "WHERE (((CountDelimitersLocal([Name]))>2));"
    Loop Until db.RecordsAffected = 0

Thanks for any - ANY suggestions!!



Sat, 29 Oct 2005 08:07:29 GMT  
 Queer Query Behavior
Jon -

I damned near killed myself in my first year of Access trying to put
functions in queries.  I finally decided it was an exercise in futility.
  IMSHO, far better to a) construct the SQL code with the variables
(functions) and execute it (DoCmd exec.SQL), or b) construct the SQL
code to produce the dataset you need ("set rst = db.openrecordset tSQL")
and then process from there, one record at a time, with your function.

I am absolutely NO expert at Access, but I'm getting more and more
distressed at the bad advice I've gotten from "experts" in their books.

Kevin

Quote:

> Does anyone here know the answer to this one?

> I am trying to execute a query in VBA in which there is a call to a
> user-defined function. Access complains that the function is undefined,
> however, when I copy the same sql statement into the query window, it runs
> flawlessly. See the two UDFs below:

> Function CountDelimitersLocal(FullString As String, Optional ByVal Delimiter
> As String = ",") As Integer
>     Dim i As Integer

>     Do Until InStr(1, FullString, Delimiter, 1) = 0
>         CountDelimiters = CountDelimiters + 1
>         FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1) + 1)
>     Loop
> End Function

> Function GetFirstThreeNamesLocal(NamesString As String, Optional ByVal
> Delimiter As String = ",") As String
>     Dim i As Integer

>     For i = 1 To 3
>         GetFirstThreeNamesLocal = GetFirstThreeNamesLocal & Mid(NamesString,
> 1, InStr(1, NamesString, Delimiter, 1))
>         NamesString = Mid(NamesString, InStr(1, NamesString, Delimiter, 1) +
> 1)
>     Next i

>     GetFirstThreeNamesLocal = Left(GetFirstThreeNamesLocal,
> Len(GetFirstThreeNamesLocal) - 1)
> End Function

> These are the SQL statements that are complaining in VBA but not in QBSQL
> window:

>     Do
>         db.Execute "UPDATE [brevard traffic1] AS BT SET BT.Name = BT.[Name]
> & ',' WHERE CountDelimitersLocal(BT.Name,',') < 2;"
>     Loop Until db.RecordsAffected = 0

>     Do
>         db.Execute "UPDATE [Brevard Traffic1] SET [Brevard Traffic1].Name =
> GetFirstThreeNamesLocal([Name]) " _
>             & "WHERE (((CountDelimitersLocal([Name]))>2));"
>     Loop Until db.RecordsAffected = 0

> Thanks for any - ANY suggestions!!



Sat, 29 Oct 2005 09:54:28 GMT  
 Queer Query Behavior
1.  Are you aware that function CountDelimitersLocal()
always return 0, i.e. default value for Integers?

Check your code/spelling carefully.  This may be one of
the problems.

2.  "Name" is a BAD choice for Field Name as just about
every object in Access has the Property "Name".  
Using "Name" as a Field Name will confuse you and give
unexpected results which are very hard to trace.

Suggest you rename the Field to something else.  In the
mean time, use square brackets for EVERY instances
of "Name" in your Query.

3. There are some differences in executing a Query in the
QBE and executing an SQL String in VBA code.  The
Expression Service which helps to resolve a lot of
references in the QBE is not available in the VBA code and
therefore, in VBA, you need to resolve the references by
yourself before passing the SQL String to JET Database
Engine.

However, fix the above problems first and try again.  If
there are still problems, post the exact error message.

HTH
Van T. Dinh
MVP (Access)

Quote:
>-----Original Message-----
>Does anyone here know the answer to this one?

>I am trying to execute a query in VBA in which there is a
call to a
>user-defined function. Access complains that the function
is undefined,
>however, when I copy the same sql statement into the

query window, it runs
Quote:
>flawlessly. See the two UDFs below:

>Function CountDelimitersLocal(FullString As String,

Optional ByVal Delimiter
Quote:
>As String = ",") As Integer
>    Dim i As Integer

>    Do Until InStr(1, FullString, Delimiter, 1) = 0
>        CountDelimiters = CountDelimiters + 1
>        FullString = Mid(FullString, InStr(1, FullString,
Delimiter, 1) + 1)
>    Loop
>End Function

>Function GetFirstThreeNamesLocal(NamesString As String,
Optional ByVal
>Delimiter As String = ",") As String
>    Dim i As Integer

>    For i = 1 To 3
>        GetFirstThreeNamesLocal = GetFirstThreeNamesLocal
& Mid(NamesString,
>1, InStr(1, NamesString, Delimiter, 1))
>        NamesString = Mid(NamesString, InStr(1,

NamesString, Delimiter, 1) +
Quote:
>1)
>    Next i

>    GetFirstThreeNamesLocal = Left

(GetFirstThreeNamesLocal,
Quote:
>Len(GetFirstThreeNamesLocal) - 1)
>End Function

>These are the SQL statements that are complaining in VBA
but not in QBSQL
>window:

>    Do
>        db.Execute "UPDATE [brevard traffic1] AS BT SET
BT.Name = BT.[Name]
>& ',' WHERE CountDelimitersLocal(BT.Name,',') < 2;"
>    Loop Until db.RecordsAffected = 0

>    Do
>        db.Execute "UPDATE [Brevard Traffic1] SET

[Brevard Traffic1].Name =

- Show quoted text -

Quote:
>GetFirstThreeNamesLocal([Name]) " _
>            & "WHERE (((CountDelimitersLocal([Name]))
>2));"
>    Loop Until db.RecordsAffected = 0

>Thanks for any - ANY suggestions!!

>.



Sat, 29 Oct 2005 11:44:43 GMT  
 Queer Query Behavior
The bad spelling only came about after I copied the code out of my library
database, after continuous failure, into the local database and the email
message window. I had done all the replacements in the local database
(replacing CountDelimiters with CountDelimitersLocal). I forgot to do all of
them as I pasted it into the email message window.

All instances of the field, 'Name', were enclosed in brackets, as it was at
the times I attempted to run the code. I normally simplify my codes by
getting rid of the variables, here so its easier for you to read. I have
made the changes that follows and the results are the same. I am not getting
any error, but the RecordsAffected property of the database object is
returning 0 when there are records that should be affected.

You said something about resolving references before passing SQL to Jet,
give me an example of how I might do that in the scenario I am posting.

Thank you

'----------------------------------- Calling Procedure
Snippet ---------------------------------------------------------
    Do
        DoCmd.SetWarnings -1
        db.Execute "UPDATE [" & strTbl & "] SET [" & strFld & "] = [" &
strFld & "] & ',' " _
            & "WHERE CountDelimiters([" & strFld & "]) < 2;"
    Loop Until db.RecordsAffected = 0

'------------------------------------- Called
Function------------------------------------------------------------------
Public Function CountDelimiters(ByVal FullString As String, Optional ByVal
Delimiter As String = ",") As Integer
    Dim i As Integer, iTemp As Integer

    Do Until InStr(1, FullString, Delimiter, 1) = 0
        iTemp = iTemp + 1
        FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1) + 1)
    Loop

    CountDelimiters = iTemp
End Function



Quote:
> 1.  Are you aware that function CountDelimitersLocal()
> always return 0, i.e. default value for Integers?

> Check your code/spelling carefully.  This may be one of
> the problems.

> 2.  "Name" is a BAD choice for Field Name as just about
> every object in Access has the Property "Name".
> Using "Name" as a Field Name will confuse you and give
> unexpected results which are very hard to trace.

> Suggest you rename the Field to something else.  In the
> mean time, use square brackets for EVERY instances
> of "Name" in your Query.

> 3. There are some differences in executing a Query in the
> QBE and executing an SQL String in VBA code.  The
> Expression Service which helps to resolve a lot of
> references in the QBE is not available in the VBA code and
> therefore, in VBA, you need to resolve the references by
> yourself before passing the SQL String to JET Database
> Engine.

> However, fix the above problems first and try again.  If
> there are still problems, post the exact error message.

> HTH
> Van T. Dinh
> MVP (Access)

> >-----Original Message-----
> >Does anyone here know the answer to this one?

> >I am trying to execute a query in VBA in which there is a
> call to a
> >user-defined function. Access complains that the function
> is undefined,
> >however, when I copy the same sql statement into the
> query window, it runs
> >flawlessly. See the two UDFs below:

> >Function CountDelimitersLocal(FullString As String,
> Optional ByVal Delimiter
> >As String = ",") As Integer
> >    Dim i As Integer

> >    Do Until InStr(1, FullString, Delimiter, 1) = 0
> >        CountDelimiters = CountDelimiters + 1
> >        FullString = Mid(FullString, InStr(1, FullString,
> Delimiter, 1) + 1)
> >    Loop
> >End Function

> >Function GetFirstThreeNamesLocal(NamesString As String,
> Optional ByVal
> >Delimiter As String = ",") As String
> >    Dim i As Integer

> >    For i = 1 To 3
> >        GetFirstThreeNamesLocal = GetFirstThreeNamesLocal
> & Mid(NamesString,
> >1, InStr(1, NamesString, Delimiter, 1))
> >        NamesString = Mid(NamesString, InStr(1,
> NamesString, Delimiter, 1) +
> >1)
> >    Next i

> >    GetFirstThreeNamesLocal = Left
> (GetFirstThreeNamesLocal,
> >Len(GetFirstThreeNamesLocal) - 1)
> >End Function

> >These are the SQL statements that are complaining in VBA
> but not in QBSQL
> >window:

> >    Do
> >        db.Execute "UPDATE [brevard traffic1] AS BT SET
> BT.Name = BT.[Name]
> >& ',' WHERE CountDelimitersLocal(BT.Name,',') < 2;"
> >    Loop Until db.RecordsAffected = 0

> >    Do
> >        db.Execute "UPDATE [Brevard Traffic1] SET
> [Brevard Traffic1].Name =
> >GetFirstThreeNamesLocal([Name]) " _
> >            & "WHERE (((CountDelimitersLocal([Name]))
> >2));"
> >    Loop Until db.RecordsAffected = 0

> >Thanks for any - ANY suggestions!!

> >.



Sun, 30 Oct 2005 10:16:37 GMT  
 Queer Query Behavior
Your suggestion (b) would also fail because I would still need to call the
UDF in the statement creating the recordset object.

I will agree with you that DoCmd is more efficient that way, except, it
doesnt return a RecordsAffected property value for me to check in order to
know when to exit the loop. I think I am going to have to find some way to
use DoCmd with its short comings.

I know exactly how you felt your first year of using access. I am very fed
up with some of it. AppActivate, for instance. According to Access Help, you
pass it the return value of SendKeys in order to activate a specific window.
But every time I use it I get an error.


Quote:
> Jon -

> I damned near killed myself in my first year of Access trying to put
> functions in queries.  I finally decided it was an exercise in futility.
>   IMSHO, far better to a) construct the SQL code with the variables
> (functions) and execute it (DoCmd exec.SQL), or b) construct the SQL
> code to produce the dataset you need ("set rst = db.openrecordset tSQL")
> and then process from there, one record at a time, with your function.

> I am absolutely NO expert at Access, but I'm getting more and more
> distressed at the bad advice I've gotten from "experts" in their books.

> Kevin


> > Does anyone here know the answer to this one?

> > I am trying to execute a query in VBA in which there is a call to a
> > user-defined function. Access complains that the function is undefined,
> > however, when I copy the same sql statement into the query window, it
runs
> > flawlessly. See the two UDFs below:

> > Function CountDelimitersLocal(FullString As String, Optional ByVal
Delimiter
> > As String = ",") As Integer
> >     Dim i As Integer

> >     Do Until InStr(1, FullString, Delimiter, 1) = 0
> >         CountDelimiters = CountDelimiters + 1
> >         FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1)
+ 1)
> >     Loop
> > End Function

> > Function GetFirstThreeNamesLocal(NamesString As String, Optional ByVal
> > Delimiter As String = ",") As String
> >     Dim i As Integer

> >     For i = 1 To 3
> >         GetFirstThreeNamesLocal = GetFirstThreeNamesLocal &
Mid(NamesString,
> > 1, InStr(1, NamesString, Delimiter, 1))
> >         NamesString = Mid(NamesString, InStr(1, NamesString, Delimiter,
1) +
> > 1)
> >     Next i

> >     GetFirstThreeNamesLocal = Left(GetFirstThreeNamesLocal,
> > Len(GetFirstThreeNamesLocal) - 1)
> > End Function

> > These are the SQL statements that are complaining in VBA but not in
QBSQL
> > window:

> >     Do
> >         db.Execute "UPDATE [brevard traffic1] AS BT SET BT.Name =
BT.[Name]
> > & ',' WHERE CountDelimitersLocal(BT.Name,',') < 2;"
> >     Loop Until db.RecordsAffected = 0

> >     Do
> >         db.Execute "UPDATE [Brevard Traffic1] SET [Brevard
Traffic1].Name =
> > GetFirstThreeNamesLocal([Name]) " _
> >             & "WHERE (((CountDelimitersLocal([Name]))>2));"
> >     Loop Until db.RecordsAffected = 0

> > Thanks for any - ANY suggestions!!



Sun, 30 Oct 2005 10:27:24 GMT  
 Queer Query Behavior
See my reply to the thread "Error in RecordsetObject" in this newsgroup
about 2 hours before this thread.

However, JET generally can resolve custom functions unlike Form references.

--
HTH
Van T. Dinh
MVP (Access)


Quote:
> The bad spelling only came about after I copied the code out of my library
> database, after continuous failure, into the local database and the email
> message window. I had done all the replacements in the local database
> (replacing CountDelimiters with CountDelimitersLocal). I forgot to do all
of
> them as I pasted it into the email message window.

> All instances of the field, 'Name', were enclosed in brackets, as it was
at
> the times I attempted to run the code. I normally simplify my codes by
> getting rid of the variables, here so its easier for you to read. I have
> made the changes that follows and the results are the same. I am not
getting
> any error, but the RecordsAffected property of the database object is
> returning 0 when there are records that should be affected.

> You said something about resolving references before passing SQL to Jet,
> give me an example of how I might do that in the scenario I am posting.

> Thank you

> '----------------------------------- Calling Procedure
> Snippet ---------------------------------------------------------
>     Do
>         DoCmd.SetWarnings -1
>         db.Execute "UPDATE [" & strTbl & "] SET [" & strFld & "] = [" &
> strFld & "] & ',' " _
>             & "WHERE CountDelimiters([" & strFld & "]) < 2;"
>     Loop Until db.RecordsAffected = 0

> '------------------------------------- Called
> Function------------------------------------------------------------------
> Public Function CountDelimiters(ByVal FullString As String, Optional ByVal
> Delimiter As String = ",") As Integer
>     Dim i As Integer, iTemp As Integer

>     Do Until InStr(1, FullString, Delimiter, 1) = 0
>         iTemp = iTemp + 1
>         FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1) +
1)
>     Loop

>     CountDelimiters = iTemp
> End Function



Mon, 31 Oct 2005 06:07:50 GMT  
 Queer Query Behavior
If that was the case, then , how is it that the same SQL statement that
fails, when passed as an argument to the Execute method of a reference to a
Database object, succeeds when passes as an argument to the RunSQL method of
the DoCmd object? Both are executed from VBA!



Quote:
> See my reply to the thread "Error in RecordsetObject" in this newsgroup
> about 2 hours before this thread.

> However, JET generally can resolve custom functions unlike Form
references.

> --
> HTH
> Van T. Dinh
> MVP (Access)



> > The bad spelling only came about after I copied the code out of my
library
> > database, after continuous failure, into the local database and the
email
> > message window. I had done all the replacements in the local database
> > (replacing CountDelimiters with CountDelimitersLocal). I forgot to do
all
> of
> > them as I pasted it into the email message window.

> > All instances of the field, 'Name', were enclosed in brackets, as it was
> at
> > the times I attempted to run the code. I normally simplify my codes by
> > getting rid of the variables, here so its easier for you to read. I have
> > made the changes that follows and the results are the same. I am not
> getting
> > any error, but the RecordsAffected property of the database object is
> > returning 0 when there are records that should be affected.

> > You said something about resolving references before passing SQL to Jet,
> > give me an example of how I might do that in the scenario I am posting.

> > Thank you

> > '----------------------------------- Calling Procedure
> > Snippet ---------------------------------------------------------
> >     Do
> >         DoCmd.SetWarnings -1
> >         db.Execute "UPDATE [" & strTbl & "] SET [" & strFld & "] = [" &
> > strFld & "] & ',' " _
> >             & "WHERE CountDelimiters([" & strFld & "]) < 2;"
> >     Loop Until db.RecordsAffected = 0

> > '------------------------------------- Called

Function------------------------------------------------------------------

- Show quoted text -

Quote:
> > Public Function CountDelimiters(ByVal FullString As String, Optional
ByVal
> > Delimiter As String = ",") As Integer
> >     Dim i As Integer, iTemp As Integer

> >     Do Until InStr(1, FullString, Delimiter, 1) = 0
> >         iTemp = iTemp + 1
> >         FullString = Mid(FullString, InStr(1, FullString, Delimiter, 1)
+
> 1)
> >     Loop

> >     CountDelimiters = iTemp
> > End Function



Mon, 31 Oct 2005 10:48:28 GMT  
 Queer Query Behavior
Like I said in the previous post, public functions generally resolve OK.  I
am not sure what happen in your case.

However for your last post, note that RunSQL is an Access Method (any DoCmd
Method belongs to Access) and the Expression Service is involved while
Execute is a DAO Method (i.e. directly to JET) "separately" from Access and
therefore the Expression Service is not involved.  Hence, for your last
post, DoCmd.RunSQL works but Db.Execute doesn't work.  The original poster
in the other thread also used a DAO (hence directly to JET) function and
therefore his code didn't work.

Sorry I wasn't clear in the other thread but the Method you used in VBA
that's is important.  If it is an Access method/function like RunSQL,
OpenQuery, DLookUp(), the Expression service is OK.  If it is a non-Access
Method, e.g. DAO Methods, the Expression Service is not there!

--
HTH
Van T. Dinh
MVP (Access)


Quote:
> If that was the case, then , how is it that the same SQL statement that
> fails, when passed as an argument to the Execute method of a reference to
a
> Database object, succeeds when passes as an argument to the RunSQL method
of
> the DoCmd object? Both are executed from VBA!



Mon, 31 Oct 2005 20:51:33 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Inconsistent Query Behavior

2. Pass-Through Query for Stored Proc Weird Behavior

3. Strange Behavior with queries in code

4. how can I build 2 queries in 1 query(VB SQL query)

5. (Q) Crosstab query-Field size-Grouping: Weird behavior... Please Help

6. Selecting from a Parameterised query in a CreateQuerydef'd query

7. function used in query repeats for each query value

8. query of a query

9. SQL Query - MAX, Grouping and Sub query

10. Query Object vs VBA Query

11. Dialog box displayed during query insert or query update

12. Make Table Query vs Append Query

 

 
Powered by phpBB® Forum Software