asked on

Data type conversion error in Access

Still in Access.

Here is the code:

If .RecordCount = 1 Then
.MoveFirst
If IsEmpty(f!ISBN_ID.Value) = False Then
If IsEmpty(f!book_ID.Value) = False Then
If IsEmpty(f!IDNum.Value) = False Then
If IsEmpty(f!Publish_Date.Value) = False Then
If f!title_Qty > 0 Then
If f!bookPrice > 0 Then
Do Until .EOF
With out
.AddNew
!ISBN_ID = f!ISBN_ID.Value
!book_ID = f!book_ID.Value
!SEQ_NBR = 1
!ID NUM= f!IDNum.Value
!Publish_DATE = f!publish_Date.Value
!title_QTY = f!title_Qty.Value
!QTY_UOM = "PCS"
!Title_PRICE = f!title_price.Value
!Comment_1 = cmt!comment_1
!comment_2 = cmt!comment_2
!book_DESC = f!book_Desc.Value
.Update
End With
.MoveNext
Loop
End If
End If
End If
End If
End If
End If
Do Until .EOF

I get an "Data type conversion error." if I leave the date field blank on my form. Oddly, the error does not happen if I leave the date field blank the first time I enter my book information.
Two questions: 1. Why does my first entry work when I leave the date field blank? 2. How do I resolve the errror?

edtechdba

Have you tried doing a Debug.Print to verify the format of the date fields?
Something like this below.

If .RecordCount = 1 Then
        .MoveFirst
        If IsEmpty(f!ISBN_ID.Value) = False Then
                If IsEmpty(f!book_ID.Value) = False Then
                  If IsEmpty(f!IDNum.Value) = False Then
                    If IsEmpty(f!Publish_Date.Value) = False Then
                        If f!title_Qty > 0 Then
                            If f!bookPrice > 0 Then
                                    Do Until .EOF
                                        With out
                                            .AddNew
                                            !ISBN_ID = f!ISBN_ID.Value
                                            !book_ID = f!book_ID.Value
                                            !SEQ_NBR = 1
                                            !ID NUM= f!IDNum.Value

                                            '-------------------------------------------------------------
                                            'Verify format of date values
                                            Debug.Print f!Publish_Date.Value
                                            Debug.Print !Publish_DATE
                                            '------------------------------------------------------------- 

                                            !Publish_DATE = f!publish_Date.Value
                                            !title_QTY = f!title_Qty.Value
                                            !QTY_UOM = "PCS"
                                            !Title_PRICE = f!title_price.Value
                                            !Comment_1 = cmt!comment_1
                                            !comment_2 = cmt!comment_2
                                            !book_DESC = f!book_Desc.Value
                                            .Update
                                        End With
                                        .MoveNext
                                    Loop
                               End If
                        End If
                    End If
                End If
            End If
        End If
        Do Until .EOF

Open in new window

edtechdba

1. Please see above to work to resolve the data conversion issue.

2. Also, it looks like if your date field is blank or ... IsEmpty(f!Publish_Date.Value) = True, then the publish date field is not updated with the date value, which is probably why you are not having any data conversion issues when the field is left blank.

programmher

ASKER

I included the debug code and still get the error. I was able to output what is actually trying to be passed in a message box. The value is blank.

I also tried defaulting the date field to null and still got the error.

The error only happens when the date field is blank and this does not happen when the first title's information is entered - just every title's information after the first one. Very, very odd...

edtechdba

I don't doubt that you still received the error, I was just using that as a method for seeing exactly what is trying to be passed into the date field in order to match up the formats.

Are you saying that when you code:
If IsEmpty(f!Publish_Date.Value) = False

That a blank value is showing up as the Debug.Print value for f!Publish_Date.Value?

If so, how about trying:
If (IsEmpty(f!Publish_Date.Value) = False Or LTRIM(f!Publish_Date.Value) = '' Or IsNull(f!Publish_Date.Value) = True) Then ..

programmher

ASKER

Yes - a blank value is showing up as the value for the publish date.

I tried what you suggested and it did not like the single quotes. I modified it to this:

If (IsEmpty(f!Publish_Date.Value) = False Or LTrim(f!Publish_Date.Value) = "''" Or IsNull(f!Publish_Date.Value) = True) Then
and now get this error: "Invalid use of Null".

edtechdba

Oops! I'm sorry, here's the proper user of checking to see if a value is null.
Also updating the code to make sure no "blank" values pass through the IF statement.

If (IsEmpty(f!Publish_Date.Value) = False
Or LTrim(f!Publish_Date.Value) <> "''"
Or Not(IsNull(f!Publish_Date.Value))) Then

programmher

ASKER

I still get an "invalid use of null" error.

I copied and pasted the code to be sure that I did not miskey.

I had to include "_" to put the code on separate lines. I removed themto get all the codeon one line. I commented out the Print debug line and the "invalid use of null" error was resolved.

Now I am back to my original error of data type conversion error.

edtechdba

Removed parentheses around Not portion of IsNull criteria.

If (IsEmpty(f!Publish_Date.Value) = False
Or LTrim(f!Publish_Date.Value) <> "''"
Or Not IsNull(f!Publish_Date.Value)) Then

programmher

ASKER

Data type conversion error still.

als315

May be your date value is text? Try to convert it to date:
!Publish_DATE = Datevalue(f!publish_Date.Value)

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer