Solved

ms access query - find unmatched

Posted on 2014-01-20
6
268 Views
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.
tks
0
Comment
Question by:jsgould
  • 3
  • 3
6 Comments
 
LVL 142

Accepted Solution

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

part 2: non-matched entries - you can do with the wizard:
http://office.microsoft.com/en-us/access-help/compare-two-tables-and-find-records-without-matches-HA010205132.aspx#BM2
0
 

Author Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39795338
as posted above, use the format function as needed...
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

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

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
0
 

Author Closing Comment

by:jsgould
ID: 39807459
Thank You.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

770 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