I have a module that has the following IF statement that isn't working:
Code:
If DateDiff("d", Now(), rs("Date")) = 1 Then
...
End If
I'm trying to use this code to do the following:
If the difference in DAYS between rs("Date") and now is exactly 1 day (not counting for hours/minutes/seconds), I want to run that IF statement.
Please advise as to the best way to modify or re-create this code.
It runs the line of code, no errors. It just doesn't get me to output what I want.
Here's the procedure:
Code:
Public Sub ImportUpdateAppend()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs= db.OpenRecordset("FILE")
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF = True
If rs("Indicator").Value = "X" Then
If DateDiff("d", Now(), rs("Date")) = 1 Then
rs.Edit
rs("Comment") = "Happened yesterday."
rs.Update
Else If DateDiff("d", Now(), rs("Date")) = 0 Then
rs.Edit
rs("Comment") = "Happened today."
rs.Update
End If
End If
Loop
End If
End Sub
That's my code. I just want the comment "Happened yesterday." if the datediff shows it came in yesterday and "Happened today." if it happened today based on the Date in the field and the current date.
Thank you!!
Edit: When I try running it, it doesn't seem to work with the logic. Wondering if my logic is wrong or if there's another way. My "Date" field shows as follows: "m/dd/yyyy"
Date is a reserved word in Access, I changed to MyDate
Now includes Time, you should be using Date (the Access function) if you are looking for days between 2 dates.
Note: My settings are for dates to be displayed dd-mmm-yyyy
I think this demos the output you are trying to get
Code:
Public Sub ImportUpdateAppend()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("FILE")
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF = True
[COLOR="YellowGreen"]Debug.Print rs!indicator, rs!MyDate[/COLOR]
If rs("Indicator").value = "X" Then
If DateDiff("d", Date, rs("MyDate")) = -1 Then
rs.Edit
rs("Comment") = "Happened yesterday."
rs.Update
ElseIf DateDiff("d", Date, rs("MyDate")) = 0 Then
rs.Edit
rs("Comment") = "Happened today."
rs.Update
End If
End If
rs.MoveNext [COLOR="YellowGreen"]'<--- you need to move to the next record[/COLOR]
Loop
End If
[COLOR="YellowGreen"]'check to see what the changed in the recordset.[/COLOR]
rs.MoveFirst
With rs
Do While Not .EOF
Debug.Print !id, !indicator, !MyDate, !Comment
rs.MoveNext
Loop
End With
End Sub
Original "File"
Code:
id indicator myDate Comment
1 X 03-May-2017
2 X 04-May-2017
3 Y 04-May-2017
Debug.print output
Code:
X 03-May-2017
X 04-May-2017
Y 04-May-2017
1 X 03-May-2017 Happened yesterday.
2 X 04-May-2017 Happened today.
3 Y 04-May-2017 Null
"File" showing results of using the procedure.
Code:
id indicator myDate Comment
1 X 03-May-2017 Happened yesterday.
2 X 04-May-2017 Happened today.
3 Y 04-May-2017
Date is a reserved word in Access, I changed to MyDate
Now includes Time, you should be using Date (the Access function) if you are looking for days between 2 dates.
Note: My settings are for dates to be displayed dd-mmm-yyyy
I think this demos the output you are trying to get
Code:
Public Sub ImportUpdateAppend()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("FILE")
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF = True
[COLOR=yellowgreen]Debug.Print rs!indicator, rs!MyDate[/COLOR]
If rs("Indicator").value = "X" Then
If DateDiff("d", Date, rs("MyDate")) = -1 Then
rs.Edit
rs("Comment") = "Happened yesterday."
rs.Update
ElseIf DateDiff("d", Date, rs("MyDate")) = 0 Then
rs.Edit
rs("Comment") = "Happened today."
rs.Update
End If
End If
rs.MoveNext [COLOR=yellowgreen]'<--- you need to move to the next record[/COLOR]
Loop
End If
[COLOR=yellowgreen]'check to see what the changed in the recordset.[/COLOR]
rs.MoveFirst
With rs
Do While Not .EOF
Debug.Print !id, !indicator, !MyDate, !Comment
rs.MoveNext
Loop
End With
End Sub
Original "File"
Code:
id indicator myDate Comment
1 X 03-May-2017
2 X 04-May-2017
3 Y 04-May-2017
Debug.print output
Code:
X 03-May-2017
X 04-May-2017
Y 04-May-2017
1 X 03-May-2017 Happened yesterday.
2 X 04-May-2017 Happened today.
3 Y 04-May-2017 Null
"File" showing results of using the procedure.
Code:
id indicator myDate Comment
1 X 03-May-2017 Happened yesterday.
2 X 04-May-2017 Happened today.
3 Y 04-May-2017
I like this a lot as something to use in the future but I am going to be adding a bunch of other if statements. Would it be better to make each individual if statement a recordset and reference it? For example my X would be a rs and then my Y would? It's really a minute change because I'm only doing this through 300-500 records and no more than 1000 so speed shouldn't be terribly different. I understood if it were over 100000 cells I may want to look into it further, but I think 1000 is negligible.
Well, looking at the bigger picture, I would never store the text "happened today" or "happened yesterday" with my actual data. 24 hours from now that text is incorrect, so I would only display that in a report or something, and then I would write a function that calculates it on demand. I would never store data that--just by waiting--will become incorrect.
So the code you are writing, rather than write it to "push" data into a table, write a function that "pulls" the text "today", "yesterday", "tomorrow" out of a date value. Then call that function as required the moment you present your data.
Data is like a rope, you always want to pull on it.
I actually used those just as quick references for what the text would be but it actually uses the comment "Happened on ___" and ___ is a CDate function referencing the date. SO I completely agree with you that happened today/yesterday isn't a good method and I have amended it to include the date so it's more certain. I use it like this because I will be changing the comment to alert them of a timeframe they have which will be 30 days from the Date. Just wanted to start the coding from the simplistic steps and branch out!
Thank you as usual MarkK, you always provide great insight!!
