 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / September 20076 Character String to Date field
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?  Signaturehttp://njgin.aclink.org 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) 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 [quoted text clipped - 5 lines] > Is attempting to make this a date field the way to go? Is there a better > way? 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 Signaturehttp://njgin.aclink.org > To Convert it Use > [quoted text clipped - 17 lines] > > Is attempting to make this a date field the way to go? Is there a better > > way? 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)) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Thank you both for the replies. > [quoted text clipped - 32 lines] >> > better >> > way? 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!! Signaturehttp://njgin.aclink.org > Thank you both for the replies. > [quoted text clipped - 29 lines] > > > Is attempting to make this a date field the way to go? Is there a better > > > way? Not typo, just misreading <g> Pieter > Thank you! Thank you! > [quoted text clipped - 45 lines] >> > > better >> > > way? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.