ms access query - find unmatched

Posted on 2014-01-20
Last Modified: 2014-01-24
I have 2 linked .dbf tables that I want to check for unmatched entries on 2 fields. Field#1 is a text field in both tables. field#2 is a text field in table#1 which contains a date in the format yyyymmdd. field#2 is a date field in table#2 in the format mm/dd/yyyy.

I need a query that will detect an unmatched entry based on the 2 fields. Don't know how to specify/convert field#2 in table#1 so that I don't get a "type mismatch" error on the 2 date fields.

Source tables cannot be changed.
Question by:jsgould
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 39795175
part 1: converting text vs date values:
Cdate(field2)   for the text field
Format( field, "mm/dd/yyyy")  for the date field

part 2: non-matched entries - you can do with the wizard:

Author Comment

ID: 39795320
how do I construct the query so that the dates are in the correct form when running?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39795338
as posted above, use the format function as needed...
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 39795360
I mean where do I put the functions in the access query ? i.e. Field name, table,criteria, etc..
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39796361
are you working in a SQL Query Window, or pure sql?
the table names remain as they are, instead of the field name, you put the expression having the function(fieldname) instead of just fieldname

Author Closing Comment

ID: 39807459
Thank You.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

738 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