UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Third Argument In Dcount Etc. Must Be A String, Access 2010    
 
   
HowardKaplan
post Jan 27 2019, 05:48 PM
Post#1



Posts: 1
Joined: 27-January 19



I had an interesting Access 2010 problem today, one that doesn't seem to have been documented anywhere else, so I'm documenting it here.

I was trying to use the DCount function in VBA:

CODE
HowMany = DCount("*", "ShelfLabelInfo", PrintRule)


The optional third argument, the criteria, is supposed to be a string expression. In my case, PrintRule was the name of a text box on a form that was open at the time, and the text box contained the criteria I wanted. However, the function was repeatedly returning the wrong count, the total number of records in the database, as if the optional string had not been provided. When I replaced the PrintRule argument with the criteria, expressed as a literal string, I got the correct count.

Here's what was going on. When I asked VBA to show me the definition of the DCount function, it displayed the following:

CODE
Function DCount(Expr As String, Domain As String, [Criteria])


Note that the definition does not specify that the third argument is a string -- the argument type is unspecified. Since VBA, by default, passes arguments by reference instead of by value, it was passing a reference to the text box, not to its value. Presumably, something in the DCount function code does not resolve the text box to its value, instead treating the criteria as if none had been supplied.

When I changed the VBA code as follows, I got the expected result:

CODE
HowMany = DCount("*", "ShelfLabelInfo", PrintRule.Value)


The DFirst, DLookup, DMin, DMax, DLast, DSum, DVar, and DVarP functions all have the same issue: the optional criteria argument is not specified as a string in the function definition.



Go to the top of the page
 
cheekybuddha
post Jan 27 2019, 06:07 PM
Post#2


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

The criteria argument in the domain aggregate functions needs to be a valid SQL WHERE clause expression (without the 'WHERE').

E.g.
How many = DCount("*", "ShelfLabelInfo", "SomeField = '" & Me.PrintRule & "'")

If you pass Me.PrintRule as you have done, it will be evaluated to True or False depending on it's value ( Null, 0, empty string => False, everything else => True).

False will lead to no records being matched and your sum will equal 0, True will lead to all records being matched and your sum will be the same as if you omitted the criteria altogether.


Apologies, I originally read and answered your post on my phone and completely missed the point!

Re-reading on a proper screen I now see what you're trying to explain. Nice catch! thumbup.gif



d

--------------------


Regards,

David Marten
Go to the top of the page
 
PhilS
post Jan 28 2019, 05:07 AM
Post#3



Posts: 578
Joined: 26-May 15
From: The middle of Germany


QUOTE
Note that the definition does not specify that the third argument is a string -- the argument type is unspecified. Since VBA, by default, passes arguments by reference instead of by value, it was passing a reference to the text box, not to its value.

While it is correct that VBA passes arguments ByRef by default, this has nothing to do with the issue you were facing.

Value is the Default Property of a Textbox control. When used in an expression in the Access GUI (e.g. in a query) Access will resolve that expression to the default property and hence get the value of the Value-Property.
VBA will not resolve to the default property automatically (for good reason!) unless the data type of the argument forces it to. Thus it is causing the issue by passing in the textbox itself instead of its value. - This would be all the same if the argument would have been passed in ByVal.

Bottom Line: Be explicit in your code and avoid any ambiguity!

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 02:33 AM