?
Solved

Date comparison syntax in MS Access 2003

Posted on 2012-04-05
4
Medium Priority
?
280 Views
Last Modified: 2012-12-02
I have to fields in a DB both holding different syntax for date/time and I need to compare them with one another finding when they are not the same

Sample set of data:

DATETIME_OF_ENTRY : 201203201719
TRANSACTION_D_T : 03/20/2012 05:19PM

what I want to do is find situations where this is the case:
DATETIME_OF_ENTRY : 201203211719
TRANSACTION_D_T : 03/20/2012 05:19PM
(where the two fields differ)
0
Comment
Question by:markloessi
  • 2
4 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 37814052
use this expression in a query


 Format(Mid([DATETIME_OF_ENTRY], 5, 2) & "/" & Mid([DATETIME_OF_ENTRY], 7, 2) & "/" & Left([DATETIME_OF_ENTRY], 4) & " " & Mid([DATETIME_OF_ENTRY], 9, 2) & ":" & Right([DATETIME_OF_ENTRY], 2), "mm/dd/yyyy hh:nnAMPM")

that will format the field  [DATETIME_OF_ENTRY] in the same format as the field  [TRANSACTION_D_T]
0
 
LVL 53

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 37814285
Use this criteria:

Select * From tblYourTable
Where [DATETIME_OF_ENTRY] <> Format([TRANSACTION_D_T], "yyyymmddhhnn")

/gustav
0
 

Author Closing Comment

by:markloessi
ID: 37818146
Both these solutions actually helped with the end result, as is often the case until you see the solution you sometimes don't actually know what you need.

Thanks!
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 37818527
You are welcome!

/gustav
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question