Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: What is best way to insert date from unbound textbox into recordset

    What is the best way to have a user enter a day (in the format dd/mm/yyyy) in an unbound textbox and afterwards save it into a recordset field (ie rcd![date]=text1)?

    I tried to put the input mask of the textbox to '00/00/0000', but when I inject into the recordset, it says the types don't match. (logically, because when you enter 31122004, it doesn't interprete it as a date).

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    When the user presses a command button, check the format then.

    If it isnt dd/mm/yyyy (20/03/2004) then tell user unable to update record as date format is not compliant.



    ~Or~

    You could try the calendar function on a form so they have to pick a date.

    My database seems to work fine.

    Rst.Fields("date_from") = F_Date

    If it doesnt work let me know and I'll try to get sometime to write you a simple form and table.

    ~Ken

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    OK, so I won't use the control validation.

    Could you tell me how to check the format, i.e. that the entered string complies to dd/mm/yyyy?

    Thx!!!

  4. #4
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    I have attached an Access2000 database.

    I used a 'cheating way'. !!!

    It accepts pretty much ALL date formats, when you use the datediff function BUT if it doesnt recognise the format it gives error 13. So logically I placed a message box in an erro catcher.

    Heres the code from one of the forms. Incase you haven't got Access 2000.

    The other form has a calender, I don't know if you have the ability to see it, but have ago anyway.

    It also checks for null values too..Just in case.



    Private Sub Command3_Click()

    On Error GoTo Wrong_Format

    If IsNull(Me.new_date) Then MsgBox "Please enter a date (dd/mm/yyyy)", vbOKOnly + vbInformation, "No Date": Exit Sub

    chk = DateDiff("d", Now(), Me.new_date)

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO Tbl_Dates_KH ( Days_here ) SELECT """ & Me.new_date & """ AS New_Date;"
    DoCmd.SetWarnings True
    Me.new_date.Value = ""
    Exit Sub
    Wrong_Format:
    If Err.Number = 13 Then
    MsgBox "Please re-enter the date formatted DD/MM/YYYY)", vbOKOnly + vbCritical, "Not correct format": Exit Sub
    Err.Clear
    Resume Next
    End If
    Return
    end sub


    See if this works.

    Ken
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Ken_Hart
    I have attached an Access2000 database.

    I used a 'cheating way'. !!!

    It accepts pretty much ALL date formats, when you use the datediff function BUT if it doesnt recognise the format it gives error 13. So logically I placed a message box in an erro catcher.

    Heres the code from one of the forms. Incase you haven't got Access 2000.

    The other form has a calender, I don't know if you have the ability to see it, but have ago anyway.

    It also checks for null values too..Just in case.



    Private Sub Command3_Click()

    On Error GoTo Wrong_Format

    If IsNull(Me.new_date) Then MsgBox "Please enter a date (dd/mm/yyyy)", vbOKOnly + vbInformation, "No Date": Exit Sub

    chk = DateDiff("d", Now(), Me.new_date)

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO Tbl_Dates_KH ( Days_here ) SELECT """ & Me.new_date & """ AS New_Date;"
    DoCmd.SetWarnings True
    Me.new_date.Value = ""
    Exit Sub
    Wrong_Format:
    If Err.Number = 13 Then
    MsgBox "Please re-enter the date formatted DD/MM/YYYY)", vbOKOnly + vbCritical, "Not correct format": Exit Sub
    Err.Clear
    Resume Next
    End If
    Return
    end sub


    See if this works.

    Ken
    Ken,

    The ONLY thing I hate about IsNull is if you have an empty string it fails ... If you clear a textbox you're left with an empty string not a NULL string ... How I test (that covers both) is:

    If TextBox.Value & "" ="" then 'Do something

    Also for checking for valid dates try: IsDate

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    Thank you, it works!!!

Posting Permissions

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