Microsoft KB Archive/103401: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - "&" to "&") |
||
Line 96: | Line 96: | ||
If FieldName <> "*" Then | If FieldName <> "*" Then | ||
If Len(Criteria) > 0 Then | If Len(Criteria) > 0 Then | ||
Criteria = Criteria & | Criteria = Criteria & " AND " | ||
End If | End If | ||
Criteria = Criteria & | Criteria = Criteria & "[" & FieldName & "] Is Not Null" | ||
Myset.Filter = Criteria | Myset.Filter = Criteria | ||
Set Myset = Myset.CreateDynaset() | Set Myset = Myset.CreateDynaset() | ||
Line 123: | Line 123: | ||
' (note the quote (') in the data) | ' (note the quote (') in the data) | ||
' X="Mike's Diner" | ' X="Mike's Diner" | ||
' A=DRecCount("*","Clients","Name='" & | ' A=DRecCount("*","Clients","Name='" & X & "'") | ||
' | ' | ||
' Use either: | ' Use either: | ||
' X=DFix("Mike's Diner",False) | ' X=DFix("Mike's Diner",False) | ||
' Or: | ' Or: | ||
' A=DRecCount("*","Clients","Name='" & | ' A=DRecCount("*","Clients","Name='" & DFix(X,False) & "'") | ||
'------------------------------------------------------------------ | '------------------------------------------------------------------ | ||
Dim P As Integer, OldP As Integer, Q As String * 1 | Dim P As Integer, OldP As Integer, Q As String * 1 | ||
Line 141: | Line 141: | ||
Do While P > 0 | Do While P > 0 | ||
OldP = P + 2 | OldP = P + 2 | ||
T = Left$(T, P) & | T = Left$(T, P) & Q & Mid$(T, P + 1) | ||
P = InStr(OldP, T, Q) | P = InStr(OldP, T, Q) | ||
Loop | Loop | ||
Line 161: | Line 161: | ||
Instead, use: | Instead, use: | ||
DRecCount("ID","LOG","Name = '" & | DRecCount("ID","LOG","Name = '" & DFix(Name,False) & "'") | ||
</pre> | </pre> | ||
<p><br /> | <p><br /> |
Latest revision as of 12:24, 21 July 2020
Article ID: 103401
Article Last Modified on 5/6/2003
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
This article was previously published under Q103401
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
Custom domain functions can be used to provide functionality other than that provided by the standard domain functions DLookup(), DMin(), DLast(), and so on.
This article demonstrates how to write a custom domain function that is similar to the DCount() function. In addition, it demonstrates a sample function, DFix(), to overcome limitations in concatenating variables in criteria strings. Information about the limitations of custom domain functions is at the end of this article.
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic" in version 2.0.
MORE INFORMATION
The DRecCount() sample custom domain function is useful when the domain is a totals or aggregate query based on an attached SQL table. The DRecCount() custom domain function will accurately return the count of such a dynaset.
NOTE: Domain functions such as Dcount function normally with SQL attached tables when using Microsoft Access for Windows 95.
To create these examples, open a new module within Microsoft Access and add the following functions with the appropriate declaration section:
'------------------------------------- ' GLOBAL DECLARATION '------------------------------------- Option Compare Database Option Explicit Function DRecCount (FieldName, DomainName, Criteria) '--------------------------------------------------- ' Use DRecCount to return a count ' of records when the domain is a query based on a ' totals/aggregate query on an attached SQL table. '--------------------------------------------------- Dim MyDB As Database, Myset As Dynaset 'Comment for 7.0 or 97 If VarType(FieldName) <> 8 Or Len(FieldName) = 0 Then MsgBox "You Must Specify a Field name", , "DRecCount" Exit Function End If If VarType(DomainName) <> 8 Or Len(DomainName) = 0 Then MsgBox "You Must Specify a Domain name", ,"DRecCount" Exit Function End If If VarType(Criteria) <> 8 And Not IsNull(Criteria) Then MsgBox "Invalid Criteria", , "DRecCount" Exit Function End If Set MyDB = CurrentDB() Set Myset = MyDB.CreateDynaset(DomainName) If FieldName <> "*" Then If Len(Criteria) > 0 Then Criteria = Criteria & " AND " End If Criteria = Criteria & "[" & FieldName & "] Is Not Null" Myset.Filter = Criteria Set Myset = Myset.CreateDynaset() End If If Myset.EOF Then DRecCount = 0 Else Myset.MoveLast DRecCount = Myset.recordcount End If Myset.Close MyDB.Close End Function Function DFix (ByVal T, DQuote As Integer) '------------------------------------------------------------------ ' Fixes string arguments that are passed ' to Criteria in domain functions ' ' DQuote should be TRUE or -1 if Double Quotes (") delimit Criteria ' DQuote should be FALSE or 0 if Single Quotes (') delimit Criteria ' ' e.g. this gives an error ' (note the quote (') in the data) ' X="Mike's Diner" ' A=DRecCount("*","Clients","Name='" & X & "'") ' ' Use either: ' X=DFix("Mike's Diner",False) ' Or: ' A=DRecCount("*","Clients","Name='" & DFix(X,False) & "'") '------------------------------------------------------------------ Dim P As Integer, OldP As Integer, Q As String * 1 If VarType(T) = 8 Then If DQuote = 0 Then Q = "'" Else Q = """" End If P = InStr(T, Q) Do While P > 0 OldP = P + 2 T = Left$(T, P) & Q & Mid$(T, P + 1) P = InStr(OldP, T, Q) Loop End If DFix = T End Function
General Limitations to Custom Domain Functions
You cannot use Forms!FormName!ControlName or Form.ID in quotation marks. The following example is not allowed:
DRecCount("ID","LOG","Name=Form.Name") Instead, use: DRecCount("ID","LOG","Name = '" & DFix(Name,False) & "'")
Note that the DFix() function is another custom function that is demonstrated in this article.- You must always specify a criteria, even if it is "" or Null.
- Do not place field names or domain names in brackets. For example, use "First Name" instead of "[First Name]".
NOTE: DFix() can be used with other criteria strings, such as the FindRecord method, or when building custom SQL criteria.
Additional query words: modules ab
Keywords: kbhowto kbprogramming KB103401