Need to change a field in all records that show a date previosu to tod 1

[avayaman]

I have a membership database that shows an expiry date. I have a field that indicates "active" or "expired". I need to run a routine that will go through every record in a table and change the field (called status) if the date in another field (duespaid) is prior to the current date.

I can run this off a button, but I would much rather have it run automatically as soon as the database is opened up if possible.

I know it's mysql command but I can't get it to work

I tried something like this:

DoCmd.SetWarnings False

Dim SQL As String


SQL = "UPDATE tblmembers " & _
"SET tblmembers.status = 'Expired' where Date > tblmembers.Duespaid.Value"


DoCmd.RunSQL SQL



Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[PHV]

Perhaps this ?
SQL = "UPDATE tblmembers SET status='Expired' WHERE Duespaid<Date()"
Or this ?
SQL = "UPDATE tblmembers SET status='Expired' WHERE Duespaid<#" & Format(Date, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[avayaman]

Worked great, Thankyou. I figured out how to get to run when the database opens by inserting the code in the on open section of the switchboard form.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[avayaman]

OK, one other thing. I only want it to change when the status field shows "Active". There are other possibilites for that field that I do not want to be altered. Can I restrict it to change only when status = 'active'

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[PHV]

Simple enough:
SQL = "UPDATE tblmembers SET status='Expired'" _
& " WHERE Duespaid<Date() AND status='active'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[avayaman]

Simple for you, not for me. I'm just learning this stuff. anyway it works and thank you very much. I was close with trial & error, but not close enough.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com