[AccessD] datatype mismatch in query

Hello Gustav and others,

when using Val() or CLng(), i get a message, that says:

Not all records in the append query can be added:  ...... records as a 
result of conflict in validationrules.
How can this be solved?

Pedro Janssen


----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Monday, October 17, 2005 9:33 PM
Subject: Re: [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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>