DCount VBA issues (syntax)

[sly like Coyote]

I'm having a ton of trouble with the DCount method. I'm trying to get it working in VBA on a command button click; basically I'm checking to see a record exists with the same identifying info within the database already. Right now I'm saving the values as string variables and then trying to run a dCount check, and saving the DCount value as an integer just to check that it's working, like so:

Dim saveLName as String
saveLName = Me.client_Lname
IntLNameOnly = DCount("[client_LName]", "qry_Clients", "[client_Lname]='" & save_LName & "'")
Debug.Print IntLNameOnly

This works....in that it returns 0 every time, even when I know saveLName is a value already entered in the list. Ulitmately I need to be able to check combinations of these strings agains their fields (so have the criteria be saveLName = client_Lname AND saveSSN = client_SSN, etc). To make matters worse the most basic identifying element is a date field (date of birth).

The syntax for this function is making me quite testy, and I haven't been able to find any tutorial or documentationthat covers the syntax in the detail I'd like. Can anyone help me out here?

 

[John Big Booty]

What is the SQL for your query qry_Clients? It is returning client_Lname as part of it's results?

 

[ChrisO]

I would first recommend including Option Explicit at the top of each Module.

Chris.

 

[sly like Coyote]

Yes, client_Lname is included in the specified query. Right now it's not specified in a module but called in the on-click event of one of my commands. It's set as an Option Compare Database right now, the default. Switching the declaration to Option Explicit seems to do it for the simple string variable and doesn't effect the rest of my code in any way I can tell, but I'm still having issues performing the DCount operation on the date field and the multiple criteria.

I'll keep working with that, but if you can point me to any detailed documentation on the subject or provide a few examples I'd appreciate it.

 

[spikepl]

I don't get i.

Your code shown here is:

Dim saveLName as String
saveLName = Me.client_Lname
IntLNameOnly = DCount("[client_LName]", "qry_Clients", "[client_Lname]='" & save_LName & "'")
Debug.Print IntLNameOnly

So you set the value of saveLName to something, and in the DCount you have a condition using a save_LName . So what is the value of save_Lname?

 

[sly like Coyote]

I copied the code from an older version that had a typo, which I've since fixed. I have the syntax working for a single variable now, or a single field in the form, so the analog in this case would be

[/FONT]
[FONT=Georgia]iLName = DCount("[client_ID]", "qry_Clients", "[client_Lname]='" & Forms!frm_Clients!client_Lname & "'")[/FONT]
[FONT=Georgia]

where iLName is an int variable. This makes the syntax longer but it does make it easier to handle fields that aren't required and may be null. The issue I'm having is using multiple fields, like so:

[/FONT]
[FONT=Georgia]iFullName = DCount("[client_ID]", "qry_Clients", "[client_Lname]='" & Forms!frm_Clients!client_Lname & "'" And “[client_Fname]=’” & Forms!frm_Clients!client_Fname] & “’”) [/FONT]
[FONT=Georgia]

Only this gives me a type mismatch error, even though the second field is also a string type. I've been unable to use variables to do this either, and haven't been able to get the date working at all which is a major problem since the date field (a date of birth) is the only absolutely required field and the primary method of preventing or detecting duplication in combination with the other fields.

What I'm really trying to do is check to see if these things are happening:
1) The date of birth is duplicated in the DB already, so I want to prompt the user to make sure they're aware of it and still want to proceed.

2) The DoB and any one other identifying field match a record in the database, and I want to doubleplus encourage the user to check and recheck before entering

3) The DoB and any two other fields match a pre-existing record, in which case it's considered a duplicate and I want to prevent a duplicate entry and force them to get it added by bypassing the form, probably by me, in the (very unlikely) case that it's actually a unique record.

 

[spikepl]

Your
iFullName = DCount("[client_ID]", "qry_Clients", "[client_Lname]='" & Forms!frm_Clients!client_Lname & "'" And “[client_Fname]=’” & Forms!frm_Clients!client_Fname] & “’”)

contains a syntax error

The way to check for this, as in most other cases: DO NOT DO EVERYTHING AT ONCE in hope that it will work.

For your condtion, do

Dim myCondition as string

myCondition= whatever_it_is

debug.print myCondition ' to inspect the value in the Immediate window

iFullName = DCount("[client_ID]", "qry_Clients",myCondition)

 

[spikepl]

Also : "and haven't been able to get the date working " Honestly, this is one equation with many unknowns. If you have a problem, then

