[AccessD] datatype mismatch in query

Hi Pedro

It sounds like a validation rule for DebID is kicking in. Obtain a specification for this.
If that is not possible, change the query to a normal select query and study the records.
Or add a filter to the append query to select only a subset of the records. Then make this smaller and smaller until you haved focused some of the records that breaks the validation.

/gustav 

>>> pedro at plex.nl 18-10-2005 22:46 >>>
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