Empty date field (type mismatch)

G

Guest

I'm having the worst time trying to fix a problem. I have a table with a
date/fime field (formatted as Short Date). On an unbound form I have the
textbox formatted as a Short Date, but whenever I try to pass the recordset
with an empty date field (the field may be empty due to various reasons) I
get a Type Mismatch. I know it's the date field because when I comment it
out, it works perfectly.

Here's the code:


*****************************************************************

Private Sub btnSavePromoDetails_Click()

On Error GoTo Err_btnSavePromoDetails_Click



' add a new details

' about events, thoughts and anything else



Dim rstANPD As Recordset



' create a recordset reference, then set its properties

Set rstANPD = New ADODB.Recordset

rstANPD.CursorType = adOpenKeyset

rstANPD.LockType = adLockOptimistic



' open the recordset

rstANPD.Open "tblCompanyServices", CurrentProject.Connection



' add a new record to tblCompanyServices

With rstANPD

.AddNew

.Fields("fldPromoID") = (Me!txtPromoIDTen)

.Fields("chkConnected") = (Me!chkConnected)
‘yes/no field

.Fields("fldConnectedDetails") = (Me!txtConnectedDetails)

.Fields("fldEventTitle") = (Me!txtEventTitle)

.Fields("fldPromoDate") = (Me!txtPromoDate) ' short date
Note: Even if I change it to Me!txtPromoDate.Value it doesn’t work

.Fields("fldPromoDetails") = (Me!txtPromoDetails) ' memo fld

.Fields("fldUniqueServices") = (Me!txtUniqueServices) ' memo fld

.Fields("fldUnderstanding") = (Me!txtUnderstanding) ' memo fld

.Update

End With



' clean up

rstANPD.Close

Set rstANPD = Nothing



Exit_btnSavePromoDetails_Click:

Exit Sub



Err_btnSavePromoDetails_Click:

MsgBox "Error in btnSavePromoDetails" & " " & Err.Description & “ “ & "
"Err.Number

Resume Exit_btnSavePromoDetails_Click


End Sub
 
G

Guest

Christina,

Try this:

If Not IsNull(Me!txtPromoDate) then
.Fields("fldPromoDate") = (CDate(Me!txtPromoDate))
End If

Don't insert anything if the txtPromoDate is null....

TomT
 
G

Guest

Tom,

It didn't work. I still get a type mismatch. As mentioned before if I
comment out the offending code it works or if I change the table field to
text.

This is just driving me nuts.
 
B

Brendan Reynolds

What if you adapt Tom's suggestion to check for empty strings as well as for
Null ...

If Not IsNull(Me!txtPromoDate) And Me!txtPromoDate <> vbNullString Then

Alternatively, you could try the IsDate function ...

If IsDate(Me!txtPromoDate) Then

IsDate returns False, without raising any error, for either a Null or an
empty string.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
G

Guest

The IsDate worked.

If IsDate(Me.txtPromoDate) Then
.Fields("fldPromoDate") = (Me!txtPromoDate)
End If

A big thank you to Tom and Brendan.

Chris
 
G

Guest

Christina,

I see you got this working, however I'm puzzled that the CDate solution
didn't work.

I created a test table "tbltest" with a numeric field "rec", and a date/time
field "testdate", and a form with an unbound text box "tstfld", formatted
short date. I entered a date, (e.g. 8/16 [Access changed this to 8/16/2004
when I exited the text box]), and ran the following code from a button on the
form:

Private Sub ok_Click()

Dim recset As New ADODB.Recordset

With recset

.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Open "tbltest", CurrentProject.Connection
.AddNew
.Fields("rec") = 1

If Not IsNull(Me![tstfld]) Then

.Fields("testdate") = (CDate(Me![tstfld]))

End If

.Update

End With

End Sub

This worked perfectly, and leaves me curious as to why it didn't work for
you....I can see where the IsDate might be more foolproof, if there is no
other validation on what gets entered in the date text box. I tend to do some
kind of validation on date entries because in my experience people can
mistakenly enter unintended dates (and Access will allow them), e.g. 1/2/504
entered, when the user intended to enter 1/25/04.

Anyway, glad to hear you've got it solved.

Tom
 
B

Brendan Reynolds

What seems to have been happening, Tom, is that the text box on Christina's
form contained an empty string. That means that the IsNull test would return
False, because an empty string is not Null, and so the next line of code
would be executed, passing the empty string to the CDate() function. The
CDate() function would then raise the error, because an empty string is not
a valid argument for that function.

I'm not sure just what circumstances would cause the text box to contain an
empty string rather than a Null, but the Len(ControlName & vbNullString) > 0
test will reliably catch both of them. Add Trim$() if you also want to catch
spaces.

I was once told, by a developer who's advice I usually respect, that I
should use <> 0 rather than > 0 in this test. But that's never made any
sense to me, as I can not imagine any circumstances under which the length
of a string can be less than zero! :)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


TomT said:
Christina,

I see you got this working, however I'm puzzled that the CDate solution
didn't work.

I created a test table "tbltest" with a numeric field "rec", and a date/time
field "testdate", and a form with an unbound text box "tstfld", formatted
short date. I entered a date, (e.g. 8/16 [Access changed this to 8/16/2004
when I exited the text box]), and ran the following code from a button on the
form:

Private Sub ok_Click()

