Hello everyone,
  I need help with something that is a bit more complicated than I am used to dealing with. It's three seperate components.

The first one is that I need to take a date field and make a couple of changes to it. If the date field is <=12/31/2006, I need to update it to m/1/2007. I think the formula I would use for it is, but I want to be sure:
The second is that if the date is <=12/31/2006, I need it to change a checkbox field to "True" and I need it to change a Date_Changed field to "12/28/2006".

I'm not sure how is the best way to do this.

Thanks,
Arholly

You are doing many things with the same date parameters. Is that right? You say in the first one, if the date is <=date, and in the second one, if the date is <=date. These are the same conditions. Did you mean one to be >=date?

That being said, you want to take the values in variables, and wrap the values in a conditional, like this:

Dim dtNewDate As Date

If Me.Scheduled_Review_Date <= #12/31/2006# Then
   dtNewDate = DatePart("m",[Scheduled_Review_Date]) & "/1/2007"
   Me.CheckBox = True
   Me.DateChanged = #12/28/2006#
End If

Me.Scheduled_Review_Date = dtNewDate

Is that what you want? This code will run on some event on a form. If you want to loop through all of your records and make these changes, then let me know.



mmcdonal

Mmcdonal,
  Thanks for the help, but yes, I wanted to run it as an update so that I can update all the records.
  To answer your questions though, yes, I am using the same parameters for all the variables. If the "Scheduled_Review_Date" is <="12/31/2006", then do all of the stated.

Thanks for your help with this, I appreciate it.

I would do something like this then. Create a form, and put a single button on the form. Put this sort of code on the On CLick event of the button:

Dim rs As ADODB.Recordset
Dim dtNewDate, dtChange As Date
Dim sSQL As String

dtNewDate = DatePart("m",[Scheduled_Review_Date]) & "/1/2007"
dtChange = "12/28/2006"

sSQL = "SELECT * FROM myTable WHERE [Scheduled_Review_Date] <= #12/31/2006#"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
   rs("Scheduled_Review_Date") = dtNewDate
   rs("CheckBox") = True
   rs("DateChanged") = dtChange
   rs.Update
   rs.MoveNext
Loop
rs.Close

You may have to mess around with the SQL string syntax to get it to pull the proper records. So try this for your check:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtSample As Date

sSQL = "SELECT * FROM myTable WHERE [Scheduled_Review_Date] <= #12/31/2006#"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtSample = rs("Scheduled_Review_Date")

MsgBox dtSample

rs.Close

This will let you tes the syntax and only pop up the first record in the recordset.

I bet one of the other posters will come up with a very elegant Update query though.

HTH



mmcdonal

Okay, I got the test code to work just fine, but when I go to run the real code, I get a run time '2465' error. It says it can't find the field '|' refered to in your expression and the line in question is:

Dang *smacking forehead* of course you wouldn't get that because it hasn't pulled the date yet. Yeesh.

Add this:

Do Until rs.EOF
   dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

   rs("Scheduled_Review_Date") = dtNewDate
   rs("CheckBox") = True
   rs("DateChanged") = dtChange
   rs.Update
   rs.MoveNext
Loop

And remove the dtNewDate line
dtNewDate = DatePart("m", [Scheduled_Review_Date]) & "/1/2007"
from the top part of the code.

Dang.

mmcdonal

Thanks for all of your help. I realized I made a mistake in the information I gave you, but was able to fix it myself. I gave you the real date instead of the date_ID (because the tables are related), so it was throwing an error. Once I realized what I had done, it worked perfectly. Thanks for all of your help mmcdonal. I appreciate it.

Best Regards & Thanks again,
Arholly

De nada. Anytime.

Hey, I got a post for that! =)

mmcdonal

I'm curious as to why you're dealing with dates as strings:

dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

Instead of dates as DATES:

dtNewDate = DateSerial(2007, Month(Me.Scheduled_Review_Date), 1)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I can see the code is shorter...

dtNewDate = DateSerial(2007, Month(Me.Scheduled_Review_Date), 1)
versus
dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

No wait, its not! =)

That is the way the question was posed.


mmcdonal