Using Dcount

[Eljefegeneo]

I have two fields in a table, one is RecordID, the primary Key and the other is SequenceNumber. A SequenceNumber can appear one or two times. If there are two entries for the same SequenceNumber, then I want to know the larger number of RecordID and the smaller number. What I want to do is if I enter an new record with the same SequenceNumber, I can update two T/F fields on the smaller RecordID number and one on the larger RecordID number.
So first I tried to figure out are there were more than two records with the same SequenceNumber. I tried the following in an unbound text box a form to see what code would work for me:

  = IIF(DCount("[SequenceNumber]", "tblRegularInvoices2", " SequenceNumber = " & Me!SequenceNumber) >=2, True, False)

But I get the #Name? error

Then I tried :

  = IIF(DCount("*", "tblRegularInvoices2", " SequenceNumber = " & Me!SequenceNumber) >=2, True, False)

But I get the same error message. In both cases, when I paste the code into the control source, go back to the form and see the error #Name?, the code reverts to [Me]![SequenceNumber] after the "&" in the code and I am wondering why and if this is the problem and what am I doing wrong.

 

[Eljefegeneo]

Why, as soon as I post something I figure it out.

I tried:

 = IIF(DCount("[SequenceNumber]", "tblRegularInvoices2", " SequenceNumber = " & [SequenceNumber]) >=2, True, False)

And now get the answer I want.

Now, all I have to figure out is the second part.

What I want to do is if I enter an new record with the same SequenceNumber, I can update two T/F fields on the smaller RecordID number and one on the larger RecordID number.

Any suggestions?

 

[plog]

I mean, your 90% there. You clearly xpressed your logic in English, now you just need to convert it to code. And you've already demonstrated you're comfortable with the IIF statement. What help do you need exactly?

Here's the broad strokes:

"When I enter a new record"--you must research which VBA event that gets triggered when you do that -- https://msdn.microsoft.com/en-us/library/office/jj249049.aspx -- and put your code there.

"I can update..."--in SQL you use an UPDATE statement to change data in existing records-- https://www.w3schools.com/sql/sql_update.asp -- and in VBA to execute SQL you use the DoCmd.RunSQL method -- https://msdn.microsoft.com/en-us/library/office/ff194626.aspx

Hopefully that's enough of a push. If not, let me know what specifically you run up against.

 

[Eljefegeneo]

Finally had a chance to work on this and I thought I was doing everything correctly but I am getting a Error 3144 Syntax Error in Update Statement:

