Converting text to number in access

[ItIsHardToProgram]

Hey, I am not sure what section is best to start this thread, but I believe the solution to my problem will reside in queries...

Let me start off by explaining what I have.

I currently have a database that stores the number of hours an employee spends on a specific project (in access). The storage is done through excel, with a VBA fonction and an ADO recordset.

Everything on that part works fine, data is stored correctly etc...

But as you can imagine, this sort of system creates "trash" data, if an employee does a mistake or what ever.

So, to remove this trash data, I take the data from access, paste it in excel, compare it to filtered data from what was paid (another excel sheet) and filter and remove everything unecessery and change what wasnt entered correctly.

In that process, once I am done, I paste back all the data into access, (column by column since it doesnt let me paste it as a whole.) --> Before doing so I make sure all data is numbers

The problem is the following:

When I paste the data from Excel to acess, access does not seem to process it has numbers, even though my field is numbers only, it seem to only recognize what was entered through the ADO recordset, and not what is pasted. (don't know if this is a normal behaviour or if this is what is happening... ).

The result: For 5400 hours worked, access only processes 1700 in my queries, thus resulting in big margins.

[blue] 1:I would first of all like to know if this is in fact my problem, if I can do anything to explain more please mention.

2:If this is my problem, is there any way to fix this, in order to have no margins between what I have entered and the Actual data.[/blue]

[Red]The way I verified data integrity:[/red]

I started by pasted all data in access in a excel sheet, I then convert all to number, since it automaticaly stores back in text, and then I summarized the numbers, gives me a total of 5400.

I then run the following query:

SELECT tblTimeSheet.IdProjet AS Expr1, Sum(tblTimeSheet.Lundi+tblTimeSheet.Mardi+tblTimeSheet.Mercredi+
tblTimeSheet.Jeudi+tblTimeSheet.Vendredi+tblTimeSheet.Samedi+tblTimeSheet.
Dimanche+tblTimeSheet.LundiRD+tblTimeSheet.MardiRD+tblTimeSheet.
MercrediRD+tblTimeSheet.JeudiRD+tblTimeSheet.VendrediRD+tblTimeSheet.
SamediRD+tblTimeSheet.DimancheRD) AS TotalTemps
FROM tblTimeSheet
GROUP BY TblTimeSheet.IdProjet;

I have a margin of over 3K hours.

I tried taking the numbers in excel and pasting them back once converted, no joy.

Thanks ALOT for your help, it is much appreciated.



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Just for your information, I have verified my claim, and the only data that isnt processed is indeed the data I had pasted already. I had the backup of what I pasted and it was the part missing.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Your process is not very sound. I would NEVER recommend using copy 'n' paste like this. NEVER.

You ought to edit the entries made into your Access table to prevent junk!

ON ONE should be entering data directly into tables. Data entry should be via a form that has proper edits and controls to prevent this kind of mess.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, duly noted, but let me explain why I resulted in doing this.

The timesheets that the employees fill out, that eventualy feed the database are in excel. These are then filtered by our secretary and the only remaining timesheets are the ones with validated time, thus the correct data.

There are several employees in this company thus a large amount of data each week, to be changing the trash data 1 by 1 in access takes a while. But filtering them in excel takes maximum 10 minutes.

I first tried to "import" any type of file from my excel sheet I created with the right data, and nothing would cut it.

The access part of this system is very important, since it gives out real time indication about how projects are going to the managers.

Thus said, I understand what you are saying about controling the mess from the frontend, so from the timesheet itself, but if an employee punches in a 10 instead of a 1, I can't really control it, since it is possible that the employee passed 10 hours on a specific project, limiting that would mean rethinking our whole system.

The trash data being inevitable, I figured cleaning every month would be the best deal.

We all know that the reason I am here is that I am an imperfect programmer / developper / database manager, wich is not really my job, and I can use alot from your experiences.

[blue] So, in light of what you mention:

1- What process would you consider the best to go from an excel based cleaned table to an access clean table, a macro that would simply "clean" the faulty data through ADO ?

2- What can I do now to fix this mess I got into, since I clearly have a bunch of data that needs to be used, I figure that you will tell me to have the macro that cleans paste the data missing, thus pasting the data in my excel sheet?

3- If a quickfix is possible to what mentioned above, I would definatly like to use it, to have this fixed for monday morning, if not, ill live with working over the weekend.

4- I always really appreciate your work, thank you very much.[/blue]

Cordialy,

Julien






"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Just to update on the situation.

I currently have this macro that uses DAO to feed the table with the correct data, when I filtered everything to clean the trash.

[blue]I am not sure how to proceed though, should I remove all data, and rewrite everytime ? Any problem with this??


Should I modify data that needs modifying and remove data that shouldnt be there, wich seems alot more sound to me? (This would take alot longer to program, for what more?) [/blue]

If so, I am not sure how to process this into a macro, let me put it in code:

'The following loop feeds the table
With RstTimeSheet
     .ADdnew
         !IdProjet = Worksheets(1).RAnge("A" & i).Value

Lets say I chose solution 2, for any reason, how would I go into comparing Worksheets(1).RAnge("A" & i).Value with what is written in the table, and wouldnt that process be way to cpu costy ?

I would need at least to read three fields to validate that it is indeed the same entry, and then, I will need to check writings per writtings, make sure everything is correct.

If the Worksheets(1) entry is not found, I would need to create it, and if the entry in the database dosnt match any entry in the worksheets(1) I would need to delete it, and thus starting from a date to another date (there is a date field in the database)



If you want to broad an example, you can go with field 1, 2 and 3. IF you use something else than DAO, or specific functions, please, tell me.

Cordialy,

Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.