Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Emergency !!!!! (Access 97 SR 1)

    Using Access 97 SR1

    Greetings,

    I've inherited an emergency that I cannot seem to resolve in a payroll database. The DB is run strictly from Macro's. Hoping someone can help with this ASAP, as this process must be run this morning (Now 08:22 in Indiana)

    I need to add two APC codes to the 3 that are already in a MakeTable query called "IDT-INPUT - Table". When I add the two new APC, the macro crashes, displaying a parameter textbox asking for TA.

    Step1: Transfer Text from a remote file into a table called "IDT".
    Step2: Runs a Make Table query called "IDT-INPUT - Table". The query come from table "IDT". Note that this table is empty until the Transfer Text in Step1 is run. This is where it crashes if I add the two new APC's.

    Here is the SQL of the original Make Table Query:

    SELECT 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 INTO [IDT INPUT]
    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"));

    Here is the same SQL including the two new APC's (NOTE: This does not work for some reason):

    SELECT 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 INTO [IDT INPUT]
    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].[APC])="R155" Or ([IDT].[APC])="R157"));

    I'll keep checking back as often as I can, but any e-mail notifications are sent to my home e-mail address.

    Thanking all who reply in advance,

    Bob in Indy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    Bob,

    Check carefully that there is a field named TA in the most recent version of the IDT table. If it was present in previous incarnations, but not in the present one, that would cause the prompt for TA, it would have nothing to do with the addition of extra values for APC.

    As an experiment, you could omit IDT.TA from the SELECT part of the query and see if it runs then.

    Your WHERE condition can be written more efficiently using IN. If the above doesn't help, you could try and see if it makes the problem go away (I don't see why it should, but...) Here is the modified version:

    WHERE [IDT].[FY] In ("8","9","0","1","2","3","4") AND [IDT].[APC] In ("R151","R152","R153","R155","R157")

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    Hans,

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

    Best regards,

    Bob

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    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*"));

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    First of all, the fields in your new table will be name Expr1, Expr2, etc. Is that particularly useful?

    Is the IDT.FY field really a text field, or is it numeric?

    And as Hans suggested, using IN will make the query easier to read:
    ... WHERE IDT.FY IN ("0", "1", "2", "3", "4", "8", "9") AND IDT.APC IN ("R151", "R152", "R153")

    If this isn't working, run the query without the selection criteria on APC. Then look at the APC field in the results. Where any of them R151, R152, or R153?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    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

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    Sorry, Bob, but I hardly ever accept downloads for review, nor will I download attachments. Occasionally if it is a specific problem and there is no other practical way, then I might. It's just that I've found it becomes too easy for people to say "see attachment for my problem" rather than explaining it. Besides, this starts getting into what I do for a living (and for which I charge a consulting fee).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

    <Smiling>
    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.

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

    Bob in Indy

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query Emergency !!!!! (Access 97 SR 1)

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

    Hey, if you find a way, let me know! I'd like to get in on that also!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Emergency !!!!! (Access 97 SR 1)

    I'm sorry to say that most of the Access training I've seen is distinctly low level, at least the live instructor-led classes. Some of the CBT training from AppDev is pretty good, though.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •