Violation errors while inserting data from one table into another

[imnewtoaccess]

Hi,

I am getting errors while inserting records in one table from another.


These are the structures of two tables :

file51tm_new

RecordType Text
RecordNumber Number
WorkCOde Text
Date TExt
Tck_nbr Text
time text
transno text
Start_date Text
End_date TExt
Tk_serial Text
tck_price text
void_code text
mag_encode text
red_date text
red_time text
batch number

file51tm_new_1

RecordType Text
RecordNumber Number
WorkCOde Text
Date date/time
Tck_nbr number
time text
transno number
tck_type number
Start_date date/time
End_date date/time
Tk_serial text
tck_price number
void_code text
mag_encode text
red_date date/time
red_time text
batch number

And this is the query :

INSERT INTO FILE51TM_New_1 ( RecordNumber, WorkCode, [Date], Tck_Nbr, [Time], TransNo, Tck_Type, Start_Date, End_Date, Tk_Serial, Tck_Price, Void_Code, Mag_EnCode, Record_Type, Batch )
SELECT FILE51TM_New.RecordNumber, FILE51TM_New.WorkCode, IIf([Date]<>"",[DATE],"") AS Expr1, FILE51TM_New.Tck_Nbr, FILE51TM_New.Time, Val([TransNo]) AS Expr4, FILE51TM_New.Tck_Type, IIf([START_DATE]<>"",(Mid([Start_Date],1,2) & "/" & Mid([Start_Date],3,2) & "/" & Mid([Start_Date],5,2)),"") AS Expr2, IIf([END_DATE]<>"",(Mid([End_Date],1,2) & "/" & Mid([End_Date],3,2) & "/" & Mid([End_Date],5,2)),"") AS Expr3, FILE51TM_New.Tk_Serial, FILE51TM_New.Tck_Price, FILE51TM_New.Void_Code, FILE51TM_New.Mag_EnCode, FILE51TM_New.RecordType, FILE51TM_New.Batch
FROM FILE51TM_New
WHERE (((FILE51TM_New.Batch)=DMax("[FILE51TM_New]![Batch]","FILE51TM_NEw")));

I am trying to insert records from file51tm_new into file51tm_new_1 where the batch field is max and in the process, I am converting date fields ( in text format ) into date format. I get the following error :

Microsoft Access can't append all records in th query
Microsoft Access set 8 fields to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

There is no primary key defined on either of the two tables, and there are no validation rules for any of the fields in both the tables.


Anybody able to help me on this ?

Would appreciate any solutions.

Thanks in advance,

[MMcCarthy]

Your problem is with your dates. You are trying to convert text to date/time

For example,

IIf([Date]<>"",[Date],"") AS Expr1

Should be

