How do I convert a month field [Apr] and a day field [21] to a useable date??

[tgclark]

I have a database with data from a concept alarm monitoring system,
which is initailly downloaded as a text file, parsed via excel and
imported into access database.
The date in the text file is embeeded in each line
" 12 Apr21 06:09:34.7 Cleaners Upstair Pin accepted at LCD Term. #001
13 Apr21 06:09:59.4 R04:X01 On by Main Office Exit Button
14 Apr21 06:10:04.3 R04:X01 Off by Aux Timer"

and have split the month and day in to seperate fields.

I have a text month field - Mon_month which has data such as Apr May
Jun
and a text date field - Mon_day which has data such as 22 23 24 25 1 2
What I wish to have is a useable date in the access database so I can
run a query between such dates.
Advice please.
Thanks

 

[Philip Herlihy]

I have a database with data from a concept alarm monitoring system,
which is initailly downloaded as a text file, parsed via excel and
imported into access database.
The date in the text file is embeeded in each line
" 12 Apr21 06:09:34.7 Cleaners Upstair Pin accepted at LCD Term. #001
13 Apr21 06:09:59.4 R04:X01 On by Main Office Exit Button
14 Apr21 06:10:04.3 R04:X01 Off by Aux Timer"

and have split the month and day in to seperate fields.

I have a text month field - Mon_month which has data such as Apr May
Jun
and a text date field - Mon_day which has data such as 22 23 24 25 1 2
What I wish to have is a useable date in the access database so I can
run a query between such dates.
Advice please.
Thanks

Access stores dates/times as numbers (integral-part/fractional-part) and
is pretty smart at converting "well-formed" text representations into
those numbers, as is Excel.

Do you have any control over how the data is massaged before it's
offered to Access? If so, then can you avoid splitting the date
information? Access will simply gobble it up.

If you are the consumer of a format you can't change, then the platinum
option is to teach yourself to use "Regular Expressions" (Google).
Rather scary the first time, but they are designed to detect and manage
patterns in complex data, and are remarkably powerful and useful.
Alternatively, you'll have to use the various date/time functions in
Access (or Excel) to groom your data into a consistent format.

Fooling around in the immediate window of the VB environment I found
these results:

datevalue("Apr21 06:10:04.3") - invalid
datevalue("Apr 21 06:10:04.3) - invalid
datevalue("Apr 21 06:10:04") - OK

That gets you just the date part (which will be an integer equivalent).
The timevalue function will get you the time (fractional part). I
guess you simply add them, or use:

cDate("Apr 21 06:10:04")

Might be a good idea to include a validity check using the IsDate()
function to allow you to spot problems.

Phil, London

 

[John Spencer]

You can use an expression
CDate(Mon_Day & " " & Mon_Month)
will return a date for the current year as long as there is a valid
value in the fields.

You can test this with IsDate function before you attempt to do the
conversion.

IIF(IsDate(Mon_Day & " " & Mon_Month),CDate(Mon_Day & " " & Mon_Month),Null)

If you want a year other than the current year you are going to need
either a field that contains the year or assign it in some method.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ken]

As the lines in the text file appear to follow a consistent format as
regards the date/time you could in fact return a valid date/time
value, defaulting to the current year, directly from the line in the
text file with no prior parsing:

CDate(Mid([TheString],4,3) & " " & Mid([TheString],7 ,11))

Note that if you do this and want to query by date you'll need to take
account of the fact that the values contain non-zero times of day,
e.g. instead of using:

PARAMETERS [Enter start date:] DATETIME,
[Enter start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDateTime
BETWEEN [Enter start date:] AND [Enter start date:];

you'd use:

PARAMETERS [Enter start date:] DATETIME,
[Enter start date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDateTime >= [Enter start date:]
AND MyDateTime < DATEADD("d",1,[Enter start date:]);

Ken Sheridan
Stafford, England