data type mismatch

[redFred]

In the following code snap shot I am trying to put the Date data in the variable HitD.

It works fine when there is date data in the field, but does not work when the field in null and I receive a type mismatch error.


Dim HitD As Date

Form_frmMain.HitDate.SetFocus
HitD = Form_frmMain.HitDate.Text

' any advice is much appreciated

 

[KenHigg]

HitD = nz(Form_frmMain.HitDate.Text,"")

???
ken

 

[redFred]

Ken, thanks for the quick reply, however

Still the same problem, works fine when there is a date in the field, but when the field contains no data, I get a error


Dim HitD As Date

Form_frmMain.HitDate.SetFocus

HitD = nz(Form_frmMain.HitDate.Text,"")
any advice is much appreciated

 

[KenHigg]

Oh, my bad. I suggest you change your var to a variant and instead of "" set it to '0'.

 

[boblarson]

Ken, thanks for the quick reply, however

Still the same problem, works fine when there is a date in the field, but when the field contains no data, I get a error


Dim HitD As Date

Form_frmMain.HitDate.SetFocus

HitD = nz(Form_frmMain.HitDate.Text,"")
any advice is much appreciated

Don't use the .Text property as you then do NOT have to set focus and also just use an If...Then...Else to test for null first:

Dim HitD As Date

If IsNull(frmMain.HitDate) Then
   ... do whatever if the value is null
Else
   HitD = frmMain.HitDate
End If

 

[KenHigg]

Don't use the .Text property as you then do NOT have to set focus and also just use an If...Then...Else to test for null first:

Dim HitD As Date

If IsNull(frmMain.HitDate) Then
   ... do whatever if the value is null
Else
   HitD = frmMain.HitDate
End If

Good catch Bob -

 

[redFred]

Thanks Ken - that did work, however....continuing ....now that I have either nothing or a data in the var HitD, when I try and upload to e new dataset, (in which I have no control over) I am getting a “Data type Conversion Error”