[AccessD] datatype mismatch in query

Hello Gustav,

i figured it out.
I was trying to insert zero length values into a field with the "Allow zero 
Length property" of.

Thanks for your help.

Pedro Janssen


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