asked on

Date Filed in Query

I created a query to run data by date. the date field type is a txt, not date/time. I added Cdate before the date that I want to convert to date, however, some of the dates are not converting correctly. the data I am running is from Jan to July. the query result is showing date for December. it looks like the July 12 date is being see as December 7h., the April 8 data is showing as August 4th........

SELECT Tbl_Patient_Details.ID, CDate([ED_Arrival_Time]) AS A
FROM Tbl_Patient_Details;

John Tsioumpris

Format (Cdate(YourDateField),"mm/dd/yyyy")
If your system delimiter is ; then

Format (Cdate(YourDateField);"mm/dd/yyyy")

ste5an

CDate() as well as the other data type conversion functions use the systems and users locales.

Thus CDate("7/12/2019") is 12th of July on a system with US locales and 7th of December on a British/French system.

This ambiguity cannot be resolved by CDate().

Maybe you have a source descriptor, which tells you where this string came from.

The only other thing which could help is the order of your rows, you can flag incorrect conversion, when these "flips" happen in comparison to the rows before and after.

Gustav Brock

You will have to switch the month and day.
If your text dates look like this:

07-08-2018

use DateSerial in your query like this:

TrueDate: DateSerial(Mid([TextDateField], 7, 4), Mid([TextDateField], 1, 2), Mid([TextDateField], 4, 2))

Open in new window

Fed Alexander

ASKER

ID      A      TrueDate      Expr1      Expr3      ED_Arrival_Time
92085      01/08/2019 7:14:02 PM      16/09/8017      08012019      08/01/2019      43678.801412
92084      01/08/2019 7:08:08 PM      16/09/7976      08012019      08/01/2019      43678.797315
92083      01/08/2019 7:05:24 PM      16/09/7957      08012019      08/01/2019      43678.795417
92093      01/08/2019 8:08:03 PM      16/09/8392      08012019      08/01/2019      43678.838924
92081      01/08/2019 6:48:53 PM      16/09/7842      08012019      08/01/2019      43678.783947
123405      31/07/2019 12:37:06 PM            07312019      07/31/2019      7/31/2019 12:37:06 PM
123394      31/07/2019 11:55:13 AM            07312019      07/31/2019      7/31/2019 11:55:13 AM
123413      31/07/2019 12:49:21 PM            07312019      07/31/2019      7/31/2019 12:49:21 PM
123412      31/07/2019 12:48:33 PM            07312019      07/31/2019      7/31/2019 12:48:33 PM
123411      31/07/2019 12:44:54 AM            07312019      07/31/2019      7/31/2019 12:44:54 AM
123410      31/07/2019 12:42:35 PM            07312019      07/31/2019      7/31/2019 12:42:35 PM
123409      31/07/2019 12:42:11 AM            07312019      07/31/2019      7/31/2019 12:42:11 AM
123408      31/07/2019 12:41:58 AM            07312019      07/31/2019      7/31/2019 12:41:58 AM
123415      31/07/2019 12:54:35 PM            07312019      07/31/2019      7/31/2019 12:54:35 PM

The issue that I am facing the data set is mix, as you can see the last column is the actual column without using any formula to change date or format it. the first few records are not converting correctly. however, when I bring the data in from excel, it all looks ok. but when I append it as date, I lose half of the data in the date filed,

if I convert the date to text, then it comes in and it cause the above issue.

I uploaded the excel file and the access file

thank you for your help
Database7.accdb
data-j.xlsx

Gustav Brock

Don't know what you have done, but just link the worksheet in Access, and your dates, including time, will be fetched correctly and in full:

The format here is my default: day-month-year.
See the attached where the link is to C:\Test\data-j.xlsx
Database7.accdb

Fed Alexander

ASKER

thank you so much, not all the data showed the same way here. attached is a screen shot :(

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Just out of interest, why would you store this date field as a string? Doing so creates nothing but headaches in the long run.

Fed Alexander

ASKER

I think the issue is that they may have changed the system format. I will split it as you suggested.

The reason if I don’t do this, more than 500 records append blank. For some reason, the table can’t see it as date.

Gustav Brock

It is not some arbitrary reason that kicks you.
It is because the link or import cannot change from row to row between a true date value and a text date.

Dale Fye

Are you getting the data directly from Excel? or is it being pulled from some other data source?

Fed Alexander

ASKER

It is coming from Oracle system....I am not sure if the system is set correctly because every month I encounter something different...

It feels like fishing and you don’t know what you will catch this time 😁