I am appending data from a temporar

[Delindan]

I am appending data from a temporary table which was imported from excel so all the fields came in as text although things like salary need to be a number. My solution for this was to append into my permanent table (CurrentSalary) the val(annual Salary) and there is another field which is FXRate which is a calculation. Both these fields are defined as numbers with decimals in my CurrentSalary table. The values are coming in as texts I believe simply because the calculation for the discount rate is coming out as whole numbers...either 1 or 0 depending on what the fraction is. Is there another way I should be doing this? Here is my statement:

DoCmd.RunSQL "INSERT INTO Currentsalary ( [Personnel Number], [Annual Salary], [FXRate], [Date], [DateAdded] )SELECT Temporary.[Personnel Number], VAL(Temporary.[Annual Salary]), (val(Temporary.[Annual Salary - USD])/val(Temporary.[Annual Salary])), strdate, date()FROM [Temporary];"

Thanks!

 

[dhookom]

I typically import from Excel into a temporary/holding table. I can then parse, filter, and convert the data as needed into the permanent table.

Duane
Hook'D on Access
MS Access MVP