Using contents of date/time field to set value of logical field

[cmiker]

I have done some database development with another database but am new to Access. I am using Access 2000. I am trying to set the value of a logical field (True or False) based on whether or not a date/time field is empty. I have been attempting to create an expression in the Table's Default Value for the logical field. I have tried several expressions using different functions and syntax with no success. Specifically, I want the True/False field, [Open Item] to show 'true" if the Date/Time field, [Call Closed Time] is blank or empty. If the [Call Closed Time] field has is not empty, then I want the [Open Item] field to show false. This seems like it should be simple, but I am missing it. The error I get when trying to save the changes to the table is as follows:
'The database engine does not recognize either the field 'Call Closed Time' in a validation expression, or the default value in the table."
Help!!

Mike

 

[Pat Hartman]

I can understand your confusion. The help entry for the Default value is hopeless. The help entry for Validation Rule is slightly better. But, the bottom line is that you cannot do what you are trying to do. What you are asking to do would need to be done with a trigger and Jet does not support triggers.

Also, you are creating a redundantfield. The yes/no field is not necessary at all since it's value is always defined by the value of another field in the table. If you want to show a logical field on your forms or reports, "calculate" it in a query or in a control's controlsource.

Select IIf(IsNull([Call Closed Time]), -1, 0) As [Open Item]
From YourTable;

PS, using embedded spaces or special characters is poor practice and may cause problems with VBA.

 

[namliam]

Pat,

Why use an IIF here?? I would use:

select NOT isnull([Call Closed Time]) as [Open Item] from...

All you are doing in the IIF is changing the true to false and vice versa which i do with the NOT...

Just another $0.02

Regards

The Mailman

 

[Pat Hartman]

Because when I write code for posters here, I use explicit instructions. Ones that a reader (even a relative newcomer) can read and understand rather than possibly more efficient but obtuse variations. And secondly, I consiously avoid negative logic because it is more difficult to understand.

 

[cmiker]

Thank you, Pat for your help. I have a working solution from your advice.
Also, thank you Mailman. Your two cents is much appreciated.

Mike

 

[namliam]

I agree on the negative logic part as do i on the negative question

Is it not posible to open a file?
No

Meaning either No, not posible (really affermative)
of No, it is posible (denail of the question)

However i tend to use things like this cause it seems to me to be much more efficient in programming sence... and might me easely commented... while increasing processing speed and such...

For instance i have seen ppl use:
If something then
'nothing here
else
'some code...
endif

That is not a good thing to do either (I think)

Regards