Thursday, January 03, 2002

Have I mentioned how much I love working in Access databases? I love it when there is a problem and I can fix it. That's a great feeling. Even though given the size of the current database I'm working on, it's pretty much a needle in the haystack deal. Problem I diagnosed and fixed today affected about three reports - click on it and you get the following error message: "The specified field '[Staff Info].[Agreement Data]' could refer to more than one table listed in the FROM clause of your SQL statement."

Which, btw, can mean any variety of things when your database is as comprehensive as this one and where there are hundreds of fields in just the one table - the unique ones and then the ones that look something like "Agreement: IIf([Staff Licensing.Agreement Data]="0081","LSA4(TX)",IIf([Staff Licensing.Agreement Data]="0020","CE-1.2",IIf([Staff Licensing.Agreement Data]="0027","Life Sales Specialist",IIf([Staff Licensing.Agreement Data]="CE-4(TX-1)","CE-4(TX-1)",IIf([Staff Licensing.Agreement Data]="CE-4(TX-1-INC)","CE-4(TX-1-INC)",IIf([Staff Licensing.Agreement Data]="0021","CE-1.2(INC)",IIf([Staff Licensing.Agreement Data]="0057","LSA2(TX)",IIf([Staff Licensing.Agreement Data]="0058","LSA2(INC)(TX)",IIf([Staff Licensing.Agreement Data]="0082","LSA4(INC)(TX)",IIf([Staff Licensing.Agreement Data]="0093","LSA5(TX)",IIf([Staff Licensing.Agreement Data]="0094","LSA5(INC)(TX)")))))))))))". And yes, the error could maybe be embedded in there somewhere.

And there is the linked table issue - when I link to Corporate, unfortunately I cannot change date/time format - which means that 01/01/02 looks like 20020101. Lovely. I won't even start on the fact that there is a report out there where the query is mysteriously floating out in netherland - which gives me the sneaking suspicion that I may have to recreate the darn thing from scratch - something I'm not looking forward to at all. Creating and formatting reports make my eyeballs bleed even more than the above SQL statement.

Anyway, one error down, and I think I've got a handle on the second one. I'm hoping this is a case where if I fix a couple of the errors, everything else will fall into line, as I think, given the similarity of the #name? and error messages, that there could be a magic pill out there to fix the whole darn thing. A girl can always dream.

No comments: