Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Updating Blank date field...

    I have a form, which pulls records in to update ( modify, close ) My problem is when i pull in a record I can make modifications no problem.. It allows me to update.. all fields except for the close date field.. When I leave the close date field empty ( since that record isnt closed .. yet ) it gives me an error when i try to update w/o a date in there.. ( type mismatch) but if i put a date in there .. everything works fine..

    Is there a way I can leave that field blank then update it w/o having to put a date in..

    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Assuming your data is in Access...

    Check the Required property on that field in the base table. Make sure
    it's set to No.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    thanks for the response redneckgeek..

    yes I'm using access 97 to store data and to create it... I dont see where its says anything about required ? could youshow me whwat you are talking about.. thanks

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    Hey red,

    I found out what you were talking about.. and It is set to "no" but I'm still getting the error.. datatype mismatch when I update my empty date field..

    any other ideas?? thanks

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What happens if you add a new record and don't put anything in the date field?
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    Well the only time I'm actually letting the user enter in the close date is on that udate form.. soo no where else is there a close date field.. when the user updates the record.. he/she has the option of changing info. to that record.. and closing that record.. i can update just fine.. but if i leave the date field blank .. i get that darn error.. data mismatch..

    thanks

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm sorry Tony.... I'm a little lost here.

    Is your user adding new data, or updating data that was somehow
    added somewhere else?

    If the problem is that the date WAS there, but the user removes it,
    then how exactly is the user removing it? If they just hit Delete, then it
    should convert the field to Null, which would be ok. If they use the spacebar to clear the data, that would cause a Data Type mismatch.

    You could got to the close date field's BeforeUpdate property and make sure the user put in a valid date before updating the data. If the user did not put in a date or Null, you could Undo their changes.

    Is there any way for you to attach your work so I can take a look at it?

    Mark
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    Mark,

    What's going on is.. First the user enters in data for a record... then the user can take that record and add sub-records to it.. these sub-records can be updated and also they need to put a closing date on then.. when they are done.. ( these sub-records are inspections ) Soo i have a form which adds records.. " frmsitecode " then you are able to search for these records in form "frmsearch".. from "fromsearch" theres a button where you can add inspections or subrecords ( hear i made the form continous - picking out 3 fields from the record .. for that sub-record..)

    In the form "frmaddinspec" you are able to add inspections for that record.. ( I can't get it to open up just by clicking on the form, I acutally have go to "frmsearch" and then clik add inspec.. to get to this form.. )

    ok the problem lies in form "frmeditinspec" here the user can go to update/close the inspections (sub-records) .. but they dont necessarily have to close the inspection.. they can go here just to update the inspection.. and my problem is they update it fine.. but if they do not enter in a closing date then they get the mismatch error..

    I've attached my .mdb


    thanks for the help
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Tony,
    I found it. The problem lies in the code behind the form.
    Replace this code in Command62 OnClick

    DATECLOSE.SetFocus
    f13 = DATECLOSE.Text & " "

    with

    DATECLOSE.SetFocus
    If DATECLOSE <> Null and isdate(dateclose) Then
    f13 = DATECLOSE.Text
    End If

    You've declared the variable f13 as a date, but by adding the
    & " " to the end, you are converting it to text.

    Mark
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    Mark,

    Ah yes it did update but.. why is it putting the time 12:00AM in my date field instead of leaving it blank ??

    thanks

  11. #11
    Join Date
    Jul 2003
    Posts
    292
    This is also wierd.. I check it and added real dates in the closedate field.. and updated it.. check in the table and 12:00am is written in that field.. instead of the date i enter... hmmm what coould be wrong ?

    thanks

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need to run 1 of 2 sql strings, depending on the value of f13

    If IsDate(f13) Then
    strsql = "UPDATE tblInspections SET tblInspections.CRT = '" & f2 & "', tblInspections.INSP = '" & f3 & "', tblInspections.ACC = '" & f4 & "', tblInspections.REJ = '" & f5 & "', tblInspections.FLT = '" & f6 & "', tblInspections.WF = '" & f7 & "', tblInspections.DISP = '" & f8 & "', tblInspections.HRS = '" & f9 & "', tblInspections.MIL = '" & f10 & "', tblInspections.REMARKS = '" & f11 & "' , tblInspections.INSPECTOR = '" & f12 & "', tblInspections.DATECLOSE = #" & f13 & "# WHERE tblInspections.ControlNo = '" & f1 & "'"
    Else 'f13 is still an empty variable, so don't put a value in DATECLOSE
    strsql = "UPDATE tblInspections SET tblInspections.CRT = '" & f2 & "', tblInspections.INSP = '" & f3 & "', tblInspections.ACC = '" & f4 & "', tblInspections.REJ = '" & f5 & "', tblInspections.FLT = '" & f6 & "', tblInspections.WF = '" & f7 & "', tblInspections.DISP = '" & f8 & "', tblInspections.HRS = '" & f9 & "', tblInspections.MIL = '" & f10 & "', tblInspections.REMARKS = '" & f11 & "' , tblInspections.INSPECTOR = '" & f12 & "' WHERE tblInspections.ControlNo = '" & f1 & "'"
    End If
    CurrentDb.Execute strsql
    Inspiration Through Fermentation

  13. #13
    Join Date
    Jul 2003
    Posts
    292
    Hey mark,

    I added the code .. and now .. it will not update.. I fi leave a blank field.. it says update.. no date.. that seems ok.. but when i go to enter in a close date.. i get nothing ..

    Thanks for sticking with me

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sorry I ran you in circles a little. Fighting too many fires at once today....

    The last thing I forgot:
    f13 = DATECLOSE.Text

    should be
    f13 = CDate(DATECLOSE.Text)

    This worked perfectly on my machine.

    Mark
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    Mark,

    I'm a little lost here..

    Code:
    Select Case MsgBox("Are you sure you would like to UPDATE?" & vbCrLf & vbLf & "  Yes:         Updates Record" & vbCrLf & "  No:          Does NOT Update Record" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Clicking UPDATE will ERASE all old information!")
            Case vbYes: 'Updates the changes
    	If IsDate(f13) Then
    		strsql = "UPDATE tblInspections SET tblInspections.CRT = '" & f2 & "', tblInspections.INSP = '" & f3 & "', tblInspections.ACC = '" & f4 & "', tblInspections.REJ = '" & f5 & "', tblInspections.FLT = '" & f6 & "', tblInspections.WF = '" & f7 & "', tblInspections.DISP = '" & f8 & "', tblInspections.HRS = '" & f9 & "', tblInspections.MIL = '" & f10 & "', tblInspections.REMARKS = '" & f11 & "' , tblInspections.INSPECTOR = '" & f12 & "', tblInspections.DATECLOSE = #" & f13 & "# WHERE tblInspections.ControlNo = '" & f1 & "'"
    	Else 'f13 is still an empty variable, so don't put a value in DATECLOSE
    		strsql = "UPDATE tblInspections SET tblInspections.CRT = '" & f2 & "', tblInspections.INSP = '" & f3 & "', tblInspections.ACC = '" & f4 & "', tblInspections.REJ = '" & f5 & "', tblInspections.FLT = '" & f6 & "', tblInspections.WF = '" & f7 & "', tblInspections.DISP = '" & f8 & "', tblInspections.HRS = '" & f9 & "', tblInspections.MIL = '" & f10 & "', tblInspections.REMARKS = '" & f11 & "' , tblInspections.INSPECTOR = '" & f12 & "' WHERE tblInspections.ControlNo = '" & f1 & "'"
    	End If
    	CurrentDb.Execute strsql
    
            Case vbNo: 'Do not delete or undo
                'Do nothing
    
            Case vbCancel: 'Undo the changes
                DoCmd.RunCommand acCmdUndo
                Me.tbProperSave.Value = "No"
    
            Case Else: 'Default case to trap any errors
                'Do nothing
    
        End Select
    where would i put the line of code you pointed out..?

    thanks

Posting Permissions

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