Date type Variable - Type Mismatch

[mané_uk]

Hi,

I have a form in which I capture 2 dates and 2 amounts in text fields.
Once the user click on the "save" button it should pass the value to some
variables but there's an error message coming up when I leave any of the date
fields in blank - error 13: type mismatch. (It's part of the requirements to
accept blank date!!)

If I debug it and hover through my date variables it shows:
"dteInterimDteOrder = 00:00:00". Looks as if it's time but I had declared
them as date.

If I hover through my text field value it seems ok:
txtInterimDteOrder.Value = ""

What am I doing wrong? The code used to declare the variables and
allocate the text fields value into the variables are below.


Dim dteInterimDteOrder As Date
Dim dteFinalDteOrder As Date
Dim strInterimAmtRqst As String
Dim strFinalAmtRqst As String

dteInterimDteOrder = txtInterimDteOrder.Value
strInterimAmtRqst = txtInterimAmtRqst.Value
dteFinalDteOrder = txtFinalDteOrder.Value
strFinalAmtRqst = txtFinalAmtRqst.Value

Many thanks
Mané

ps.: The debug "stops" in the first line "dteInterimDteOrder =
txtInterimDteOrder.Value"

 

[Douglas J. Steele]

Try

dteFinalDteOrder = CDate(txtFinalDteOrder.Value)

 

[mané_uk]

Douglas,

Thanks for your prompt response, but it is still not working. The same
error message.

I have hovered through the CDate command while on debug and it shows:
CDate(txtFinalDteOrder.Value) = <type mismatch>

Could it be because the value of the text field is blank? Any other
suggestion please?

Thanks

 

[Douglas J. Steele]

Yes, date variables cannot be null.

If Len(Me!txtFinalDteOrder & vbNullString) = 0 Then
dteFinalDteOrder = CDate(Me!txtFinalDteOrder)
End If

 

[mané_uk]

Hi Douglas,

My problem is that I need this date fields to be empty if necessary. The
variables will be included into a SQL statement to populate my SQL back end.

For example, one of the date fields will be used to capture when the user
close the case he/she is working on, therefore its initial set up should be
blank and just be populated later on in the process when the case is really
closed.

Would you have any ideas of work around it?

Thanks again!!

 

[Douglas J. Steele]

You have to check whether the value exists or not and exclude the field from
the SQL statement if it doesn't.