Change date format

[Agom008]

Hi Everyone,

In my query I have a date field and it is half UK british and half US. I have my regional date set to UK on my laptop.

Any idea how to change it to same date? I don't mind either, I just want it to be consistent.

Thanks

 

[vbaInet]

How does the date appear in your table?
Where does the data originate?

 

[Galaxiom]

In my query I have a date field and it is half UK british and half US. I have my regional date set to UK on my laptop.

Then it is not a Date field but dates stored as text.

Before it can be converted to a true date field you will need to establish an independent way to determine which records are in US and which are in UK.

You cannot presume that valid UK dates are UK because many US dates are also valid in UK. eg 1/12/2014 could be January 12 in UK or December 1 in US.

 

[Uncle Gizmo]

Well spotted Galaxiom

 

[Agom008]

Thanks for the replies, how can I quickly differentiate between the two?

The data type in its source data was in date/time then I changed it to text.

 

[Galaxiom]

Why did you change it to text if it was Date?

When it was date it may have gotten wrong values due to Windows "helpful" behaviour. If a date is invalid in the local date format Windows will (AFAIK unstoppably) automatically try it in US or ISO and accept it if it fits.

In such cases your dates before the 12th of the month may be ambiguous.

Hopefully you have some way to determine which they should be because it is not possible to determine it from the date field alone.

 

[Agom008]

okay so should I change it back to date/time in source data and manually change the dates? I can check if it's right by checking the excel dates...which I imported the data from

 

[Galaxiom]

Change it back. You should always make sure you have a backup before doing such things too.

Are you saying the change happened during the import from Excel? What data format is the Excel column?

 

[Agom008]

in excel it's in date format

 

[Galaxiom]

Are they definitely correct dates in Excel?

Sometimes when importing, Access will make decisions based on faulty logic. This is usually a problem with text imports where it looks at the first few rows and assumes based on that. I would have thought it would follow the Format with Excel but I guess it can go wrong with Excel too.

It is often better to import everything as text into a staging table then convert entirely inside Access.

 

[Agom008]

okay, thank you... I will re-do it and hopefully I won't have much trouble.

Thanks so much for everyones help

 

[ConnorGiles]

I would try adding another field which differentiates each field between US and UK so when searching in a query make sure you know what country you are searching so the appropriate dates will show.

But don't trust me by heart - i'm a novice

 

[Agom008]

hey guys, so I changed the date column in excel to text, imported it into MS Access 2010 and tried changing it back to date/time datatype which I couldn't as I run out of memory space/ disk space even when I compact it. So I copy it into another table with just the field headings and change the data type to date type to date/time and append the data into new copy and everything copies except the date field which is just blank?

I just don't get it? I've done it both ways where it's in a date format in excel and import it and it still doesn't work properly because I can't sort the date properly and now I can't even even change text into date?

I don't know what is up with MS Access... :/

 

[RainLover]

What am I missing here.

How can we say that in a particular column some of the Fields are formatted US while the others are formatted UK.

Is this the case or is it before the data gets imported into Access that we have this difference.

 

[apr pillai]

Date/Time values are stored as Real Numbers internally. Formatting is done to display them in required format. Formatting will not change the data.

Change of date format takes place during data entry, depending on the 'Region & Language' setting of the Computer.

Assume that the data-entry field format is dd/mm/yyyy and you enter the date as 01/12/2013 for 1st December 2013 (date number: 41609). If the Region & Language setting is US format the date will be recorded as 12/01/2013 (12th January 2013, date number: 41286). This change will happen only on dates from 1 to 12 of any month.

Hope you have noticed the date number difference. After recording them wrongly doesn't matter which way you format it, it will be wrong, till you update them correctly.

I think it is important to change the 'Region & Language' setting of the computer first to a suitable Region and edit the dates in Excel itself, where ever necessary to make all of them to a common format.

You can change the date column into numeric format in Excel and upload into Access and change the Table column into Date/time format. The date will always remain as a Real Number internally in Excel/Access (Date/time - 41609.5 is 1st Dec 2013 12:00 Noon)

 

[Agom008]

Thank you, the number format before Date/Type format worked

 

[Agom008]

Hi, I have another question... I am trying to join a table and a query together, they are linked with the common key invoice_number. When I try and create a graph with it, it crashes. Why is that?

 

[Uncle Gizmo]

You may get a better response if you post your new question in a separate thread in the correct forum.

 

[RainLover]

Hi, I have another question... I am trying to join a table and a query together, they are linked with the common key invoice_number. When I try and create a graph with it, it crashes. Why is that?

The answer to this may be a bit involved.

Have you started a new Thread as suggested, and if you did could you place a link from this thread to the new one. It may help with some background info.