If then using a date not working

[epb]

I had someone enter the initial records into a form and the dates were set as default to =Now(). This is fine for normal data entry but the dates should have been set to an earlier date for 400+ records that were initially entered. I have some code that loops through all records in a table and checks that a transaction date is greater then a certain date. If it is greater it should set the date to the "corrected" date but this is not working.

The code is looping from record zero to EOF. The looping works fine and the absolute record position increments but from the watches window, the textfield Me.DateStockTrx shows the correct date the first time through the loop but subsequent times through the loop this text field shows the date that the first record was changed to. Me.DateStockTrx does not match what is in the field of the recordset. The field of the recordset is correct. So the if then statement is never true after the first pass through the loop.

Here is the code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim Int1 As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * from Inventory;")

Int1 = 0
With rs
    If Not (.EOF Or .BOF) Then
    rs.MoveFirst
        Do Until .EOF
            .Edit
            If (Format(Me!dateStockTrx, "m/dd/yyyy") > #8/15/2012#) Then
                Me!dateStockTrx = #7/28/2012#
            End If
            .Update
            .MoveNext
            Int1 = Int1 + 1
        Loop
        .Close
        Else
    End If
End With

I also tried the following:

Dim rs As Object
Dim Int1 As Integer

Set rs = Me.Recordset.Clone

Int1 = 0
With rs
    If Not (.EOF Or .BOF) Then
    rs.MoveFirst
        Do Until .EOF
            .Edit
            If (Format(Me!dateStockTrx, "m/dd/yyyy") > #8/15/2012#) Then
                Me!dateStockTrx = #7/28/2012#
            End If
            .Update
            .MoveNext
            Int1 = Int1 + 1
        Loop
        .Close
        Else
    End If
End With

[JoeM]

I think the issue is this line here:

If (Format(Me!dateStockTrx, "m/dd/yyyy") > #8/15/2012#) Then

The "Format" function returns a Text value, not a Date Value. So you are trying to compare Text to a Date.

Maybe try:

If Me!dateStockTrx > DateValue("8/15/2012") Then

[epb]

Thanks Joe,

This appeared to work to trigger the if then statement when true - is this correct?:

If (Format(Me!dateStockTrx, "m/dd/yyyy") > DateValue("8/15/2012")) Then

I still have the problem that only the first record through the loop is being updated. (??)

I tend to think access is not so straightforward. I tired numerous ways to get the IF Then statement to work correctly and I am not sure the above is correct. Field dateStockTrx is date and time. I initially tried:

If (dateStockTrx > #8/15/2012#) then...

and that did not work. I also found that the format "mm/dd/yyyy" would not work because of the "mm" having two digits for the month while "m" worked for finding months 8 and 9. I have tried other suggested methods but it may be that there is something else going on because I still have the problem that only the first record through the loop is being updated. When I return back to the form the record pointer is on the first record and the edit icon is showing. Like it never moved past the first record.

[JoeM]

I re-read your initial question more closely this time (I was mostly analyzing your code the first time around).
Is VBA really necessary? It seems to me that you should be able to to simply use an Update Query if all you want to do is update a single date field to "7/28/2012" if the value in it is currently greater than "8/15/2012". Just enter:

> #8/15/2012#

in the Criteria portion of this field, and enter #7/28/2012# on the Update To line of the Update Query.

[epb]

Joe,

The update query worked. Thanks!

Still can't explain the other problem but oh well.