Is Null crashes Microsoft Access

Posted on 2007-08-03
Last Modified: 2013-11-05
When running the following query in Access it causes it to crash:

SELECT INGC_Call_Management.Status_History_Resolved_TIME, INGC_Call_Management.Call_Comments_1, INGC_Call_Management.Call_Comments
FROM INGC_Call_Management
WHERE (((INGC_Call_Management.Status_History_Resolved_TIME)>=[From - dd/mm/yyyy hh:mm:ss] And (INGC_Call_Management.Status_History_Resolved_TIME)<=[To - dd/mm/yyyy hh:mm:ss]) AND ((INGC_Call_Management.Call_Comments_1) Is Null));

All i need it to do is return all the blank Call_Comments_1 records which fall within the [From] and [To] criteria. But when I run the code it crashes access and closes it down.

Any ideas why or where I am going wrong?

Question by:ingenico
    LVL 10

    Accepted Solution

    Query looks normal.
    First, do a DECOMPILE of your database to make sure there's no corruption:
    1. Close database, quit Access
    2. Click on the Start Menu...Run.... MSACCESS.EXE /DECOMPILE
    3. Open the database
    4. Go to Tools....Database Utilities...Compact/Repair

    Now that you've done this, try the query again.  
    If you still have problems, try a different query--use literal date contant values in place of the parameterized parameters you have.  See if it runs

    Author Comment

    Works perfectly with literal dates, but I need the user to enter a date range.

    How can I get round this?
    LVL 58

    Assisted Solution

    Access should not crash with this query, and you might have data corruption. However, you should always declare your parameters when working with dates:

     [From - dd/mm/yyyy hh:mm:ss] Date,
     [To - dd/mm/yyyy hh:mm:ss] Date;
    SELECT INGC_Call_Management.Status_History_Resolved_TIME, [...]

    See also (Query | Parameters...) from the design view menu.


    Author Comment

    Still crashes even when I declare the parameters....

    Any other way of setting out this code with parameters? Can't see how the data is corrupt, its an external database sat on an Informix server containing about 1,000,000,000 records
    LVL 58

    Expert Comment

    In that case, I'm at a loss. I can't understand how declared parameters would be different from literals. Can you show us the query that works? (I have no experience with Informix...)
    LVL 10

    Expert Comment

    Well if literal values work, then here's what you do:

    1. Capture the date parameters in text boxes on a form
    2. Create a command button on that form that runs the query
    3. In the onClick event of the button put code in that builds a SQL string
    For example, let's say you have two fields on the form DateFrom and DateThru
    in your Onclick event you might do something like this:

    dim rs as recordset
    dim db as database
    dim sqlcmd as string

    sqlcmd= "<Your Basic Query Without a Where Clause>"  & " WHERE "
    sqlcmd= sqlcmd & " INGC_Call_Management.Status_History_Resolved_TIME Between "
    sqllcmd = sqlcmd & "#" & me!dateFrom & "# and #" & me!datethru & "#"

    set db= currentdb
    set rs= db.openrecordset(sqlcmd,dbopendynaset)

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    restore daily backup 5 52
    Duplicate + trim records SQL 11 40
    Remove Hyphens in Oracle SQL 5 35
    SQL Round a percentage 2 17
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now