Comparing Dates in a query

[pcdave41]

I am fairly new to VB scripting in Access and I could use some help. I am running a query based on a status field str of [STATUS]="Shipped" with along with a field which includes the ship date. I am wanting to do a comparison with the [SHIPDATE] and the current system date - 28 days. If the shipdate is <= date-28 then I want the field str of "Shipped" to read "Anomaly"

Private Sub cmdVShp_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("qryUSLEastShipped")
Set rs = qd.OpenRecordset()

Dim Cond1 As String
Dim Cond3 As String

Cond1 = "Shipped"
Cond2 = Date - 28
Cond3 = "Anomaly"

rs.MoveFirst

Do Until rs.EOF
rs.Edit
If [STATUS] = Cond1 And [SHIPDATE] <= Cond2 Then [STATUS] = Cond1
rs.Update
rs.MoveNext
Loop

Me.RecordSource = "qryUSLEastShipped"
Me.Requery

End Sub

Any help or suggestion would be appreciated.

David A

 

[PHV]

Perhaps this ?
If rs!STATUS = Cond1 And rs!SHIPDATE <= Cond2 Then rs!STATUS = Cond3

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