How to add Decimals, Colons and Slashes to numbers in columns?

[Hswift]

Hello, I have three columns in a table with numbers that look like this.

datecolumn timecolumn pricecolumn
20151201 190006176 206350
20151201 190832820 206000
20151201 190832824 206025


Any suggestions on how I can convert the numbers to look like this?

datecolumn timecolumn pricecolumn
2015/12/01 19:00:06.176 2063.50
2015/12/01 19:08:32.820 2060.00


2015/12/01 19:08:32.824 2060.25


thanks!

[orange]

Assign appropriate data type is basic answer. Date/Time double with decimal places
How do you know that 2015/12/01 is a date? Default for Access is US presentation MM/DD/YYYY

[June7]

I agree. Why is this string data and not appropriate field types?

Converting will require string manipulation to rearrange the parts for the date and time values.

The price can be converted to a number with: Val([pricecolumn])/100

This assumes the rightmost 2 characters will always be decimal part and that no record has Null in this field.


AFAIK, Access cannot work with decimal seconds in actual time values.

[Hswift]

Hi Orange,

Is this something I do via selecting the "Query Design" tool then selecting the "SQL View"?

Can you please type out a code example for me if so?

Thanks!

[June7]

No, I think orange is suggesting the table fields should have correct data types then populate the fields.

I just tried Format() function with the date value and it does modify the format: Format([datefield], "0000/00/00")

However, this is not a true date and cannot be used in date calculations.

I cannot get Format() to work on the time value unless I drop the decimal seconds: Format(Left([timefield],6),"00:00:00")

[CJ_London]

as a direct answer to your question google using the left, right and mid string functions

however whether this does what you really require is another question - if you actually want to end up with a date field, a time field (or perhaps a datetime field) and a currency field, then this is what you need to ask

[Hswift]

Its fine for my needs if Access only recognizes the data in these three columns as numbers (rather then actual dates or times).

Does this bring up any new ideas on how i can do the conversion?

[June7]

I have provided expression that will convert the price to a number.

I have provided expressions that will modify the structure of the date and time values but the results will still be a string, not actual date and time values. If you need date and time values, try:

CDate(Format([datefield],"0000/00/00"))

CDate(Format(Left([timefield],6),"00:00:00"))

[Hswift]

Thanks June7, where would I type this code in exactly?

I clicked the "Query Design" tab then clicked the "Sql View" and entered the code, but it did not seem to work.
Did I just type it in wrong? Or am I supposed to add this code somewhere else entirely?

[June7]

The expressions can be used in query to construct fields http://www.fontstuff.com/access/acctut02.htm or in textbox ControlSource property.

This is basic Access functionality. Completing an introductory tutorial book or online course would probably be a big help in your understanding of Access.

[Hswift]

Ahh, that http://www.fontstuff.com/access/acctut02.htm link definitely explained how to construct the fields, simpler than i thought. Thanks for everything everyone!