Update Value

[fenhow]

Hi, I have a vba code that updates a status field when a condition is met.
This works but only updates the first record not all of them.
Is there a way to update all the records if the criteria is met?
Thanks.
Fen

'This makes the due payment marked as delinquent if the due date is <3 days past today
Private Sub Form_Load()
If Now() > [DueDate] + 3 Then
[Deliquent] = "Yes"
End If
End Sub

 

[Alc]

At present, it's only updating one record, as the field is only linked to one record at a time. You need to add a SQL statement to update all of them. Something like the following

 Private Sub Form_Load()
    If Now() > [DueDate] + 3 Then
        DoCmd.RunSQL "UPDATE {tablename} SET {fieldname} = 'YES' where DateDiff('d',Now(),{date field name}) > 3;"
    End If
End Sub

 

[fenhow]

Sweet! Works perfect... Many, many thanks.
Is there a way to not update to 'Yes' if either the Date paid is = to Due date or Date paid is = Null?
I really appreciate the help.
Fen

 

[pbaldy]

I wouldn't have that field or try to update it every day. If you want to see delinquent records, query with that criteria. On a form/report containing both, an IIf() can give you that display.

 

[Alc]

Sweet! Works perfect... Many, many thanks.
Is there a way to not update to 'Yes' if either the Date paid is = to Due date or Date paid is = Null?

Notwithstanding the good advice from Pbaldy, if you MUST have that working, try something like the following

  Private Sub Form_Load()
    If Now() > [DueDate] + 3 Then
        DoCmd.RunSQL "UPDATE {tablename} SET {fieldname} = 'YES' where (DateDiff('d',Now(),{date field name}) > 3) AND ({due date field} <> {paid date field}) AND (Not IsNull({date paid field}));"
    End If
End Sub

 

[fenhow]

I was just thinking that, every time I load the form it does the update and that will become a pain, so do I use the same code in a query then?

 

[pbaldy]

It also occurs to me that this might be a time to use the new calculated field, if you're using a version that supports it.

 

[fenhow]

Much appreciated, I agree with P, so how do I take this example you gave me and apply it to a query? Do I add this to the "Criteria" area for the field "delinquent"
Fen

 

[Alc]

If you just want to identify which records fall into that category, create a query and either open it to see or use it as the basis for a report (then open the report to see) e.g.

 SELECT * FROM {tablename} where (DateDiff('d',Now(),{date field name}) > 3) AND ({due date field} <> {paid date field}) AND (Not IsNull({date paid field}));

 

[fenhow]

Hi, an interesting scenario just popped up. The code works great but if I put a starting payment date of 11/1/2015 and have 12 months say to 11/1/2015 to 11/1/2016 and run the code, the dates 11/1/2015 and 12/1/2015 do not get updated to 'Yes' but all others do. Any idea why? Is the >3 equal to days?
Fen