text to date and criteria

  • Thread starter Thread starter samuel
  • Start date Start date
S

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.
 
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.
 
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#)
 
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 like your solution better than mine. Much simpler.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


John Spencer said:
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.
Click to expand...
Click to expand...
 
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?

John Spencer said:
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.
Click to expand...
Click to expand...
 
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?

John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top