Using DateDiff in Sub (1 Viewer)

M

MatthewH

Registered User.
Local time
Today, 02:25
Joined
Jan 12, 2017
Messages
49
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.

Thanks,
M
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 02:25
Joined
Jan 23, 2006
Messages
15,423
Please show us the whole procedure.

isn't working -- doesn't tell reader much.
Do you get an error?
If so does it have a number?
Could you expand a bit on isn't working/I]?
 
M

MatthewH

Registered User.
Local time
Today, 02:25
Joined
Jan 12, 2017
Messages
49
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"
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 02:25
Joined
Jan 23, 2006
Messages
15,423
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
 
M

MatthewH

Registered User.
Local time
Today, 02:25
Joined
Jan 12, 2017
Messages
49
jdraw said:
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
Click to expand...

Perfect fix, thank you!! Huge help!

On to learning how to use DCOUNT..... Because I still can't understand that problem :/
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 23:25
Joined
Mar 17, 2004
Messages
8,199
Also, rather than test for this in every row....
Code: 
        If rs("Indicator").value = "X" Then
...open a recordset that only has rows where this is true, like...
Code: 
Set rs = db.OpenRecordset( _
   "SELECT * FROM FILE " & _
   "WHERE Indicator = 'X'")
...which reduces the total rows processed by your code and potentially speeds it up.
 
M

MatthewH

Registered User.
Local time
Today, 02:25
Joined
Jan 12, 2017
Messages
49
MarkK said:
Also, rather than test for this in every row....
Code: 
        If rs("Indicator").value = "X" Then
...open a recordset that only has rows where this is true, like...
Code: 
Set rs = db.OpenRecordset( _
   "SELECT * FROM FILE " & _
   "WHERE Indicator = 'X'")
...which reduces the total rows processed by your code and potentially speeds it up.
Click to expand...

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.
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 23:25
Joined
Mar 17, 2004
Messages
8,199
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.
 
M

MatthewH

Registered User.
Local time
Today, 02:25
Joined
Jan 12, 2017
Messages
49
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!!
 
MarkK

MarkK

bit cruncher
Local time
Yesterday, 23:25
Joined
Mar 17, 2004
Messages
8,199
Thanks MatthewH, all the best with your project. :)
 
You must log in or register to reply here.

Similar threads

G
Using DateDiff and getting errors
Replies
7
Views
347
June7
June7
O
VBA Insert Into
Replies
2
Views
440
Pat Hartman
P
B
Fix error sub
Replies
7
Views
472
cheekybuddha
cheekybuddha
J
Unable to Sort on calculated field, based on another calculated field
Replies
10
Views
437
JohnPapa
J
C
Optional Variant in Inventory Control code
Replies
7
Views
277
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom