Null Date Query Results Inconsistent --

[Melee]

I thought this would be a piece of cake, but it has turned into a royal pain.

Goal: Open the Project Close frm showing only those record with no date in the ClosedDate field. Enter a date and be able to retrieve the record later if necessary to reopen the project.

I created a simple query with the ProjectName and the ClosedDate field. The only criteria used was Is Null in the date field.

I then created a form based on this query. Tested the functionality in the copy of the database on my personal computer. All seemed well. The records were those with no date. and then......

I installed it in the production database where it no longer functions correctly. The first two records I closed and reopened looked fine. Then the records retrieved included dates along with the null ones. (Reverse is true of the reopen frm) Nothing else changed.

Any ideas on why the inconsistencies? More importantly. any suggestions on how to resolve this issue?

 

[PhilipSchroevers]

Sorry, no idea..

Is the Production database also Access or is it (e.g.) a linked SQL server or Oracle DB?
Are there version differences between your development and production environment. What is the query code?

 

[Melee]

The databases are identical except for physical location.

I changed the form this morning to have the source as the table rather than the query filter. I then put the following query on the combo box for the record source and created a macro to open the form in edit mode.

The SQL for the query on the date field is

SELECT [Project tbl].[Project Name], [Project tbl].Close
FROM [Project tbl]
WHERE ((([Project tbl].Close) Is Null))
ORDER BY [Project tbl].[Project Name];

The query brings back the right records now, but the data input made to the Close field is not registering in the table. Now what?!

 

[PhilipSchroevers]

Strange...

Does does latest problem happen on both the Development and Production database?

If you want to, create a new application by merging the relevant form(s) and tables (etc.) and mail it to me offline: westwind@euronet.nl and I'll have a look at it.