How to display date from Excel to Access (1 Viewer)

vent · Oct 11, 2017

Hi everyone

So awhile back I was splitting up date fields in Excel. In the beginning there was two date fields. The first was called"AAStartDate" and had either blanks or a date entered. The second"AAEndDate" had either a date or the word ongoing entered. I added a third table called "AAExpiryDate" because I was told that ongoing = Start Date + 10 years and thus the actual expiry date could be stored in this column. I also put 1/1/1111 in cells that were blank as seen below.

When imported this excel file into Access, I changed AAExpiryDate data type to Date/Time with the format Short Date (e.g. 11/12/2013) and put the input mask as 99/99/0000;0; but when I go back to datasheet view, I notice in AAExpiryDate cells with 1/1/1111 have appear blank but once you click in a cell this appears:
_/_/__
And also in both AAStartDate and AAEndDate cells that had 1/1/1111 in Excel appear as the value "36526". Why is this?

Ranman256 · Oct 11, 2017

Dates are actually a decimal #.
The integer is the date (starting at 1900)
the decimal is the time.
just format the cell as date to get the normalized date look.

IS the access field set as DATEtime?

as for Access, you cannot import text in a date field,
and year 1111 may be illegal, but it should work, its a valid date.
1/1/1900 is a better default date.

in access add dates using DATEADD("YYYY",10,dateField)
to add 10 years.

Pat Hartman · Oct 12, 2017

Using defaults for dates which can validly be empty generally causes more problems than it solves. I would not use 1/1/1111 as a default although it is a valid date to Access.

Looking at your first png should give you a clue. some dates are 1/1/1111 others are 2010-02-04. One of those is being treated as text. Check your formats on the Excel sheet.

vent · Oct 12, 2017

Pat Hartman said:
Using defaults for dates which can validly be empty generally causes more problems than it solves. I would not use 1/1/1111 as a default although it is a valid date to Access.

Looking at your first png should give you a clue. some dates are 1/1/1111 others are 2010-02-04. One of those is being treated as text. Check your formats on the Excel sheet.

So do you think running reports would be easier with blank date cells? Because basically the criteria for these reports is that 1) the user types in a date range (e.g. 1/2/2012 to 2/3/2016) and all the filtered records within this range appear but also having 1/1/1111 as the default date value and the user types in an end date (e.g. 5/2/2015) because this way, all the records before the end date entered including all the records with 1/1/1111 appear.

Minty · Oct 12, 2017

I would simply use Is Null to search for blank dates. Much easier and more robust than putting in a nonsense default date.

Imagine it was an invoice date and you wanted to find un-invoiced items. Simple if the date is null. someone may have mistyped a year in a date and it gets missed as a result.

vent · Oct 12, 2017

Minty said:
I would simply use Is Null to search for blank dates. Much easier and more robust than putting in a nonsense default date.

Imagine it was an invoice date and you wanted to find un-invoiced items. Simple if the date is null. someone may have mistyped a year in a date and it gets missed as a result.

But about for entering new records? Basically, we don't want anymore free text entered dates. Also if there's no date, then at least that cell isn't left empty which i always assumed to be a bad thing with databases in general.

Minty · Oct 12, 2017

vent said:
But about for entering new records? Basically, we don't want anymore free text entered dates.

Is this in the Excel worksheet or Access?
In Access - simply format the textbox as a short date, and validate on before update with IsDate().
In Excel - I think you would need to use DataValidation on the cells.

vent · Oct 12, 2017

Minty said:
Is this in the Excel worksheet or Access?
In Access - simply format the textbox as a short date, and validate on before update with IsDate().
In Excel - I think you would need to use DataValidation on the cells.

Eventually everything will be on Access but for now I'm using Excel to edit the data, (e.g. separate date values and trailing text into their own respective field)

Minty · Oct 12, 2017

vent said:
Also if there's no date, then at least that cell isn't left empty which i always assumed to be a bad thing with databases in general.

For dates null is actually very useful, because a date field has to either be a valid date or null. You can't have an "" empty string in a date. Hence why checking for null is a powerful flag.

You don't need to have an "IsInvoiced" flag if you have an InvoiceDate, it's presence or lack of is enough.

Pat Hartman · Oct 17, 2017

If a piece of data can validly have an "unknown" value, then you shouldn't be using bogus default values. There is nothing wrong with allowing nulls for unknown values. That allows you to set a column as required when a value is actually required. In that case, you might decide to allow a default but it will be a valid value that you default to rather than a bogus value.

Once you allow nulls in data, you have to consider them in your criteria. While Between date1 and date2 will validly exclude nulls, you have to make a conscious decision to have a < date1 criteria also select null values. That isn't obvious or universal. It will be application specific.

An example that you need to consider is how aggregation functions work. They ignore null values so:

Avg(3 + 0 + 3) = 2
whereas
Avg(3 + null + 3) = 3

I don't know if Excel works the same way but you should check. BTW - to clear a cell in Excel, you actually have to use the delete action to remove the contents.

How to display date from Excel to Access (1 Viewer)

vent

Registered User.

Attachments

Ranman256

Well-known member

Pat Hartman

Super Moderator

vent

Registered User.

Minty

AWF VIP

vent

Registered User.

Minty

AWF VIP

vent

Registered User.

Minty

AWF VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread