Learn how to a build a cloud-first strategyRegister Now


Is Null crashes Microsoft Access

Posted on 2007-08-03
Medium Priority
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
  • 2
  • 2
  • 2
  • +1
LVL 10

Accepted Solution

aesmike earned 1000 total points
ID: 19625809
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

ID: 19626308
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

harfang earned 1000 total points
ID: 19626354
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 19626500
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

ID: 19626606
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

ID: 19626880
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)


Expert Comment

ID: 20075436
Forced accept.

EE Admin

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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