text to date and criteria

[samuel]

I have a column [date] that is stored in the following format:

20090603080000

I need two things:

Filter all dates for just the past month, and also change this format to an
actual date format.

I'm lost.

 

[Allen Browne]

Criteria for last month's dates:
Like """" & Format(DateAdd("m", -1, Date()), "yyyymm") & "*"""

To convert it to a real date:
DateSerial(Left([d], 4), Mid([d],5,2), Mid([d],7,2))
substituting your field name for [d].

Not sure how the remainder of the field is to be interpreted. Use
DateAdd("s", ...) if you need to add seconds.

 

[Jerry Whittle]

Here's a way to convert it to a real date in a query:

TheDate: CDate(Left([date],4) &â€/â€& Mid([date], 5,2) &â€/â€& Mid([date], 7,2)
&†â€& Mid([date], 9,2) &â€:â€& Mid([date], 11,2) &â€:â€& Mid([date], 13,2))

However the CDate function will bomb out if any record has something that
can't be considered a valid date. Therefore I link to use the IsDate function
to find these bad dates.
I put it into an IIf statement and if IsDate finds something wrong, it puts
in a bogus date - in this case 01/01/1776. You can change the bogus date to
something else and then fix any problem records.

TheDate: IIf(IsDate(Left([date],4) &â€/â€& Mid([date], 5,2) &â€/â€& Mid([date],
7,2) &†â€& Mid([date], 9,2) &â€:â€& Mid([date], 11,2) &â€:â€& Mid([date], 13,2))=
True, CDate(Left([date],4) &â€/â€& Mid([date], 5,2) &â€/â€& Mid([date], 7,2) &â€
â€& Mid([date], 9,2) &â€:â€& Mid([date], 11,2) &â€:â€& Mid([date],
13,2)),#01/01/1776#)

 

[John Spencer]

If you always have a VALID value, you can use the following to convert
the value.
CDate(Format("20090603080000","@@@@-@@-@@ @@@@"))

If you might have invalid strings then you can use
IIF(IsDate(Format("20090603080000","@@@@-@@-@@ @@@@")),
CDate(Format("20090603080000","@@@@-@@-@@ @@@@")),Null)

Replace Null with #2999-12-31 23:59:59# (or whatever your choice is) if
you want a date generated for the invalid strings instead of a null value.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jerry Whittle]

I like your solution better than mine. Much simpler.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

If you always have a VALID value, you can use the following to convert
the value.
CDate(Format("20090603080000","@@@@-@@-@@ @@@@"))

If you might have invalid strings then you can use
IIF(IsDate(Format("20090603080000","@@@@-@@-@@ @@@@")),
CDate(Format("20090603080000","@@@@-@@-@@ @@@@")),Null)

Replace Null with #2999-12-31 23:59:59# (or whatever your choice is) if
you want a date generated for the invalid strings instead of a null value.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a column [date] that is stored in the following format:

20090603080000

I need two things:

Filter all dates for just the past month, and also change this format to an
actual date format.

I'm lost.

 

[samuel]

that code does look simple, but it's only converting the actual string. I
need it to convert the actual data field [date].

Also, what do I need to put in the criteria to only show the values that
occured one month ago?

If you always have a VALID value, you can use the following to convert
the value.
CDate(Format("20090603080000","@@@@-@@-@@ @@@@"))

If you might have invalid strings then you can use
IIF(IsDate(Format("20090603080000","@@@@-@@-@@ @@@@")),
CDate(Format("20090603080000","@@@@-@@-@@ @@@@")),Null)

Replace Null with #2999-12-31 23:59:59# (or whatever your choice is) if
you want a date generated for the invalid strings instead of a null value.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a column [date] that is stored in the following format:

20090603080000

I need two things:

Filter all dates for just the past month, and also change this format to an
actual date format.

I'm lost.

 

[KenSheridan via AccessMonster.com]

If you want to store the converted values in the table then you'll need to
add an extra column of date/time data type and then run an update query to
update the new column, using John's expression, to:

CDate(Format([YourTextColumn],"@@@@-@@-@@ @@@@"))

Once you've done so, and you are happy that the resulting values are correct,
you can delete the text column and in a query enter the following criteria in
design view for the new date/time column:

= DateAdd("m",-1,Date()) And < DateAdd("d",1,Date())

That will return all rows with dates from one month ago up to and including
the current date.

Ken Sheridan
Stafford, England

that code does look simple, but it's only converting the actual string. I
need it to convert the actual data field [date].

Also, what do I need to put in the criteria to only show the values that
occured one month ago?

If you always have a VALID value, you can use the following to convert
the value.

[quoted text clipped - 23 lines]

 

[John Spencer]

If you are saying you want a field to have the date stored as a date,
then you will need to ADD a new field to store the actual date and then
run an update query to transform the string to a date.

Occurred one month ago? Exactly one month ago - so you want all records
for the date that is May 4, 2009? Or do you want all records for the
last month? Or all records that are over a month old?

Exactly one month ago

WHERE YourDateField Like Format(DateAdd("m",-1,Date()),"yyyymmdd") & "*"

Between one month ago and today
WHERE YourDateField >= Format(DateAdd("m",-1,Date()),"yyyymmdd") &
"000000" and YourDateField <= Format(Date(),"yyyymmdd") & "999999"

An update query might look like
UPDATE YourTable
SET RealDateField =
CDate(Format([TheStringField],"@@@@-@@-@@ @@@@"))
<<Add a where clause here if desired>>


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

that code does look simple, but it's only converting the actual string. I
need it to convert the actual data field [date].

Also, what do I need to put in the criteria to only show the values that
occured one month ago?

If you always have a VALID value, you can use the following to convert
the value.
CDate(Format("20090603080000","@@@@-@@-@@ @@@@"))

If you might have invalid strings then you can use
IIF(IsDate(Format("20090603080000","@@@@-@@-@@ @@@@")),
CDate(Format("20090603080000","@@@@-@@-@@ @@@@")),Null)

Replace Null with #2999-12-31 23:59:59# (or whatever your choice is) if
you want a date generated for the invalid strings instead of a null value.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a column [date] that is stored in the following format:

20090603080000

I need two things:

Filter all dates for just the past month, and also change this format to an
actual date format.

I'm lost.