Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    7

    Unanswered: Data Type Conversion on All Date Fields

    Hi all,

    I'm very new to Access and VBA so I'm sure this is probably a simple fix but I can't seem to figure it out. I have a rather large database with multiple forms that record dates when investigations and assignments are completed. There are multiple dates as the assignment progresses and the managers approve it. The problem I'm having is I can't save anything unless all the dates are filled in. I keep getting a Data Type Conversion Error until the date fields all have something in them. The forms are unbound and I've programmed everything with VBA. Any help would be greatly appreciated. Thanks.

    PS. I've checked to make sure data types on the tables and the forms match up.
    Last edited by jsweeney76; 06-05-14 at 15:01.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    If you are using an update query for edit recs on the form,
    Before running it, be sure validate the form data:

    make sure all dates are valid dates... IsDate(txtDateIn)

    make sure the qry only updates flds with data, you cant update a date field with "", which is what you get on an empty txt box.

  3. #3
    Join Date
    Apr 2014
    Posts
    7
    Thanks for the quick reply. Would something like this work?

    If tbxDate = "" Then
    Next Value
    Else
    rst!Date = tbxDate

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Sounds about right, but also ..tbxDate = "" or IsNull(tbxDate) then

    But in Access you dont have to edit in recordsets. Queries do all the work without any code.
    The it do the work.

  5. #5
    Join Date
    Apr 2014
    Posts
    7
    Ok. I tried the code but it says that it needs a For after the Next Value?? How do I get around that? Sorry. I know this is basic stuff.

    I didn't realize I could use queries to edit. I already programmed it as recordsets. Maybe next time. LOL. Thanks.

  6. #6
    Join Date
    Apr 2014
    Posts
    7
    Does anyone have any idea why this is happening?? Upon further investigation it seems like it's any of the fields that are set to numbers or dates. I was able to enter the following code but it's not going to work if someone edits a record and deletes information in a field that previously had data in it. Any help would be greatly appreciated.

    If IsNull(tbxOpendate) = false Then
    rst!OpenDate = tbxOpenDate
    Else
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •