filtering invalid data type 1

[Adamba]

Hi All

Im not sure if there is a previous post as am not sure what to search for. In my search i have not found anything of use so i apologise if i am repating someone elses question.


I am Linking/Importing (have tried both) to a text file source that i have tidied the formatting of in some previous scripting so that it can be used fairly easily. but am finding that with all my cleaning for values greater than 0, etc.. i am getting funny fields that cause later queries to fail.

On investigating the problem i am recieving #num! fields which will just crash any form of calculation in a query rather than exclude/ignore it.

All my best efforts so far to filter this out seem to not work or it isnot a valid field reference.

Question:
How do i filter out/exclude these records from the table (query or vba based)

File Format:
WeekEnd | CuID | £ Earned
26/10/04| 10000000 | 10
24/05/04| 10000000 | #Num!
(Hope that helps a little)

as i say i am in a query filtering out all '£ Earned' that are greater than zero to get rid of my Zero/Null values but these #Nums! will not dissapear.

Have i waffled on a lil too much? i do apologise again if i have.

MANY THANKS in advance



Adam

 

[BNPMike]

Have you looked at the source data to find out why it is not being accepted? If it is because it contains non-numerics then you will first need to bring the data into a table with an alphanumeric field type. Perhaps Access is being deceived into thinking the field is numeric when it is not. Once you have the data in a table you can remove or otherwise 'correct' the aberrent values.

My tip is to add a dummy row to the start of the imported file. For any dodgy fields, set the value to be 'ABCD' for example. Access will then set this to alpha.

Once the data is imported you can delete the first row (give it some key you can reliably identify it with), and then set about cleaning the data.

Of course, Plan B is clean your data from its source.

 

[Adamba]

BNPMike,

Why is it always so simple! ive spent days looking for complex ways of filtering them out!

Many thanks for the simple answer
Turns out the fields had ? in them but when your looking at over 1 million records and in number format i was just missing this fact.

Once again many thanks and a star to you




Adam

 

[BNPMike]

Adamba

Thanks, and good luck.

Stars are always appreciated