Replies

No Sir, you are not missing anything – I am; or was until I looked at your second reply. I could not see the forest for the trees. I was using the transfer spreadsheet method instead of transfer text.
It is working very well now and I cannot thank you enough – you made my weekend! I can now go outside and enjoy Houston’n sunny warm Sunday.
Thanks again,

itconc

Thanks for your answer.
We use office 2003 – no way to upgrade at this point.
I save the excel file as an htm. The import into Access works great manually.
However, I can not automate the import. I am between a rock and a hard place!

Most of the text file has a consistent structure:
– If the first two or three characters on a line aren’t spaces, they indicate a field name.
– Text from position 5 on the line forms the field value, together with the text on subsequent lines that start with spaces.
– A line that only consists of three spaces indicates that the next line starts a new record.
It’s possible to write code to convert this to a table with fields L22, AN, TI, IN etc.

But there are lines with a completely different structure, such as

Code:

FAN.CNT 1
     PATENT NO.          KIND   DATE        APPLICATION NO.        DATE
     ---------------     ----   --------    --------------------   --------
PI   US 7594994           B1    20090929    US 2005-244589         20051006
PRAI US 2005-244589             20051006     
ASSIGNMENT HISTORY FOR US PATENT AVAILABLE IN LSUS DISPLAY FORMAT

How should these be converted?

Thanks, Hans
I am not sure where to go from here, but have some ideas after reading your reply. I will play with this and see if I have any more questions.

The parentheses aren’t quite correct. Try

=IF(ISERROR(INDEX(lreturn,MATCH(F3,lcc,0))),”Not Found”,INDEX(lreturn,MATCH(F3,lcc,0)))

In fact, you only need to test whether MATCH returns an error:

=IF(ISERROR(MATCH(F3,lcc,0)),”Not Found”,INDEX(lreturn,MATCH(F3,lcc,0)))

That did it. Thanks, Hans

What was the minor change you made?

It would help if you could attach a copy of the workbook with sensitive information removed or altered.

Thanks to both of you.
Before I got ready to attach the file, I re-did all the column headings and lo and behold that was the problem. The minor change was to add a data series and adjust all the dynamic ranges I had.
Anyway, all is working again.
Thanks again!

Strange – it doesn’t work that way for me. Anyway, does the problem PC have Access 2003 SP3? If so, I’d install the two hotfixes released after SP3:

Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 – this one is a must, it solves a number of common problems.
Access 2003 post-Service Pack 3 hotfix package: November 21, 2007 – worth a try too, although less essential.

I have finally had a chance to try your suggestions.
On my machine I have no problems. On the client machine even the fixes did not work. However, your NZ function suggestion did. So the problem is fixed but we still do not know why this is happening on different machines.
Thanks for the help!
itconc

Strange – it doesn’t work that way for me. Anyway, does the problem PC have Access 2003 SP3? If so, I’d install the two hotfixes released after SP3:

Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 – this one is a must, it solves a number of common problems.
Access 2003 post-Service Pack 3 hotfix package: November 21, 2007 – worth a try too, although less essential.

Will try when I get back to the Arboretum.
Thanks Hans

I don’t know why it causes problems, but try this expression:

=[AvailableSpaces]-Nz([Events Subform]![Total Attendees],0)

Hans, I chnaged the control source as you recommended but got error messages when there are no records in the subform.
Thanks,

itconc

Thank you

Yes:

Code:

Sub FindIt()
  CommandBars.FindControl(ID:=1849).Execute
End Sub

Thanks to all of you!

We have an ecellent backup system. However, our control systems leave a lot to be desired. We are working on that.

If I had considerate users I would be so happy. All of our spreadsheets are treated in a mutli-user fashion.
Thanks for the input.

Thanks. I think that will be the only answer, although I will test the program mentioned.

I agree. However, these accountants work with many spreadsheets and it would be impossible to use Access for each one.