[AccessD] datatype mismatch in query

Hi Pedro

Try comparing numbers too:

WHERE CLng(DEBSTM.Nummmer) NOT IN (SELECT tblDebiteurAlgemeen.DebID FROM tblDebiteurAlgemeen);

You can use SQL-native VAL() for CLng().

/gustav

>>> pedro at plex.nl 17-10-2005 21:26 >>>
Hello All,

i am using the following append-query to update a table (tblDebiteurAlgemeen) with records from another table (DEBSTM), that is linked to this database:

SELECT INTO tblDebiteurAlgemeen (DebID, Naambedrijf)
SELECT Clng([Nummer]) AS DebID, [Naam] AS NaamBedrijf
FROM DEBSTM IN 'C:\ThisDatabase.mdb'
WHERE DEBSTM.Nummmer NOT IN (SELECT tblDebiteurAlgemeen.DebID FROM tblDebiteurAlgemeen);

When using this query, i get an error. Datatype mismatch. 
This is because field DebID is Number and field Nummer is text. 
Although field Nummer is text, the values are al numbers. Because this field is in a commercial database, i can't change the field type. Is there another way??

Pedro Janssen