Solved Compare Two date values in two fields in one table

[mansied]

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]

Hi. Tables could have more than one record. How do you determine which records to compare?

 

[mansied]

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]

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]

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]

One table with two date fields .if return to service date < event occurrence , then pop a messag

e

 

[theDBguy]

One table with two date fields .if return to service date < event occurrence , then pop a messagView attachment 95092e

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]

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]

yes two fields in one table ....

So, did you try using DLookup()? For example:

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

 

[mansied]

So, did you try using DLookup()? For example:

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]

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]

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]

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!"

 

[mansied]

Are those date columns actually Date/Time data types? Or, are they Short Text?

yes both are Date/Time data types.

 

[theDBguy]

yes both are Date/Time data types.

In that case, try the following code and let us know if the result makes sense.

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]

In that case, try the following code and let us know if the result makes sense.

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 .

 

[theDBguy]

Thank you very much.it worked properly .

Okay. Glad to hear you got it sorted out. Good luck with your project.