Dim SQL, SQL1 As String
Dim DMinA, DMaxA As Long
DMinA = DMin("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])
DMaxA = DMax("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])

SQL = "UPDATE tblRebularInvoices2" & "," & " " & _
"Set CheckNextMonth = True" & "," & " " & _
"SpecialBilling = True" & "," & " " & _
"Remove = True" & "," & " " & _
"Where RecordID = DMinA"

SQL1 = "UPDATE tblRebularInvoices2" & ", " & " " & _
"Set CheckNextMonth = True" & "," & " " & _
"SpecialBilling = True" & "," & " " & _
"Where RecordID = DMaxA"

    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQL1

I have tried various ways of writing the SQL string, but I just can't seem to get it right. No commas, no spacing after a comma, and spacing after the comma. The DMax and DMin statements are giving me the correct number.

I am hoping that it is only a simple syntax error.

 

[Eljefegeneo]

OOps, mispelled tblRegularInvoices2 in the first line. Changed it and still get the error message.

 

[Eljefegeneo]

Correct (at least spelling corrected):

Dim SQL, SQL1 As String
Dim DMinA, DMaxA As Long
DMinA = DMin("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])
DMaxA = DMax("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])

SQL = "UPDATE tblRegularInvoices2" & "," & " " & _
"Set CheckNextMonth = True" & "," & " " & _
"SpecialBilling = True" & "," & " " & _
"Remove = True" & "," & " " & _
"Where RecordID = DMinA"

SQL1 = "UPDATE tblRegularInvoices2" & ", " & " " & _
"Set CheckNextMonth = True" & "," & " " & _
"SpecialBilling = True" & "," & " " & _
"Where RecordID = DMaxA"

    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQL1

 

[sneuberg]

Here are a few things that need attention:

• No commas after the table name or just before the WHERE
• There's no point in concatenating literals, e,g, & "," & " " &
• Strings can't extend from one line to the next so you do need to concatenate them
• Variables need to be concatenated into the string.

With that the code simplifies to:

Dim SQL, SQL1 As String
Dim DMinA, DMaxA As Long
DMinA = DMin("RecordID", "tblRegularInvoices2", "SequenceNumber = " [COLOR="seagreen"]& [SequenceNumber][/COLOR])
DMaxA = DMax("RecordID", "tblRegularInvoices2", "SequenceNumber = "[COLOR="seagreen"] & [SequenceNumber][/COLOR])

SQL = "UPDATE tblRebularInvoices2 " [COLOR="blue"]&[/COLOR] _
" Set CheckNextMonth = True ,"[COLOR="blue"] &[/COLOR] _
" SpecialBilling = True , " & _
" Remove = True " [COLOR="Blue"]&[/COLOR] _
" Where RecordID = " [COLOR="seagreen"]& DMinA[/COLOR]

SQL1 = "UPDATE tblRebularInvoices2 " [COLOR="blue"]&[/COLOR] _
" Set CheckNextMonth = True , " [COLOR="blue"]&[/COLOR] _
" SpecialBilling = True " [COLOR="blue"]&[/COLOR] _
" Where RecordID = " [COLOR="SeaGreen"]& DMaxA[/COLOR]

    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQL1

 

[sneuberg]

Here's a good tip for debugging these sorts of problems.

 

[Eljefegeneo]

And I tired this, but it asks for a parameter when I run it for DMinA and DMaxA

Dim SQL, SQL1 As String
Dim DMinA, DMaxA As Long
DMinA = DMin("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])
DMaxA = DMax("RecordID", "tblRegularInvoices2", "SequenceNumber = " & [SequenceNumber])

SQL = "UPDATE tblRegularInvoices2 " & vbCrLf & _
"Set CheckNextMonth = True,  SpecialBilling = True, Remove = True Where RecordID = DMinA"

SQL1 = "UPDATE tblRegularInvoices2 " & vbCrLf & _
"Set CheckNextMonth = True,  SpecialBilling = True, Remove = True Where RecordID = DMaxA"
DoCmd.RunSQL SQL
    DoCmd.RunSQL SQL1

 

[MarkK]

But you don't want to pass the string 'DMinA.' That is a variable, and you want to pass its contents, so you need to evaluate that variable in VBA, and then concatenate that value to the end of your string, like....

SQL = _
   "UPDATE tblRegularInvoices2 " & _
   "Set CheckNextMonth = True, SpecialBilling = True, Remove = True " & _
   "WHERE RecordID = " [COLOR="DarkRed"]& DMinA[/COLOR]

See the difference?

 

[Eljefegeneo]

I ran the debug and got this:

UPDATE tblRegularInvoices2, Set CheckNextMonth = True, SpecialBilling = True, Remove = True, Where RecordID = DMinA
UPDATE tblRegularInvoices2,  Set CheckNextMonth = True, SpecialBilling = True, Where RecordID = DMaxA

Which, to me seems that it is correct. Or not? But maybe the DMinA and DMaxA are not getting passed?

 

[Eljefegeneo]

I changed the code as per suggested by MarkK and the Debug gives me:

UPDATE tblRegularInvoices2, Set CheckNextMonth = True, SpecialBilling = True, Remove = True, WHERE RecordID = 296
UPDATE tblRegularInvoices2,  Set CheckNextMonth = True, SpecialBilling = True, WHERE RecordID = 299

Which should be correct. But I still get the error message of syntax error.

 

[MarkK]

Too many commas. Remove the comma after the table name, and the comma after the last field.

 

[MarkK]

Here...

SQL = _
   "UPDATE tblRegularInvoices2 " & _
   "Set CheckNextMonth = True, SpecialBilling = True, Remove = True " & _
   "WHERE RecordID = " & DMinA

 

[Eljefegeneo]

Thanks. Works like a charm. Knew it could be done.

 

[MarkK]

Fantastic! Way to go sticking with it.