Dcount Syntax Help w/Criteria

[CharlieT302]

Hi,

I need help populating a field. The field is on a subform. The following code will populate my field by combining values from various other fields.

If IsNull(Me.Tracker_Painting_Number) Or Me.Tracker_Painting_Number.Value = "" Then

Me.Tracker_Painting_Number.Value = Me.[Artist Full Last Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2)
else...

The above code works fine, however, I want to be able to check for existing records with the same values. As a test, I tried to display a message box indicating how many records have the same value. I get an error message when I run it. The criteria seems to be off even though "it appears" to be the same I used above.

Invalid Code:
************************************************************
MsgBox (DCount("Tracking_Painting_Number", "tbl_Tracker_Information_sub", "Me.Tracker_Painting_Number.Value = '" & Me.[Artist Full Last Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2) & "'"))

************************************************************

My ultimate goal is to be able to count up items with the same base number and then add a value of 1 to the count to achieve a unique value.


Help is greatly appreciated.

 

[dhookom]

The criteria in the DCount() can't have "Me."
Your expression is totally confusing. You should split out the DCount() and MsgBox() and other expressions soemthing like:

Dim strCrit as String
Dim strNum as String
strNum = Me.[Artist Full Last Name] & _
   Left(Me.[Artist Full Last Name], 1) & _
   DatePart("m", Me.Date_Painting_Started) & _
   DatePart("d", Me.Date_Painting_Started) & _
   Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & _
   Left(Me.Discipline, 2) 
strCrit = "Tracker_Painting_Number = '" & strNum & "'")
MsgBox (DCount("*", "tbl_Tracker_Information_sub", strCrit)

Ideally, you should have a small user-defined-function that has the Artist Full Last Name, Date_Painting_Started, and Discipline and returns the string value.



Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

Thank you dhookum,

I will try this.

 

[CharlieT302]

I tried the code suggested in the prior post without success. I do not understand the comment about "Me" not being accessible by Dcount. I have used "Me" with dfunctions before without any problem. The criteria suggested in the prior post also used "Me" but indirectly. Perhaps I am misunderstanding something?

Here is my latest attempt below. When I run the code, it produces a zero which is wrong as I ran the code on records that I knew had duplicates:

Dim strCriteria As String
Dim strNumber As String
strCriteria = "Tracker_Painting_Number = '" & strNumber & "'"

strNumber = Me.[Artist Full Last Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2)

MsgBox "Count is " & (DCount("Tracker_Painting_Number", "tbl_Tracker_Information_sub", strCriteria))

More Information:
The message box below, which uses the same code as the count function, will display the result properly. This is the same code that I used to populate the "Tracking Painting Number" field (from my original post), which also works fine.

MsgBox Me.[Artist Full Last Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2)

Additionaly, this code is being run on a nested subform:
Main form
Sub
Nested Sub

I know its probably something simple. Ideas?

Thanks

 

[PHV]

I'd put the strCriteria = line AFTER the strNumber = line !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

I wish it were that simple. Regardless of the order, it still shows a zero instead of the actual value...which should be 2..by the way.

As a test, I have also simplified the stnumber to look at only a single field (assuming there was a syntax error in the long code). It still is incorrect.

 

[CharlieT302]

Nevermind, it is working.

It comes down to simple user error. The first field in the code should have been [Artist First Name] instead of Artist Full Last Name. It was evaluating the wrong field.

The code structure is correct. Thanks again.