Append Query - Data Format

[Moxioron]

Hello all.

I am running an append query taking a field from Table 'A' that has an account number, which is 15 digits and all text and appending it to Table 'B', which is formatted for a number, long integer, standard, 0 decimals.

However, I get a message that not all of the records were appended and most of the accounts are not in the formatted table (Table 'B').

I have never done an append query, so please excuse my ignorance.

What am I missing? Thanks for your help.

 

[HiTechCoach]

A long integer can hold a max positive vaule of 2,147,483,647 (10 digits max).

A 15 digit string of numbers can't be appended a long integer field unless the string is within the range 000000000000000 to 000002147483647.

In a test copy of the database try manually enter on of the 15 digit string that will not appens to see if you can enter it. My guess is that you can't. You will probably need to change the data type to double.

 

[Moxioron]

Thanks. I changed it to double/standard/0 decimal and it works great.

However, now I have a field that is text in Table 'A' and Date/Time in Table 'B'. For some reason I still get the message that '23 field(s) to Null due to type conversion failure'

The funny thing is that 23 fields are already blank in the text file. If I select, 'Yes' everything gets brought over. But I obviously would rather not have the error message at all.

Ideas? Thank you for your help.

 

[HiTechCoach]

Few things com to mind:

1) is the field is always blank then do not include it in your append query

2) If it can have data then you need to make sure that the data you are append matched the Date/Time data type

A date/time data type is a numeric data. You can Appen in Null, zero (0), or a vaild date time serial number. But not a zero length string.

If it does have data, what is the format?

 

[Moxioron]

Thanks for the response.

It's a text field in the first table and the field is blank at times, but most of the time there is data.

The second table I have the field formatted as Date/Time, Format = Short Date, Required = No, and Indexed = Yes (Duplicates OK)

 

[HiTechCoach]

A simple solution would be to use two separate append queries.

1) Select all the records where the date field in blank and append the other fields except eh blank date field.

2) Select all records with the date field in not blank and append all fields.

 

[Moxioron]

thank you. I will give that a whirl.

 

[HiTechCoach]

Let us know how it goes.