Access date/time null values 
Author Message
 Access date/time null values

Hi

I have a bit of a tricky one!

I have 2 fields in an Access 2000 database, CloseDate and CloseTime
that have their properties set to shortdate and longtime respectively.

My app fills in these values without any problem.

However the problem comes when on the rare occasion I will have to
re-open these closed fields and set the date back to blank.
I can do this by opening Access and manually deleting the record but I
can find no way of doing it from my app.

I Have tried setting the corresponding text boxes to "" and " " but
this just generated an error "Data type mismatch in expression".

Does anyone know of a way to do this from VB or will I just have to do
this manually?

I could set the fields back to plain text but this would mess up my
reports when I have to produce reports based on dates/times.

Does anyone have any ideas?

thx.

--
PJ



Thu, 06 Jan 2005 02:12:48 GMT  
 Access date/time null values

[...]

Quote:
> I have 2 fields in an Access 2000 database, CloseDate and CloseTime
> that have their properties set to shortdate and longtime respectively.
[...]
> However the problem comes when on the rare occasion I will have to
> re-open these closed fields and set the date back to blank.
[...]
> I Have tried setting the corresponding text boxes to "" and " " but
> this just generated an error "Data type mismatch in expression".

You've given yourself the answer in the subject line. Rather than
attempting to set the fields value to " " (how on earth is that supposed
to be a valid date?) or "" (it's a date, not a string), set them to
NULL. The whole purpose of NULL in database fields is to indiciate the
abscense of a value.

Even if you were dealing with a string field, "" <> NULL. The former
indicates a value that is empty, the latter that no value is present.

hth

Adam
--
I'm glad I was not born before tea.
               -- Sidney Smith (1771-1845)



Thu, 06 Jan 2005 02:38:05 GMT  
 Access date/time null values
On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt"

Quote:


>[...]
>> I have 2 fields in an Access 2000 database, CloseDate and CloseTime
>> that have their properties set to shortdate and longtime respectively.
>[...]
>> However the problem comes when on the rare occasion I will have to
>> re-open these closed fields and set the date back to blank.
>[...]
>> I Have tried setting the corresponding text boxes to "" and " " but
>> this just generated an error "Data type mismatch in expression".

>You've given yourself the answer in the subject line. Rather than
>attempting to set the fields value to " " (how on earth is that supposed
>to be a valid date?) or "" (it's a date, not a string), set them to
>NULL. The whole purpose of NULL in database fields is to indiciate the
>abscense of a value.

>Even if you were dealing with a string field, "" <> NULL. The former
>indicates a value that is empty, the latter that no value is present.

>hth

>Adam

Hi

Thanks for the reply.
Sorry I am new to VB and access.

Are you saying it is possible?
If so how do I set it to Null?

I have the following query string:

    strSQL = "UPDATE calls SET [Status]='"
    strSQL = strSQL & txtStatus.Text
    strSQL = strSQL & "', [CloseDate]='"
    strSQL = strSQL & txtCloseDate.Text
    strSQL = strSQL & "', [CloseTime]='"
    strSQL = strSQL & txtCloseTime.Text
    strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

Thanks for any help.

--
PJ



Thu, 06 Jan 2005 04:27:32 GMT  
 Access date/time null values

Quote:

> On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt"


[...]
>>You've given yourself the answer in the subject line. Rather than
>>attempting to set the fields value to " " (how on earth is that supposed
>>to be a valid date?) or "" (it's a date, not a string), set them to
>>NULL. The whole purpose of NULL in database fields is to indiciate the
>>abscense of a value.
[...]
>    strSQL = strSQL & "', [CloseDate]='"
>    strSQL = strSQL & txtCloseDate.Text
>    strSQL = strSQL & "', [CloseTime]='"
>    strSQL = strSQL & txtCloseTime.Text

Replace with:

  strSQL = strSQL & "', CloseDate=" & IIf( Trim$( txtCloseDate.Text )  _
    = "", "NULL", "'" & txtCloseDate.Text & "'" ) & ", CloseTime = " & _
    IIf( Trim$( txtCloseTime.Text ) = "", "NULL", "'" & _
    txtCloseTime.Text & "'")

In each case, if the textbox is empty, NULL is passed, otherwise the
contents of the textbox are passed surrounded by single quotes.

Quote:
>    strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

                        ^                    ^^^^^                    ^

Remove the closing single quote there, as it's been dealt with by the
previous line. Remove the CInt() as it's redundant - you're taking a
string, and concatenating it to a second string; converting to and from
an Integer in the process is pointless and unneccessary.

Wherever possible, avoid multiple string concatentations (repeated
strSQL = strSQL &... in your example). They're slower and more expensive
than simply continuing the assignment over multiple lines, as in my
rework above.

I'd also suggest the acquisition of a good database theory book,
together with a VB book. None of the above is particularly complicated,
although it may seem that way to a novice.

hth

Adam
--
"Do not go where the path may lead, go instead where there is no path
and leave a trail."
               -- Ralph Waldo Emerson (1803-1882)



Thu, 06 Jan 2005 05:01:16 GMT  
 Access date/time null values
On 20 Jul 2002 22:01:16 +0100, "Adam D. Barratt"

Quote:


>> On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt"


>[...]
>>>You've given yourself the answer in the subject line. Rather than
>>>attempting to set the fields value to " " (how on earth is that supposed
>>>to be a valid date?) or "" (it's a date, not a string), set them to
>>>NULL. The whole purpose of NULL in database fields is to indiciate the
>>>abscense of a value.
>[...]
>>    strSQL = strSQL & "', [CloseDate]='"
>>    strSQL = strSQL & txtCloseDate.Text
>>    strSQL = strSQL & "', [CloseTime]='"
>>    strSQL = strSQL & txtCloseTime.Text

>Replace with:

>  strSQL = strSQL & "', CloseDate=" & IIf( Trim$( txtCloseDate.Text )  _
>    = "", "NULL", "'" & txtCloseDate.Text & "'" ) & ", CloseTime = " & _
>    IIf( Trim$( txtCloseTime.Text ) = "", "NULL", "'" & _
>    txtCloseTime.Text & "'")

>In each case, if the textbox is empty, NULL is passed, otherwise the
>contents of the textbox are passed surrounded by single quotes.

>>    strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)
>                        ^                    ^^^^^                    ^

>Remove the closing single quote there, as it's been dealt with by the
>previous line. Remove the CInt() as it's redundant - you're taking a
>string, and concatenating it to a second string; converting to and from
>an Integer in the process is pointless and unneccessary.

>Wherever possible, avoid multiple string concatentations (repeated
>strSQL = strSQL &... in your example). They're slower and more expensive
>than simply continuing the assignment over multiple lines, as in my
>rework above.

>I'd also suggest the acquisition of a good database theory book,
>together with a VB book. None of the above is particularly complicated,
>although it may seem that way to a novice.

>hth

>Adam

Hi Adam

Brilliant! thanks very much :)

That did it and I will take the advice about the books.

thx

--
Paj



Thu, 06 Jan 2005 06:00:07 GMT  
 Access date/time null values
SQL = "UPDATE MyTable SET CloseDate = NULL, CloseTime = NULL WHERE ( .... );

BTW, there's no need to have two fields for this. Date values include the
time. ShortDate and LongTime are display formats, that have nothing to do
with the field's data format.


Quote:
> Hi

> I have a bit of a tricky one!

> I have 2 fields in an Access 2000 database, CloseDate and CloseTime
> that have their properties set to shortdate and longtime respectively.

> My app fills in these values without any problem.

> However the problem comes when on the rare occasion I will have to
> re-open these closed fields and set the date back to blank.
> I can do this by opening Access and manually deleting the record but I
> can find no way of doing it from my app.

> I Have tried setting the corresponding text boxes to "" and " " but
> this just generated an error "Data type mismatch in expression".

> Does anyone know of a way to do this from VB or will I just have to do
> this manually?

> I could set the fields back to plain text but this would mess up my
> reports when I have to produce reports based on dates/times.

> Does anyone have any ideas?

> thx.

> --
> PJ



Thu, 06 Jan 2005 08:29:07 GMT  
 Access date/time null values
You've already got advise on how to use Null. I'd just like to comment that
it's usually MUCH better to have a single date/time field than storing them
as two separate fields. For those occasions where you need only the date
portion, or only the time portion, you can use the DateValue and TimeValue
functions.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> Hi

> I have a bit of a tricky one!

> I have 2 fields in an Access 2000 database, CloseDate and CloseTime
> that have their properties set to shortdate and longtime respectively.

> My app fills in these values without any problem.

> However the problem comes when on the rare occasion I will have to
> re-open these closed fields and set the date back to blank.
> I can do this by opening Access and manually deleting the record but I
> can find no way of doing it from my app.

> I Have tried setting the corresponding text boxes to "" and " " but
> this just generated an error "Data type mismatch in expression".

> Does anyone know of a way to do this from VB or will I just have to do
> this manually?

> I could set the fields back to plain text but this would mess up my
> reports when I have to produce reports based on dates/times.

> Does anyone have any ideas?

> thx.

> --
> PJ



Thu, 06 Jan 2005 19:44:02 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Access date/time null values

2. Null date values in Date/Time fields in Access DB

3. Populating Null value in SQL Date/Time field

4. Null values in Date Time Picker

5. Date Time Picker and null value

6. Passing a NULL value into a date time field

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

8. Date Time Picker & Null dates

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

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

11. how to set a date time value different from system time in the status bar

12. NULL VALUES IN DATE FIELD - TYPE MISMATCH ERROR

 

 
Powered by phpBB® Forum Software