6 Character String to Date field

[Guest]

I've looked through the posts but I can't find a suitable answer. I've got a
field that is a 6 character string (September 12, 2007 would look like
091207) and I would like it to be some sort of field and type that is
searchable in a query (ie find records that are between this date and that
date)...any suggestions?

Is attempting to make this a date field the way to go? Is there a better way?

 

[Franck]

to do so you must be sure that your string is always same format and
no typing error at all. but obviously a date field is the way to go.
by changing the data type to date if the date is enter in an
understandable format it should convert with no problem, worst case
you will have to change few one before. but once you transform in date
format you will see all opportunities

you could type in a search field : 07-09-12 or 12/09/07 or 12/09/2007
and he will all see them as september 12 2007, as long as windows can
understand those format, witch is the case for 2000, xp and vista
(those are the one im sure of but 98 should be able to read them)

 

[Pieter Wijnen]

To Convert it Use

DateSerial(Right([MyField],2), Mid([MyField],3,2), Left([MyField],2))

HTH

Pieter

a sidenote to Franck - Please retain the original text when answering
questions, that way we don't need to go back to the original question to add
comments/answers

 

[Guest]

Thank you both for the replies.

Pieter I like it, thank you for the lead.

I don't know if its doing what I need it to though. It seems as if there
are some that work and some that don't 050598 came up as May 5, 1998. I
believe that is correct. But when I look at 082694, this comes up as
February 8, 1996 (not August 26, 1994).

I am running Windows XP, Access 07, if you can think of anything else...
But again, thank you

--
http://njgin.aclink.org

To Convert it Use

DateSerial(Right([MyField],2), Mid([MyField],3,2), Left([MyField],2))

HTH

Pieter

a sidenote to Franck - Please retain the original text when answering
questions, that way we don't need to go back to the original question to add
comments/answers

I've looked through the posts but I can't find a suitable answer. I've
got a
field that is a 6 character string (September 12, 2007 would look like
091207) and I would like it to be some sort of field and type that is
searchable in a query (ie find records that are between this date and that
date)...any suggestions?

Is attempting to make this a date field the way to go? Is there a better
way?

 

[Douglas J. Steele]

Pieter had a slight typo. It should be

DateSerial(Right([MyField],2), Left([MyField],2), Mid([MyField],3,2))

Note that MyField must be a text field for that to work. If it's numeric,
you need to ensure that it's converted into a 6 character string:

DateSerial(Right(Format([MyField], "000000"),2), Left(Format([MyField],
"000000"),2), Mid(Format([MyField], "000000"),3,2))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you both for the replies.

Pieter I like it, thank you for the lead.

I don't know if its doing what I need it to though. It seems as if there
are some that work and some that don't 050598 came up as May 5, 1998. I
believe that is correct. But when I look at 082694, this comes up as
February 8, 1996 (not August 26, 1994).

I am running Windows XP, Access 07, if you can think of anything else...
But again, thank you

--
http://njgin.aclink.org

To Convert it Use

DateSerial(Right([MyField],2), Mid([MyField],3,2), Left([MyField],2))

HTH

Pieter

a sidenote to Franck - Please retain the original text when answering
questions, that way we don't need to go back to the original question to
add
comments/answers

I've looked through the posts but I can't find a suitable answer. I've
got a
field that is a 6 character string (September 12, 2007 would look like
091207) and I would like it to be some sort of field and type that is
searchable in a query (ie find records that are between this date and
that
date)...any suggestions?

Is attempting to make this a date field the way to go? Is there a
better
way?

 

[Guest]

Thank you! Thank you!

Minor modification and I got it

Thank you Pieter!!!!

This is what ended up working:
DateSerial(Right([DeedDate],2),Left([DeedDate],2),Mid([DeedDate],3,2))

Little different but I couldn't have gotten there without you, Thank you!!
--
http://njgin.aclink.org

Thank you both for the replies.

Pieter I like it, thank you for the lead.

I don't know if its doing what I need it to though. It seems as if there
are some that work and some that don't 050598 came up as May 5, 1998. I
believe that is correct. But when I look at 082694, this comes up as
February 8, 1996 (not August 26, 1994).

I am running Windows XP, Access 07, if you can think of anything else...
But again, thank you

--
http://njgin.aclink.org

To Convert it Use

DateSerial(Right([MyField],2), Mid([MyField],3,2), Left([MyField],2))

HTH

Pieter

a sidenote to Franck - Please retain the original text when answering
questions, that way we don't need to go back to the original question to add
comments/answers

I've looked through the posts but I can't find a suitable answer. I've
got a
field that is a 6 character string (September 12, 2007 would look like
091207) and I would like it to be some sort of field and type that is
searchable in a query (ie find records that are between this date and that
date)...any suggestions?

Is attempting to make this a date field the way to go? Is there a better
way?

 

[Pieter Wijnen]

Not typo, just misreading <g>

Pieter

Thank you! Thank you!

Minor modification and I got it

Thank you Pieter!!!!

This is what ended up working:
DateSerial(Right([DeedDate],2),Left([DeedDate],2),Mid([DeedDate],3,2))

Little different but I couldn't have gotten there without you, Thank you!!
--
http://njgin.aclink.org

Thank you both for the replies.

Pieter I like it, thank you for the lead.

I don't know if its doing what I need it to though. It seems as if there
are some that work and some that don't 050598 came up as May 5, 1998. I
believe that is correct. But when I look at 082694, this comes up as
February 8, 1996 (not August 26, 1994).

I am running Windows XP, Access 07, if you can think of anything else...
But again, thank you

--
http://njgin.aclink.org

To Convert it Use

DateSerial(Right([MyField],2), Mid([MyField],3,2), Left([MyField],2))

HTH

Pieter

a sidenote to Franck - Please retain the original text when answering
questions, that way we don't need to go back to the original question
to add
comments/answers


I've looked through the posts but I can't find a suitable answer.
I've
got a
field that is a 6 character string (September 12, 2007 would look
like
091207) and I would like it to be some sort of field and type that is
searchable in a query (ie find records that are between this date and
that
date)...any suggestions?

Is attempting to make this a date field the way to go? Is there a
better
way?