[AccessD] A2K: Exact match in query

Access is not case sensitive by default and the query engine isn't at
all, although you can force binary comparisons using InStr with the 0
option to force a binary compare. 

Are the two productIDs actually for different products?  If so, you
might want to find one of the values and replace all those with a new
ProductID that won't confuse the issue as well.

Charlotte Foust


-----Original Message-----
From: Steven W. Erbach [mailto:serbach at new.rr.com] 
Sent: Tuesday, September 28, 2004 12: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