Convert text field with additional characters to date field 2

[tj007]

Hi

I have a text field (line-descr) which holds dates in this format *030803*. I have created an update query to convert the text field to date. However, I receive an error unable to update due to data type conversion. Here is the update query.

UPDATE inv_podate SET inv_podate.INV_PODATE = CDate(Left(Line-Descr,2) & "/" & Mid(Line-Descr,3,3) & "/" & Mid(Line-Descr,6));

Is there a way to exclude the asterisk from the text field during the conversion?

 

[lespaul]

Instead of using the LEFT on the first portion, use another mid so that you just get the month information. Check it first by running a select:

SELECT (Mid(Line-Descr,2, 2) & "/" & Mid(Line-Descr,4,2) & "/" & Mid(Line-Descr,6)) As NewDate FROM inv_podate

if that returns what you are looking for then this update query should work:

UPDATE inv_podate SET inv_podate.INV_PODATE = CDate(Mid(Line-Descr,2, 2) & "/" & Mid(Line-Descr,4,2) & "/" & Mid(Line-Descr,6));

Leslie

 

[willir]

Did you try including the date delimiter, "#" ?

I also used the MID statement for month, day and year components.

UPDATE inv_podate SET inv_podate.INV_PODATE = "#" & CDate(MID(Line-Descr,2,2) & "/" & Mid(Line-Descr,4,2) & "/" & Mid(Line-Descr,6,2)) & "#";

The resulting string, when printed is...
#3/8/2003#

There are other solutions.
Richard

 

[willir]

Leslie -- no fair, you type faster and more accurately than me. I guess we psted at the same time.

 

[lespaul]

comes from YEARS of data entry before I moved up the IT food chain!

(at one point I could do 13,000 + key strokes per hour if I was just using the number pad, went down to about 10,000 if alphanumeric!)

les

 

[tj007]

lespaul, your suggestion worked fine for the left side, but the asterisk was still in place on the right side. Thank you

willir, your suggestion was right on the money. I converted the field to date with no problem. Now I have other colums with this format 20030830. I am going to mod the statement

UPDATE inv_podate SET inv_podate.voc_date = "#" & CDate(MID(VOUCHER-DA,4,2) & "/" & Mid(VOUCHER-DA,2,2) & "/" & Mid(VOUCHER-DA,6,2)) & "#";

Is this correct?

 

[tj007]

Sorry, typos

UPDATE inv_podate SET inv_podate.voc_date = "#" & CDate(MID(voc_date,4,2) & "/" & Mid(voc_date,2,2) & "/" & Mid(voc_date,6,2)) & "#";

 

[lespaul]

Yeah, mine was off by not including the '2' in the last MID!

here's a corrected version of your new query:

UPDATE inv_podate SET inv_podate.voc_date = "#" & CDate(MID(voc_date,5,2) & "/" & Mid(voc_date,7,2) & "/" & Mid(voc_date,1,4)) & "#";

I changed the numbers of you MID statements:

20030830
12345678

the month starts at 5 and goes 2 places (so the first mid is 5,2)
the day starts at 7 and goes 2 places (so the second mid is 7,2)
the year starts at 1 and goes 4 places (so the last mid is 1,4)

HTH

Leslie

 

[willir]

Leslie's post is correct. I suspect you want

MM/DD/YY or YYYY

Note how Leslie numbered the characters in your date field to find the specific locations of where to start, and for how many characters to use. Well done Leslie.

Since you do not have leading and trailing characters, you can also use LEFT and RIGHT string functions as well as MID.

Richard

 

[tj007]

Thanks leslie I will try this. I have been summoned to something else.

 

[tj007]

oops, this is what happened when I tried this statement

UPDATE inv_podate SET inv_podate.REF_PO_DATE = "#" & CDate(Mid(REF_PO_DATE,5,2) & "/" & Mid(REF_PO_DATE,7,2) & "/" & Mid(REF_PO_DATE,1,4)) & "#";

REF_PO_DATE
#12/10/0
#04/11/0
#04/11/0
#04/11/0
#04/11/0
#04/11/0

 

[willir]

TJ

Is "inv_podate.REF_PO_DATE" actually defined as a field in the table inv_podate. If it is, then you would have gotten errors during the update.

If you are seeing #12/10/0 in the table, then I would suspect the field is defined as a text field -- huge difference.

Next, your post gave the date string...
Date: 20030830
Position: 12345678

Using Leslie's syntax...
Month:
Mid(REF_PO_DATE,5,2) = 08
Explanation: Move left to right 5 character, use 2 characters

Day:
Mid(REF_PO_DATE,7,2) = 30
Explanation: Move left to right 7 character, use 2 characters

Year:
Mid(REF_PO_DATE,1,4) = 2003
Explanation: Move left to right 1 character, use 4 characters

BUT this was for the voc_date. Leslie's post for voc_date looked perfect to me with the information provided.

Is the REF_PO_DATE raw data in the same format as voc_date? Or is voc_date more like your orignal post using Line-Descr?

Richard

 

[tj007]

willi

The field is a text and raw data in the form i posted e.g. 20031106. Th statement you posted was formatted text 030803. The REF_PO_DATE is formatted text 20030806. ???

I am still working on it.

 

[tj007]

Strange, I went to another computer and the statement converted the text field perfectly. You guys talk me some valuable skills. Thanks

 

[willir]

Glad it worked