Dim recset As New ADODB.Recordset

With recset

.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Open "tbltest", CurrentProject.Connection
.AddNew
.Fields("rec") = 1

If Not IsNull(Me![tstfld]) Then

.Fields("testdate") = (CDate(Me![tstfld]))

End If

.Update

End With

End Sub

This worked perfectly, and leaves me curious as to why it didn't work for
you....I can see where the IsDate might be more foolproof, if there is no
other validation on what gets entered in the date text box. I tend to do some
kind of validation on date entries because in my experience people can
mistakenly enter unintended dates (and Access will allow them), e.g. 1/2/504
entered, when the user intended to enter 1/25/04.

Anyway, glad to hear you've got it solved.

Tom


Christina said:
Tom,

It didn't work. I still get a type mismatch. As mentioned before if I
comment out the offending code it works or if I change the table field to
text.

This is just driving me nuts.
Click to expand...
Click to expand...
 
G

Guest

You're right, I meant to ask her what the value passed from the form was. I
always validate date entries before doing anything else, to make sure a valid
date is entered.

Thanks

Brendan Reynolds said:
What seems to have been happening, Tom, is that the text box on Christina's
form contained an empty string. That means that the IsNull test would return
False, because an empty string is not Null, and so the next line of code
would be executed, passing the empty string to the CDate() function. The
CDate() function would then raise the error, because an empty string is not
a valid argument for that function.

I'm not sure just what circumstances would cause the text box to contain an
empty string rather than a Null, but the Len(ControlName & vbNullString) > 0
test will reliably catch both of them. Add Trim$() if you also want to catch
spaces.

I was once told, by a developer who's advice I usually respect, that I
should use <> 0 rather than > 0 in this test. But that's never made any
sense to me, as I can not imagine any circumstances under which the length
of a string can be less than zero! :)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


TomT said:
Christina,

I see you got this working, however I'm puzzled that the CDate solution
didn't work.

I created a test table "tbltest" with a numeric field "rec", and a date/time
field "testdate", and a form with an unbound text box "tstfld", formatted
short date. I entered a date, (e.g. 8/16 [Access changed this to 8/16/2004
when I exited the text box]), and ran the following code from a button on the
form:

Private Sub ok_Click()

Dim recset As New ADODB.Recordset

With recset

.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Open "tbltest", CurrentProject.Connection
.AddNew
.Fields("rec") = 1

If Not IsNull(Me![tstfld]) Then

.Fields("testdate") = (CDate(Me![tstfld]))

End If

.Update

End With

End Sub

This worked perfectly, and leaves me curious as to why it didn't work for
you....I can see where the IsDate might be more foolproof, if there is no
other validation on what gets entered in the date text box. I tend to do some
kind of validation on date entries because in my experience people can
mistakenly enter unintended dates (and Access will allow them), e.g. 1/2/504
entered, when the user intended to enter 1/25/04.

Anyway, glad to hear you've got it solved.

Tom


Christina said:
Tom,

It didn't work. I still get a type mismatch. As mentioned before if I
comment out the offending code it works or if I change the table field to
text.

This is just driving me nuts.

:

Christina,

Try this:

If Not IsNull(Me!txtPromoDate) then
.Fields("fldPromoDate") = (CDate(Me!txtPromoDate))
End If

Don't insert anything if the txtPromoDate is null....

TomT

:

I'm having the worst time trying to fix a problem. I have a table with a
date/fime field (formatted as Short Date). On an unbound form I have the
textbox formatted as a Short Date, but whenever I try to pass the recordset
with an empty date field (the field may be empty due to various reasons) I
get a Type Mismatch. I know it's the date field because when I comment it
out, it works perfectly.

Here's the code:


*****************************************************************

Private Sub btnSavePromoDetails_Click()

On Error GoTo Err_btnSavePromoDetails_Click



' add a new details

' about events, thoughts and anything else



Dim rstANPD As Recordset



' create a recordset reference, then set its properties

Set rstANPD = New ADODB.Recordset

rstANPD.CursorType = adOpenKeyset

rstANPD.LockType = adLockOptimistic



' open the recordset

rstANPD.Open "tblCompanyServices", CurrentProject.Connection



' add a new record to tblCompanyServices

With rstANPD

.AddNew

.Fields("fldPromoID") = (Me!txtPromoIDTen)

.Fields("chkConnected") = (Me!chkConnected)
'yes/no field

.Fields("fldConnectedDetails") = (Me!txtConnectedDetails)

.Fields("fldEventTitle") = (Me!txtEventTitle)

.Fields("fldPromoDate") = (Me!txtPromoDate) ' short date
Note: Even if I change it to Me!txtPromoDate.Value it doesn't work

.Fields("fldPromoDetails") = (Me!txtPromoDetails) ' memo fld

.Fields("fldUniqueServices") = (Me!txtUniqueServices) ' memo fld

.Fields("fldUnderstanding") = (Me!txtUnderstanding) ' memo fld

.Update

End With



' clean up

rstANPD.Close

Set rstANPD = Nothing



Exit_btnSavePromoDetails_Click:

Exit Sub



Err_btnSavePromoDetails_Click:

MsgBox "Error in btnSavePromoDetails" & " " & Err.Description & " " & "
"Err.Number

Resume Exit_btnSavePromoDetails_Click


End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro: Changing the field type from tx to date in Macros 3

Top