Update date to friday

[n90]

So I have an AR report, that currently we track by our MailDate.

Issue is some times the maildate is not inputted into the system and it gives the date 1/1/1900. Then I filter my report for Accounts that have that 1/1/1900 in there Maildate then I run an UPDATE query on all those Invoices to make the MailDate equal the Friday of the DocumentDate week, so lets say DocumentDate = 2/13/14 I make an UPDATE query to make the MailDate = 2/14/14.

What kind of code(I assume CASE) would I need to add to my AR query in order to avoid having to create and run that separate UPDATE query.

 

[Rx_]

Just a quick note:
the 1/1/1900 is 0 (in Access)
Time / date is the number of days since 1900 midnight

In SQL Server it is different:
http://social.msdn.microsoft.com/Fo...-and-jet-or-vba-date-datatype?forum=accessdev

this MSDN forum doesn't have all the answers. But, it might help get your questions started.

 

[Salbrox]

Do you allow Nulls in this field? Maybe you should. Or consider using a default value.

You could auto-run a SP using the SQL agent every evening to make the MailDate equal the Friday of the DocumentDate week. Or you could place a trigger on the table to do it on update.