IIf([Date]<>"",Format([DATE], "dd/mm/yy",Null) AS Expr1

I've used european date format but you can use whichever format is suitable to your database.

Follow the same rules for the other dates. You cannot use "" for the value if false as this is a string, so I've used Null.

[imnewtoaccess]

I tried this option, but I still get the same error.....

Your problem is with your dates. You are trying to convert text to date/time

For example,

IIf([Date]<>"",[Date],"") AS Expr1

Should be

IIf([Date]<>"",Format([DATE], "dd/mm/yy",Null) AS Expr1

I've used european date format but you can use whichever format is suitable to your database.

Follow the same rules for the other dates. You cannot use "" for the value if false as this is a string, so I've used Null.

[MMcCarthy]

I tried this option, but I still get the same error.....

Did you do this for all date fields?

[NeoPa]

I tried this option, but I still get the same error.....

Check if the field properties for the date fields in the resultant table (where you're inserting the data into) have allowance for Nulls.

PS. Also consider how you WANT these values to be handled.

[imnewtoaccess]

Well, the other date fields are string fields in format "mmddyyyy". so i have to format them the way i am doing it in the query ( and cannot use the format function on them ).

[MMcCarthy]

Well, the other date fields are string fields in format "mmddyyyy". so i have to format them the way i am doing it in the query ( and cannot use the format function on them ).

You can't insert a string into a date field. At least change the value if false to Null in each case.

[imnewtoaccess]

It is very strange...... MS Access is giving that error. But when I click on 'Yes' to ignore the error and run the query, it inserts those records that i want.

[NeoPa]

Well, the other date fields are string fields in format "mmddyyyy". so i have to format them the way i am doing it in the query ( and cannot use the format function on them ).

Why can't you use Format()? I would.

[MMcCarthy]

It is very strange...... MS Access is giving that error. But when I click on 'Yes' to ignore the error and run the query, it inserts those records that i want.

It is probably just inserting nulls instead of those empty strings.

[imnewtoaccess]

if i use format directly on the text fields it gives incorrect date values.

[imnewtoaccess]

Anyways, thanks for the help.

[NeoPa]

If I use format directly on the text fields it gives incorrect date values.

That's probably just because you're not using them correctly.
Let's focus on a single field for the moment.
Post here what you start with and what you hope to be inserted into the other table. I'll see if I can find the correct code for translating it. Remember, it is very important to know if Nulls are possible, or empty strings, and also which values are accepted by the destination table.
Choose any of the date fields, but do be clear about what you've selected.

[FishVal]

Hi,

I am getting errors while inserting records in one table from another.


These are the structures of two tables :

file51tm_new

RecordType Text
RecordNumber Number
WorkCOde Text
Date TExt
Tck_nbr Text
time text
transno text
Start_date Text
End_date TExt
Tk_serial Text
tck_price text
void_code text
mag_encode text
red_date text
red_time text
batch number

file51tm_new_1

RecordType Text
RecordNumber Number
WorkCOde Text
Date date/time
Tck_nbr number
time text
transno number
tck_type number
Start_date date/time
End_date date/time
Tk_serial text
tck_price number
void_code text
mag_encode text
red_date date/time
red_time text
batch number

And this is the query :

INSERT INTO FILE51TM_New_1 ( RecordNumber, WorkCode, [Date], Tck_Nbr, [Time], TransNo, Tck_Type, Start_Date, End_Date, Tk_Serial, Tck_Price, Void_Code, Mag_EnCode, Record_Type, Batch )
SELECT FILE51TM_New.RecordNumber, FILE51TM_New.WorkCode, IIf([Date]<>"",[DATE],"") AS Expr1, FILE51TM_New.Tck_Nbr, FILE51TM_New.Time, Val([TransNo]) AS Expr4, FILE51TM_New.Tck_Type, IIf([START_DATE]<>"",(Mid([Start_Date],1,2) & "/" & Mid([Start_Date],3,2) & "/" & Mid([Start_Date],5,2)),"") AS Expr2, IIf([END_DATE]<>"",(Mid([End_Date],1,2) & "/" & Mid([End_Date],3,2) & "/" & Mid([End_Date],5,2)),"") AS Expr3, FILE51TM_New.Tk_Serial, FILE51TM_New.Tck_Price, FILE51TM_New.Void_Code, FILE51TM_New.Mag_EnCode, FILE51TM_New.RecordType, FILE51TM_New.Batch
FROM FILE51TM_New
WHERE (((FILE51TM_New.Batch)=DMax("[FILE51TM_New]![Batch]","FILE51TM_NEw")));

I am trying to insert records from file51tm_new into file51tm_new_1 where the batch field is max and in the process, I am converting date fields ( in text format ) into date format. I get the following error :

Microsoft Access can't append all records in th query
Microsoft Access set 8 fields to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

There is no primary key defined on either of the two tables, and there are no validation rules for any of the fields in both the tables.


Anybody able to help me on this ?

Would appreciate any solutions.

Thanks in advance,

You can use DateValue function
Example of append query for 1 field.

INSERT INTO tblNew ( dteDate )
SELECT IIf(nz([tblOld]![strDate])="",Null,DateValue([tblOld]![strDate]))) AS dteDate
FROM tblOld;

iif avoids passing empty strings and nulls to DateValue function otherwise resulting in error message You've seen

you can use DateValue function w/o IIf, Access will fill recordfields unable to be evaluated with nulls when you choose Yes on prompt

Good luck.