Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unhappy Unanswered: Update A Datefield to blank value

    I'm trying to update a date field to a blank value from a date that is currently in the field.

    To update from blank to adding a date is not a problem, but reversing the process seams impossible..Thanks for any help

    <b>RequestReadyDate</b> is the field I'm trying to figure out how to set back to blank.


    Dim SQL1, SQL2, SQL

    SQL1 = "UPDATE Unitinfo SET MarketRent = '"& MarketRent &"', WS = '"& WS &"', GarageCarport = '"& GarageCarport &"', GarageCarportPrice = '"& GarageCarportPrice &"', Status = '"& Status &"', StaffNotes = '"& StaffNotes &"', PricingNotes = '"& PricingNotes &"', MRS_Punch = '"& MRS_Punch &"', MRS_Paint = '"& MRS_Paint &"', MRS_Maint = '"& MRS_Maint &"', MRS_Clean = '"& MRS_Clean &"', MRS_Carpet = '"& MRS_Carpet &"', MRS_Blinds = '"& MRS_Blinds &"', MaintNotes = '"& MaintNotes &"', CurrentWDStatus = '"& CurrentWDStatus &"', RequestedWDStatus = '"& RequestedWDStatus &"', CurrentCableStatus = '"& CurrentCableStatus &"', RequestedCableStatus = '"& RequestedCableStatus &"'"

    SQL2 = "WHERE Unit="& Unit &""

    If Not IsEmpty(RequestReadyDate) AND Trim(RequestReadyDate) <> "" Then
    SQL1 = SQL1 & ", RequestReadyDate = #"& RequestReadyDate &"#"
    End If

    Update = SQL1 & SQL2
    Conn.Execute( Update)

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    to set it to blank you need something like

    Code:
    If Not IsEmpty(RequestReadyDate) AND Trim(RequestReadyDate) <> "" Then
      SQL1 = SQL1 & ", RequestReadyDate = null"
    End If

  3. #3
    Join Date
    Feb 2004
    Posts
    19
    THanks.. I have it now hacked apart your code but the SQL1 line was what i need, THANK YOU SO MUCH!!


    Code now reads....

    If RequestReadyDate="" Then
    SQL1 = SQL1 & ", RequestReadyDate = null "
    End If

    If Not IsEmpty(RequestReadyDate) AND Trim(RequestReadyDate) <> "" Then
    SQL1 = SQL1 & ", RequestReadyDate = #"& RequestReadyDate &"#"
    End If

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Yup, that loks about right. I wasn't quite sure when you were wanting to set it to null so....

Posting Permissions

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