excell to access import problem

Hi guys I have an issue which I have been trying to fathom out and would welcome any help or suggestions.
Firstly on a daily basis I copy some production information and paste into two macro enabled excel files . Lets call them FILE 1 AND FILE 2 .The macro auto fills in the date.#
These excel files are imported into my access data base by way of a button in the access database which append the files to 3 different tables. Table 1 from file 1.Table 2 from file 2and a third Table which is called Table 3 which imports File 1 and 2 .
I also have the same thing appending a different database which is a backup.
The problem I have encountered is that some times I find that not all the information has been imported into table 2 and the same info is missing from table 3.
I have worked out that in one field it is not importing any items that have letters and dashes. the field is formatted as short text.
It doesn't happen to File 1 and it doesn't happen every day so I having trouble working out what is wrong ..... anyone have any ideas?

Thanks for reading this

Hi thanks for the reply, I have checked the formatting and it is ok .....I have also copied the excel file that imports to file one and used this to import to table two .......it works for a WHILE THEN IT STOPS IMPORTING ITEMS WITH A - OR LETTERS FOR EXAMPLE IF THE PRODUCT IS 3333-33 IT WONT LOAD THE ENTRY IF IT IS 333YAY IT WONT EITHER EVEN THOUGH BOTH TABLE AND EXCELL FILES ARE FORMATTED TO TEXT.
I DONT HAVE THIS PROBLEM WITH TABLE ONE

Hi I have tried to up load a basic database with and idea of what I am doing . The two macro enabled spread sheets are in the basic form I receive the information .If the date macro is ran it populates the date and auto fills in blanks in the next field .As you can see from table two it has not recognised some of the data .If the formatting is checked on the linked table that field always returns to numbers.... Hope this helps

Hi sorry I was just trying to simplify .I will load an almost complete version .The label on the main form called scrap breakdown generates the inputting to the tables when pressed. I know the formatting changes in the excel sheets but I don't get any issues in table one just table two ....I am just learning myself in access and have built this database with help from this forum and although I have probably made many mistakes Im always glad to receive advise or direction.
Thanks for your help so far

Here is a better version