Replies

Hans,

Thanks for the reply. I will check out the link, and also the Documentor. My apologies to anyone whom I may have offended. I’ve removed that sentence.

Regards,

Bob in Indy

Thank you, Hans. Is there another function that displays the DESCRIPTION for the tables, queries, etc? I include a description with all of my objects, and think it would be nice to have the ability to print a TOC of my objects and their descriptions.

Bob in Indy

Hi Dave,

How could I use this method to display queries, forms, reports, macros, and modules?

Also, (sounds dumb), but how can I view these hidden objects?

Mark,

Using Access 2000 (home). When I paste the first function into a new module, I get the following error:
Compile Error: Expected: List seperator or )

In fact, when I pasted the function, the first line is red…indicating an error. Would the code be different for A2K? Also, I use A97 SR1 at work. Will your function work with that?

Bob

No prob, Mark. But I think you’ve misunderstood. Didn’t intend my e-mail offer as a means of asking for assistance…but merely to show whomever was interested the crazy way this db was set up. I’ve already begun the process of revamping the db. I want to thank you and all the others that provide inspiration and answers in this forum.

….now, if I could just get the Government to pay for some Access training for me….

Bob in Indy

Mark,

Thanks for replying. I inherited the mess that generated my plea for help on Friday. It’s now working. As I said, I don’t pretend to understand why the person who created this app did it as she did. In the past I was forced to overhaul another app she had created which was similar in structure to the current mess.

I had intended to upload a copy of this mess, but even when I remove everything I can to reduce it’s size, it’s still over 100K (max upload limit). If anyone is interested in seeing this mess, let me know, and I can e-mail it to you.

I still don’t understand why she uses criteria in a Delete query that delete all records anyway. That’s what had me confused last Friday.

Bob in Indy

Hans,

This is my first opportunity to post again. Indeed, the IDT.TA field exists…but the table that the query comes from is deleted after the make-table runs. Your query didn’t work…but not your fault.

I don’t pretend to understand how this happens, but there was ANOTHER query….a DELETE query, where the APC criteria also existed….and I had to ensure that the same criteria from the Make-Table query also existed in the Delete query. I don’t understand why criteria is needed in this query, which seems to delete all of the data in the IDT table. That @#$%^&* Delete query was there all the time, but I didn’t look at it. I’m posting the SQL from this query for any who are interested.

I predict that I’ll be called upon to rewrite this app, as I’ve had to rewrite another similar app in the past. Thank you again for your effort, and the rapid response.

Bob in Indy (where it’s over 30 degress warmer today than yesterday !!)

DELETE DISTINCTROW IDT.TA AS Expr1, IDT.B1 AS Expr2, IDT.BLK AS Expr3, IDT.B2 AS Expr4, IDT.FY AS Expr5, IDT.B3 AS Expr6, IDT.DUEDATE AS Expr7, IDT.MANHRS AS Expr8, IDT.OC AS Expr9, IDT.B4 AS Expr10, IDT.EOE AS Expr11, IDT.B5 AS Expr12, IDT.APC AS Expr13, IDT.B6 AS Expr14, IDT.ODC AS Expr15, IDT.DOCNO AS Expr16, IDT.DOV AS Expr17, IDT.IC AS Expr18, IDT.AMOUNT AS Expr19, [IDT].[FY], [IDT].[APC], [IDT].[B2]
FROM IDT
WHERE ((([IDT].[FY])=”8″ Or ([IDT].[FY])=”9″ Or ([IDT].[FY])=”0″ Or ([IDT].[FY])=”1″ Or ([IDT].[FY])=”2″ Or ([IDT].[FY])=”3″ Or ([IDT].[FY])=”4″) AND (([IDT].[APC])=”R151″ Or ([IDT].[APC])=”R152″ Or ([IDT].[APC])=”R153″)) OR ((([IDT].[B2]) Like “T*”));

Hans,

Thanks for the quick reply! I’ll try it out and let you know what I find.

Best regards,

Bob

Hans, could you post the NetUser function, and show how one would use it?

Thanks!!

Hello again,

Here is what happens when I tried these examples….

If Me![HourTypeCode] Like “L*” And(Me![WC4] Not Like “GL*” Or Me![WC5] Not Like “GL*”) Then
*** This line yields a COMPILE Error

If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2) “GL” Then
*** The above line returns: Compile Error: Expected )

If Left(Me![HourTypeCode], 1) = “L” And (Left(Me![WC4], 2) “GL” Or Left(Me![WC5], 2)) “GL” Then
*** I tried adding an extra ). This line yields a Run Time Error ’13’ Type Mismatch.

I’m still playing with it, but with little success.

Bob

Hello Hans,

Yes, you’ve provided some guidance. Could you please post an example of how I could use the CASE statement? It appears more flexible. Thanks. I’m at work now, and will try the other examples you’ve shown.

Bob in Indy

(Pointing finger at self while quoting Forrest Gump…)

“Stupid is as stupid does…”

Bob (feeling stupid) in Indy

What happened to the attachment ?!?!?!?!?

The file on my PC has data in the table, but nothing in the queries nor report. I thought I could redownload the attachment from here, but there is no data in any of the queries nor report.

Anyone have any ideas ??

Hans,

Works like a charm. Fast, too!! Went through over 50K records in 2 seconds. I had another query that took 130 seconds.

It’s nice to know that there is such great help here.

Regards,

Bob in Indy

Hans,

Unbelievable !! I don’t think I would have though of designing the queries as you did. Sorry I have not responded before now, but I was placed onto another project. I’ve studied both attachments, and will certainly be able to use them for the intended data….and other applications.

Again, thank you!!

Bob in Indy