Results 1 to 10 of 10

Thread: crashing query

  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: crashing query

    Access 2003

    INSERT INTO Results ( SampleID, DoneYet, TraitCode, MethodID )
    SELECT NewResults.SampIDBarcode AS SampleID, False AS DoneYet, NewResults.TraitCode, 1 AS MethodID
    FROM Results RIGHT JOIN NewResults ON (Results.SampleID = NewResults.SampIDBarcode) AND (Results.TraitCode = NewResults.TraitCode)
    WHERE (((Results.ResultID) Is Null));

    This query crashes access, is there any obvious reason why?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    False AS DoneYet

    msaccess mite not like False been a feildname

    my 5 cent
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jun 2006
    Posts
    72
    Thank you for your 5 cents,

    False is the value and DoneYet is the feild name. I thought experimentation would tell me what is causing it but it's it's been a frustrating process due to the crashing itself and what causes it to crash seems inconsistent. I've given up doing it all with one query like this and have broken it up but if there is an obvious and easy answer for why it might be crashing it would be helpful to me in the future.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    There is a problem with Jet and boolean field (Yes/No field), but since you're passing False, logically, it shouldn't be a problem here. You might want to check out this article by Allen Browne, as it might be a chance it relates, http://allenbrowne.com/NoYesNo.html, but I don't really know.
    Roy-Vidar

  5. #5
    Join Date
    Jun 2006
    Posts
    72
    RoyVidar,

    Thank you, although I tried changing the feild to number and it still crashes, It's good to know that boolean feilds are flawed in that way. I also tried "0 as DoneYet" instead of false.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    By the by - I don't think you need the aliases in your SELECT statement
    You've tried using 0, why not play with 1 and -1 just to see if you can get a result.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's the crash (i.e. error)? And have you tried running it as a select statement only?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2006
    Posts
    72
    The error message is:

    Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.

    The information you were working on might be lost. Microsoft Office Access can try to compact and repair your open database.

    [Checkbox] Repair my open database and restart Microsoft Office Access

    Please tell Microsoft about this problem.

    We have created an error report that you can send to help us improve Microsoft Office Access.
    We will treat this report as confidential and anonymous.

    What data does this error report contain?

    Why should I report to Microsoft?

    Send Error report Don't Send (Buttons)
    I tried without aliases and still got the error. Also it happens sometimes even as a select rather than an insert.

    I've just found that the results table I'm trying to append the records to has a problem. It's not allowing me to even add results manually because the ResultID is an auto number and is trying to set itself to a number that already exists. I'm not sure if this is the cause of the problem or coincidental but either way I need to fix it.

    [Edit]
    I've fixed the results table and I still get the same error so I assume it's coincidental.
    Last edited by Access Junkie; 07-03-07 at 00:46.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are results and newsresults tables or queries?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2006
    Posts
    72
    NewResults is a query on a table that serves as the back end to a continuous form. Results is a table.

    I didn't want to make this too confusing so I didn't specify that or what the NewResults query does. I was happy to get a no there is no obvious reason. At the same time though if you guys want to get into the nitty gritty of this I'm happy to explain it in more detail. Keep in mind though I have found a way around this problem so this is really just an academic exercise for me now.

    SELECT TestingTable.SampIDBarcode, TestingTable.Testname, "HWT" AS TraitCode
    FROM TestingTable
    WHERE (((TestingTable.HWTC) Is Not Null));

    That is the SQL for NewResults. All NewResults does is pick up any samples for which HWT has been selected as a planned trait.
    Last edited by Access Junkie; 07-03-07 at 05:02.

Posting Permissions

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