1. State specifically what you want to accomplish
2. Show the code
3. Say what it does -it ALWAYS does something, just not always what you intended
4. Say how the result differs from what you intended
5. Show atext of error messages, if any

 

[sly like Coyote]

That's nice. What's the error then? Telling me there is one isn't all that helpful - I know that. If I didn't would I even be here posting in a thread I made titled "DCount issues (syntax)"? And I've been up and down the web looking for solid tutorials or documentation that actually specified the syntax with no success.

As for the 'do everything at once', what you quoted was an alternative to the code I posted previously that did exactly what you suggested: created string variables for the field values. It works better in some ways (it doesn't complain if the field value is null for example, so I don't have to test every single field for null before setting the value of the variables) but it obviously gets a lot longer and the syntax is harder to understand.

The date testing I didn't specify because I'm not focused on that issue yet. I'm aware I didn't toss out a specific issue, because I'm not looking for a specific answer. I would like an example of the proper syntax, or a detailed breakdown of the syntax if possible.

 

[ChrisO]

sly like Coyote

One of the problems you are having is making too many changes from post to post and not taking it one step at a time.

Example:
In Post #1 you are using Font Verdana for the text and Courier New in the Code Box.
In Post #6 you are using Font Georgia for both the text and Code Box and you changed the size of the Font in the Code Box.
Why do that part the way through a thread?

Please re-post your original question after including Option Explicit at the top of the Module.
Please do not type code to site but copy/paste the code into a standard Font Code Box.
Also, please clarify this line:-
"This works....in that it returns 0 every time, even when I know saveLName is a value already entered in the list."

One step at a time.

Chris.

 

[spikepl]

#9 I am a bitch in that I do not fix people's syntax errors for them, but give them tools to do it themselves. Most syntax errors are simply due to sloppy work: missing closing or opening brackets, missing spaces etc. SQL, just like VBA, needs spaces to understand what you write, and all brackets must be matched. Since we jump between VBA and SQL, sometimes it is easy to get lost in quotes and double quotes and brackets, and that's why the best thing is to prepare the ENTIRE SQL-string by itself and inspect it. There is not much to it - it's simply a question of acquiring a systematic approach. Lookup the Dcount in the documentation -from there there you can drill down into whatever detail you wish. The condition-part of the Dcount is plain vanilla SQL with "WHERE" left out.

Pay attention to what is written. I suggested in the previous post that you make THE ENTIRE SQL string on its own, not just the variable or variables that participate. Then most of the syntax errors become pretty obvious. It will, in this case. And the reason that I do it is so you can see how to do it yourself, and so you do not need to come back here with simple syntax errors.

As to the date-issue - what is the point of adding superfluous fluff to your post? Focus on whatever you need help on, and leave the rest out. That would make your postings short, readable, comprehensible and efficient.

 

[sly like Coyote]

I posted about the syntax error specifically because I've already *spent* hours dicking around with the documentation, code examples of syntax, etc and have not been able to get my head around it. The syntax errors are obvious to you, because you know what it's supposed to be.

If your response to me asking you to share that info is 'pffff, look it up' my immediate reaction is to hit 'ignore' and not be talked down to any longer. If I had figured it out looking at the documentation or other sources I could find I wouldn't be here. I'm a complete beginner with both VBA and SQL; beating my head against a missing frickin' quotation mark or space for hours when you see it in thirty seconds is not helping me 'do it myself'. It's helping me say 'screw this'.

Now my coding experience comes from Comp Sci courses I took a decade ago in a different language and I'm a little cranky having spent several hours working with my least favorite aspect (unfamiliar and difficult to understand new syntax) of my very least favorite activity (coding) so I'm aware that I'm being a little sharp. You're more than welcome to point out the error or direct me to all of these great resources for basic SQL and VBA coding that will make understanding this so trivial and I've been totally missing. Otherwise, I'm not feeling all that charitable about being told I should have figured it out myself already.

 

[spikepl]

I apologize for talking down to you.

The advice I have given about how to break down your code and how to inspect the intermediate sql-strings, is still valid and pertinent to your error. Try it.

 

[sly like Coyote]

I did eventually find an example of the corrected syntax; I gave up searching the larger interwebs and searched through these forums with many, many different sets of keywords. The suggestion to break down create each long SQL string for the DCount criteria and return it for inspection so I could debug was valuable, but it wasn't getting me anywhere until I found this old thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=43295

