[AccessD] A2K: Exact match in query

Try using Ucase(DailyFID3.[Facility Part #]) = Ucase(Product.[Facility Part
Number]) -- it will force all the alpha characters to upper case.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steven W. Erbach
Sent: Tuesday, September 28, 2004 3:50 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] A2K: Exact match in query


Dear Group,

I've scanned through Access help on this and I'm coming up short. I have a
query like the following:

INSERT INTO Daily 
  ( [Date], 
  [Source Number], 
  [Facility Part #], 
  [Used before disposal], 
  [Unit used], 
  ProductID )
SELECT 
  DailyFID3.Date, 
  DailyFID3.[Source Number], 
  DailyFID3.[Facility Part #], 
  DailyFID3.[Used before disposal], 
  DailyFID3.[Unit used], 
  Product.ProductID
FROM DailyFID3 
INNER JOIN 
  Product ON DailyFID3.[Facility Part #] = Product.[Facility Part Number];

The query inserts records from the DailyFID3 table into the Daily table.
This is an upgrade from a Paradox for Windows application and I'm
consolidating and normalizing the data.

There are several records in the DailyFID3 table that have a [Facility Part
#] of 8605guz. The problem comes here: the Product table has entries for
both 8605guz and 8605GUZ. So for every record in DailyFID3 with a part
number of 8605guz, Access appends TWO records into the Daily table, each
record having a different ProductID.

Paradox had no trouble matching the exact capitalization of the [Facility
Part #]. Access seems to ignore capitalization; thus I'm getting more
records appended than exist in the original table.

My question is: how do I get an Access query to pay attention to
capitalization?

Regards,

Steve Erbach
Neenah, WI

"We cannot make a man worth a gven amount by making it illegal for anyone to
offer him less." - Henry Hazlitt


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com