Solved Update Date Field to Blank (2 Viewers)

[NatDB]

Hi Everyone,

I am new to using Access and need a bit of help with the below.

My DB has an end date for closed accounts. Some customers have returned and I want to remove the end date on their record. I have a added a new data source containing the customers details and all data matches. Happy with this.

I have said the following:

Field: Closed Date
Table: Customer Records
Criteria: Is Not Null And <>""
Update To: ""

Is the above going to cause any issues or is there something more accurate I can add to remove the date instead of the quotation marks?

Many thanks

 

[Minty]

You can't set a date field to an empty string (or zero length string ZLS as it is also known).
You can set it to null though.

If possible before you get too stuck into the design remove all the spaces and any special characters from your field names.
Typing ClosedDate is a lot easier than typing [Closed Date]

 

[NatDB]

Thank you @Minty I will amend the Field name and other field names in my DB.

I will give it a go and thank you for the information regarding ZLS.

 

[The_Doc_Man]

Regarding "end dates" - Rather than using nulls or a ZLS, I used essentially an impossible date as the marker.

When dealing with dates in Access, you can legally display any date from 1-Jan-100 to 31-Dec-9999. If I had some event that wasn't done yet, I always marked its closure as 31-Dec-9999, which is easy to test for. I also used 1-Jan-100 for anything in the past but for which the exact past date was totally unknown.

The most recent project where I used that was my genealogy database where someone who died on an unknown date (where I didn't even know which century) was marked as dead on 1-Jan-100 and then used it as the "flag" for unknown. Those who haven't died yet got death date 31-Dec-9999, of course meaning "still alive and kicking." Sort of like Star Trek's Spock when he says "live long and prosper."

Before you ask, sometimes death dates are not available in church records, perhaps because the person's birth was recorded but they moved away and the church's rector couldn't update the date of death. And for ancestry records, sometimes those church records are all you have. So I used the "impossible" date as a flag for "probably dead but damned if I know when."

 

[ebs17]

UPDATE
   CustomerRecords
SET
   ClosedDate = NULL
WHERE
   ClosedDate > 0
      AND
   CustomerID IN(23, 34, 38)

Some customers have returned

Some are certainly not all. Therefore, you should specify which one you mean in an additional criterion.

 

[NatDB]

Regarding "end dates" - Rather than using nulls or a ZLS, I used essentially an impossible date as the marker.

When dealing with dates in Access, you can legally display any date from 1-Jan-100 to 31-Dec-9999. If I had some event that wasn't done yet, I always marked its closure as 31-Dec-9999, which is easy to test for. I also used 1-Jan-100 for anything in the past but for which the exact past date was totally unknown.

The most recent project where I used that was my genealogy database where someone who died on an unknown date (where I didn't even know which century) was marked as dead on 1-Jan-100 and then used it as the "flag" for unknown. Those who haven't died yet got death date 31-Dec-9999, of course meaning "still alive and kicking." Sort of like Star Trek's Spock when he says "live long and prosper."

Before you ask, sometimes death dates are not available in church records, perhaps because the person's birth was recorded but they moved away and the church's rector couldn't update the date of death. And for ancestry records, sometimes those church records are all you have. So I used the "impossible" date as a flag for "probably dead but damned if I know when."

Thank you, this gives me something to think about to and I like the Spock reference

Many thanks

 

[NatDB]

UPDATE
   CustomerRecords
SET
   ClosedDate = NULL
WHERE
   ClosedDate > 0
      AND
   CustomerID IN(23, 34, 38)

Some are certainly not all. Therefore, you should specify which one you mean in an additional criterion.

Thank you, much appreciated

 

[Pat Hartman]

In order to make queries easier, I also use an artificial EndDT but it is always in the future rather than in the past. 12/31/2199 works. I think the maximum date for the Jet Date data type is different from what SQL Server uses for DateTime so just pick something.

With a far future date, your queries can use <= Date() to find "active" items.