with the corrected syntax that I got it. My current code for these checks looks like this, and seems to be working in my save button's OnClick although I'm still debugging:

'Create String variables to hold DCount test condition string arguments
Dim wFullID As String, wDoBNameSSN As String, wDoBSSN As String, wDoBFullName As String, wDoBName As String, wSSNName As String, wSSN As String, wDoB As String, wFullName As String
'Set string SQL arguments to pass to DCount criteria
Debug.Print "***DCOUNT CRITERIA STRINGS***"
wFullID = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "# AND [client_4SSN]='" & [Forms]![frm_Clients]![client_4SSN] & "' AND [client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' AND [client_FName]='" & [Forms]![frm_Clients]![client_Fname] & "'"
Debug.Print "Full ID test --> " & wFullID
wDoBNameSSN = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "# AND [client_4SSN]='" & [Forms]![frm_Clients]![client_4SSN] & "' AND ([client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' OR [client_FName]='" & [Forms]![frm_Clients]![client_Fname] & "')"
Debug.Print "First OR Last/SSN/DoB test --> " & wDoBNameSSN
wDoBSSN = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "# AND [client_4SSN]='" & [Forms]![frm_Clients]![client_4SSN] & "'"
Debug.Print "DoB/SSN test --> " & wDoBSSN
wDoBFullName = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "# AND [client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' AND [client_FName]='" & [Forms]![frm_Clients]![client_Fname] & "'"
Debug.Print "DoB/Full Name test --> " & wDoBFullName
wDoBName = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "# AND ([client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' OR [client_FName]='" & [Forms]![frm_Clients]![client_Fname] & "')"
Debug.Print "DoB/Name test --> " & wDoBName
wSSNName = "[client_4SSN]='" & [Forms]![frm_Clients]![client_4SSN] & "' AND ([client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' OR [client_Fname]='" & [Forms]![frm_Clients]![client_Fname] & "')"
Debug.Print "First OR Last/SSN test --> " & wSSNName
wSSN = "[client_4SSN]='" & [Forms]![frm_Clients]![client_4SSN] & "'"
Debug.Print "SSN test --> " & wSSN
wDoB = "[client_DoB]=#" & [Forms]![frm_Clients]![client_DoB] & "#"
Debug.Print "DoB test --> " & wDoB
wFullName = "[client_Lname]='" & [Forms]![frm_Clients]![client_Lname] & "' AND [client_Fname]='" & [Forms]![frm_Clients]![client_Fname] & "'"
Debug.Print "Full name test --> " & wFullName
'Run DCount tests using created string variables, from most stringent to least,
'Proceed accordingly to either discard/delete or save actions
If (DCount("[client_ID]", "[qry_Clients]", wFullID)) > 0 Then '@ least one record found with a complete identical match of all ID fields
    Dim FullMatchfound As Integer
    FullMatchfound = MsgBox("A client with an identical combination of name, date of birth, and partial SSN already exists in the system.", vbOKOnly, "WARNING: DUPLICATE FOUND!")
    GoTo DeleteRoutine
ElseIf (DCount("[client_ID]", "[qry_Clients]", wDoBNameSSN)) > 0 Then '@ least one record found with identical DoB, first or last name, and SSN
    Dim DoBNameSSNFound As Integer
    DoBNameSSNFound = MsgBox("A client with an identical combination of first or last name, date of birth, and partial SSN already exists in the system.", vbOKOnly, "WARNING: DUPLICATE FOUND!")
    GoTo DeleteRoutine
ElseIf (DCount("[client_ID]", "[qry_Clients]", wDoBSSN)) > 0 Then 'at least one record found with identical DoB and partial SSN
    Dim DoBSSNFound As Integer
    DoBSSNFound = MsgBox("A client with an identical date of birth and partial SSN already exists in the system.", vbOKOnly, "WARNING: DUPLICATE FOUND!")
    GoTo DeleteRoutine
ElseIf (DCount("[client_ID]", "[qry_Clients]", wDoBFullName)) > 0 Then 'at least one record found with identical first/last and DoB.
    Dim DoBFullNameFound As Integer
    DoBFullNameFound = MsgBox("A client with an identical date of birth and full name already exists in the system.", vbOKOnly, "WARNING: DUPLICATE FOUND!")
    GoTo DeleteRoutine
