Make table from linked Excel file...but exclude "errors"?

[Big Pat]

Hi,

My colleague has a large Excel file in which several columns contain dates. She updates this constantly, but now I need to take a weekly 'snapshot' of it to do further work in Access. So I'm using a make-table query.

But for some records, she enters "N/A" or even "Ignore this one" (I know! ) in certain columns. I've told her this is bad practice from a data viewpoint but she's had the spreadsheet a long time and doesn't wan't to change it. Her system works for her! When I open the spreadsheet via Access, these values show up as #Num!

The problem is that when I try to add those fields to the make-table query, I get a "Numeric field overflow" error message. So what I would like to do is simply convert these errors to NULL. But I can't make it work. I was hoping IIF(IsDate([MyDate]),NULL,[MyDate]) would work, but it doesn't. To try to work out why, I simplified it to IsDate([MyDate]) to see whether I got ones or zeroes, but if there was #Num! in MyDate, then I also got #Num! from the IsDate function.

Suggestions please! I think I've run up against a brick wall, but as usual there's probably something I'm missing.

Thanks,

 

[reddwarf]

sounds simple - but with excel tables i prefer "" instead of null. So - it would be

IIF([MyDate]="n/A" or [mydate]="Ignore this one", "",[MyDate])

mydate could be any field name ?

now, i see your making a new table with a query - is there any reason you are not importing the data using the GET EXTERNAL DATA - set up a table with all the formatting you need, call it MYTABLE_BLANK - then copy structure only and call that MYTABLE_DATE - then use the get external data to import - access will sort out the errors and not import them as the format will be incorrect. then - to snapshot, just keep pasting data into new tables saving them with a date in the name. make sense ?>

 

[Big Pat]

I'm happy to go with "" instead of NULL.

Right, I see where you're going with your formula, but (and I should have clarified this) there's no telling what text she might enter instead of a date. I've seen "Awaiting confirmation", I've seen "Postponed" etc. In reality, she could enter just about anything and won't agree to have it limited to a drop-down list. She insists it must be free text. So rather than having a formula check for specific things, I'd just like to check whether the entry is a date.

Why am I not using GET EXTERNAL DATA? Two reasons:
1) I'm pretty amateur at this stuff, so it honestly hadn't occurred to me.
2) But now that I think about it, I was hoping to hand this over to a junior colleague when I got it right and the make table query would be run via a command button. I can just about manage to create one of those if the code is simple! Could I still use a command button to run the get-external-data process? What would be the code for that?

One further complication (which I'd got round using the query) is that in her spreadsheet she uses an identifier which is three to five digits, followed by forward slah and then three letters. Because my part of the process will have to link to another database which uses numeric record identifiers, I have to strip off the last four characters, so I'm using CLng(Left([Study ID],Len([Study ID])-4)), so that would have to be factored in.

 

[reddwarf]

having a button to get external data isnt easy to be honest - you can do it though using http://www.access-programmers.co.uk/forums/archive/index.php/t-169481.html. that said, most of the time when i use the get data featyre i run into problems if a field isnt formatted corrcetly or is to big etc etc. it will paste the errors into another sheet, but that defeats to objkect of having a one button solution.

best thing you can do is look at the cource of the data - make a new spreadsheet for your co-worker that uses Standard Drop down boxes for ansers and restricts formats in cells - then you can easily import the data. always best to start at the source of the problem than try to make fixes for it half way through.

 

[Big Pat]

Hi,
Thanks for reply.

So there's no way that you know of to have a make-table query that would exclude anything that wasn't a date (without specifying beforehand what the entry was)? Damn...that's going to make things awkward.

See post #3 for previous comment about standard dropdown boxes. I may just have to go back and have this conversation with my colleague again, though I'm not hopeful about the outcome! (She's far more senior than humble me!)

On reflection it does sound like your previous suggestion is probably the best one and the link you gave me will be very useful.

Further suggestions always welcome, but I think I'll give that approach a go.

Thank you.

 

[reddwarf]

Seniority does not reflect knowledge. I always find the rule "make it first - then make it sound like their idea and you have a winner. " if you can show them how it easy it is without having to exlplain it has more chance of being used.

I spend most of my time as a DB admin working on ways to make sure the data going in is corrcet - which invloves much validation and lots of cleaning. the stricter you are at getting good data in the less time you spend getting good data out.

 

[gemma-the-husky]

personally, i would import the spreadsheet into a new (but preferably pre-designed existing) table in your database. then you can fix the date issues without compromising the actual data in the spreadsheet.

the rogue column will come in as text, so you need some functionality to add an extra date column and change the text into a valid date where appropriate.

having an access table will also make all your processing quicker.