Null date values in Date/Time fields in Access DB 
Author Message
 Null date values in Date/Time fields in Access DB

Sorry if this is a newbie question, but I have a Access DB with
date fields. The problem is that this date can be left blank when the
record is created. If I do this (leave it blank) I then get 'type
mismatch' when I do a search for this value using an SQL query.
I use this
 if NOT Mydynaset("DateConfirmed") = ""
which gives 'Type Mismatch'
Should I be testing for IsNull?
Or should I make the field in the Database a Text field so that I can
have a blank (zero-length string) value? Surely there must be many databases
out there where a date field can be empty.

All help gratefully received.
--
Simon Rayner
Why not walk Offa's{*filter*} this year? 177 miles of uncrowded countryside.
The{*filter*} is more than 1200 years old. Email me for more details.



Wed, 05 Aug 1998 03:00:00 GMT  
 Null date values in Date/Time fields in Access DB
Quote:

> Sorry if this is a newbie question, but I have a Access DB with
> date fields. The problem is that this date can be left blank when the
> record is created. If I do this (leave it blank) I then get 'type
> mismatch' when I do a search for this value using an SQL query.
> I use this
>  if NOT Mydynaset("DateConfirmed") = ""
> which gives 'Type Mismatch'
> Should I be testing for IsNull?
> Or should I make the field in the Database a Text field so that I can
> have a blank (zero-length string) value? Surely there must be many databases
> out there where a date field can be empty.
>... Try something like:

  Detect Null fields in a database field faster.  Use code
  similar to that listed below (instead of using an IF IsNull).

   'string

   astr$ = data1.recordset.fields("strfld") & ""

   'numeric

   anum = data1.recordset.fields("numfld") & 0

Phil

VB Tips Page: http://pages.prodigy.com/MO/vbsite/



Wed, 12 Aug 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Populating Null value in SQL Date/Time field

2. Passing a NULL value into a date time field

3. ADO with Access Date/Time field not storing the time, just the date

4. How do you update a null date into an access table date field

5. Access date/time null values

6. Access date/time null values

7. combining a time and a date to one Date/Time value

8. Matching system date/time with field date/time

9. Date Time Picker & Null dates

10. Crystal Reports 8.5 date range parameter field - record selection that includes null date range

11. NULL VALUES IN DATE FIELD - TYPE MISMATCH ERROR

12. How to give Date fields null values?

 

 
Powered by phpBB® Forum Software