Solved Compare Two date values in two fields in one table (1 Viewer)

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
Hello
I have a basic question,
I have a table and want to compare dates .if date 1> date 2 , then pop a message .
any Idea how can i do it ?
with query or with VBA ??
Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Tables could have more than one record. How do you determine which records to compare?
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
Hi. Tables could have more than one record. How do you determine which records to compare?
it is better to check all records .but if it could find the first one should pop a message..I really just need the notice to pop out .
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
it is better to check all records .but if it could find the first one should pop a message..I really just need the notice to pop out .
Maybe you can give us some sample data to help us understand what you need. If there are multiple dates in both tables, you will inevitably get the popup.
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
I have this code to import from Xl to Access records by record . how can i add a control here ?

For Each varFile In .SelectedItems
Name = varFile
FileName = Dir(varFile)
Workbooks.Open(varFile).Sheets("Report Details").Activate
Dim i As Integer
i = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Close False
i = i - 1
DoCmd.RunSQL ("INSERT INTO ImportedRows (Rows) VALUES ('" & i & "');")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "DATA_DA_Imported", varFile, True, "Report Details!"
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
One table with two date fields .if return to service date < event occurrence , then pop a messag
1633616195314.png
e
 

Attachments

  • 1633615974368.png
    1633615974368.png
    8.3 KB · Views: 128

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
One table with two date fields .if return to service date < event occurrence , then pop a messag View attachment 95092 e
Ah, so you're not trying to compare two tables then. Instead, you just want to compare two columns in one table, and all the records have the same dates, correct? If so, you should be able to use DLookup().
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
Ah, so you're not trying to compare two tables then. Instead, you just want to compare two columns in one table, and all the records have the same dates, correct? If so, you should be able to use DLookup().
yes two fields in one table ....
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
yes two fields in one table ....
So, did you try using DLookup()? For example:

Code:
If DLookup("[Return to Service Date]<[Event Occurrence]", "TableName")=True Then
    Msgbox "Popup message here."
End If
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
So, did you try using DLookup()? For example:

Code:
If DLookup("[Return to Service Date]<[Event Occurrence]", "TableName")=True Then
    Msgbox "Popup message here."
End If
Yes i tested .It doesn't work .i have no error on it but no message pops...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
Yes i tested .It doesn't work .i have no error on it but no message pops...
Are those date columns actually Date/Time data types? Or, are they Short Text?
 

Mike Krailo

Well-known member
Local time
Today, 14:27
Joined
Mar 28, 2020
Messages
1,030
I have a table and want to compare dates .if date 1> date 2 , then pop a message .
Are you wanting to get that message box at the time data is entered in for a record? If so, then that code would go into a Before Update event. That's usually how I would set something like that up. This prevents bad data from going into the table in the first place.
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
Are you wanting to get that message box at the time data is entered in for a record? If so, then that code would go into a Before Update event. That's usually how I would set something like that up. This prevents bad data from going into the table in the first place.
Yes ,update events ? what do mean .?

this part of code is useded to import ..


For Each varFile In .SelectedItems
Name = varFile
FileName = Dir(varFile)
Workbooks.Open(varFile).Sheets("Report Details").Activate
Dim i As Integer
i = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Close False
i = i - 1
DoCmd.RunSQL ("INSERT INTO ImportedRows (Rows) VALUES ('" & i & "');")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "DATA_DA_Imported", varFile, True, "Report Details!"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
yes both are Date/Time data types.
In that case, try the following code and let us know if the result makes sense.
Code:
If DLookup("[Return to Service Date]<[Event Occurrence]", "TableName")=True Then
    MsgBox "Popup message here."
Else
    MsgBox DLookup("[Return to Service Date]", "TableName") _
        & " is not lesser/earlier than " & DLookup("[Event Occurrence]", "TableName")
End If
 

mansied

Member
Local time
Today, 14:27
Joined
Oct 15, 2020
Messages
99
In that case, try the following code and let us know if the result makes sense.
Code:
If DLookup("[Return to Service Date]<[Event Occurrence]", "TableName")=True Then
    MsgBox "Popup message here."
Else
    MsgBox DLookup("[Return to Service Date]", "TableName") _
        & " is not lesser/earlier than " & DLookup("[Event Occurrence]", "TableName")
End If
Thank you very much.it worked properly .:)
 

Users who are viewing this thread

Top Bottom