ElseIf (DCount("[client_ID]", "[qry_Clients]", wDoBName)) > 0 Then 'at least one record found with identical DoB and first or last name
    Dim DoBNameFound As Integer
    DoBNameFound = MsgBox("A client with an identical date of birth and first or last name already exists in the system." & vbCrLf & "Are you certain this client is not a duplicate entry?", vbYesNo, "WARNING: POSSIBLE DULPICATE!")
    If DoBNameFound = vbYes Then
        Dim DoBNameFoundConfirm As Integer
        DoBNameFoundConfirm = MsgBox("Duplicate client records may result in eventual deletion, replication, or missing data. " & vbCrLf & "Are you ABSOLUTELY SURE you want to save this client record?", vbYesNo, "WARNING: POSSIBLE DUPLICATE!")
        If DoBNameFoundConfirm = vbYes Then
            GoTo SaveRoutine
        Else
            GoTo DeleteRoutine
        End If
    Else
        GoTo DeleteRoutine
    End If
ElseIf (DCount("[client_ID]", "[qry_Clients]", wSSNName)) > 0 Then '@ least one record found with identical partial SSN and first or last name
    Dim SSNNameFound As Integer
    SSNNameFound = MsgBox("A client with an identical partial SSN and first or last name already exists in the system." & vbCrLf & "Are you certain this client is not a duplicate entry?", vbYesNo, "WARNING: POSSIBLE DULPICATE!")
    If SSNNameFound = vbYes Then
        Dim SSNNameFoundConfirm As Integer
        SSNNameFoundConfirm = MsgBox("Duplicate client records may result in eventual deletion, replication, or missing data. " & vbCrLf & "Are you ABSOLUTELY SURE you want to save this client record?", vbYesNo, "WARNING: POSSIBLE DUPLICATE!")
        If SSNNameFoundConfirm = vbYes Then
            GoTo SaveRoutine
        Else
            GoTo DeleteRoutine
        End If
    Else
        GoTo DeleteRoutine
    End If
ElseIf (DCount("[client_ID]", "[qry_Clients]", wSSN)) > 0 Then '@ least one record found with identical partial SSN
    Dim SSNFound As Integer
    SSNFound = MsgBox("A client with an identical partial SSN already exists in the system." & vbCrLf & "Are you certain this client is not a duplicate entry?", vbYesNo, "WARNING: POSSIBLE DULPICATE!")
    If SSNFound = vbYes Then
        Dim SSNFoundConfirm As Integer
        SSNFoundConfirm = MsgBox("Duplicate client records may result in eventual deletion, replication, or missing data. " & vbCrLf & "Are you ABSOLUTELY SURE you want to save this client record?", vbYesNo, "WARNING: POSSIBLE DUPLICATE!")
        If SSNFoundConfirm = vbYes Then
            GoTo SaveRoutine
        Else
            GoTo DeleteRoutine
        End If
    Else
        GoTo DeleteRoutine
    End If
ElseIf (DCount("[client_ID]", "[qry_Clients]", wDoB)) > 0 Then '@ least one record found with identical DoB
    Dim DoBFound As Integer
    DoBFound = MsgBox("A client with an identical date of birth already exists in the system." & vbCrLf & "Are you certain this client is not a duplicate entry and should be saved?", vbYesNo, "WARNING: POSSIBLE DUPLICATE!")
    If DoBFound = vbYes Then
        GoTo SaveRoutine
    Else
        GoTo DeleteRoutine
    End If
ElseIf (DCount("[client_ID]", "[qry_Clients]", wFullName)) > 0 Then '@ least one record found with identical first & last name
    Dim FullNameFound As Integer
    FullNameFound = MsgBox("A client with an identical full name already exists in the system." & vbCrLf & "Are you certain this client is not a duplicate entry and should be saved?", vbYesNo, "WARNING: POSSIBLE DUPLICATE!")
    If FullNameFound = vbYes Then
        GoTo SaveRoutine
    Else
        GoTo DeleteRoutine
    End If
Else 'no identifying field combinations were found that indicate a possible duplicate
    GoTo SaveRoutine
End If

SaveRoutine and DeleteRoutine of course either save the record or make sure the record is deleted to prevent duplication.

 

[friendlyhendie]

I too have been struggling with the DCount syntax. Finally found a concise solution that worked!

This forum won't let me post the link, but if you Google Microsoft Community Run-time error ‘2471’ - The expression you entered as a query parameter produced the following error: ‘AUD’ you should be able to find it. The last post by RonaldoOneNeil