Sting function in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is so frustrating!
I need to run an update query that changes the date field value to a more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help
 
TLuebke said:
This is so frustrating!
I need to run an update query that changes the date field value to a more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help
Click to expand...

If it's a date field, just use the Format function.

Format([DateField], "m/d")

HTH,
Randy
 
Oops, I didn't notice that you had leading zeroes. Use:

Format([DateField], "mm/dd")


Randy Harris said:
TLuebke said:
This is so frustrating!
I need to run an update query that changes the date field value to a more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help
Click to expand...

If it's a date field, just use the Format function.

Format([DateField], "m/d")

HTH,
Randy
Click to expand...
 
Thanks but it didn't work. Gave me a type conversion error.
any other ideas?



Randy Harris said:
Oops, I didn't notice that you had leading zeroes. Use:

Format([DateField], "mm/dd")


Randy Harris said:
TLuebke said:
This is so frustrating!
I need to run an update query that changes the date field value to a more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help
Click to expand...

If it's a date field, just use the Format function.

Format([DateField], "m/d")

HTH,
Randy
Click to expand...
Click to expand...
 
If that is a string then try the following:
Format(Right(TheField,4)"@@/@@")

If you want a date then
DateValue(Format(TheField,"@@@@/@@/@@"))
That will fail with an error if TheField is null.
 
Didn't quite work - Using the "@@/@@" gave me an error that I was using an
operand without an operator.

Dropping the @@/@@ got rid of the 2005 but if the month has a leading zero
it drops the zero. That's okay if I could count from the right and insert a
"/"

Thankss
 
Did you have quote marks around the format string?

Try breaking this down into steps. Assume that the field has "20050830" as
a STRING value.

Right(TheField,4) That should return 0830. Does it? If it returns 830,
then TheField may have a trailing space.

Try
Right(Trim(TheField),4) that should return 0830. Does it? If so, then
you have trailing spaces in the field.

Format("0830","@@/@@") should give you "08/30"

Putting both together should give you something like the following.
Format(Right(TheField,4)"@@/@@")
 
TLuebke said:
Thanks but it didn't work. Gave me a type conversion error.
any other ideas?
Click to expand...

Then it must not be a date field. Assuming it is text, use:

Mid([YourField], 5, 2) & "/" & Mid([YourField], 7)



Randy Harris said:
Oops, I didn't notice that you had leading zeroes. Use:

Format([DateField], "mm/dd")


Randy Harris said:
This is so frustrating!
I need to run an update query that changes the date field value to
Click to expand...
a
more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help

If it's a date field, just use the Format function.

Format([DateField], "m/d")

HTH,
Randy
Click to expand...
Click to expand...
Click to expand...
 
Thank Van that did the trick. Now I can get on with my life :)
Thanks also to Randy,jahoobob,and John for lending a hand.

Van T. Dinh said:
Try:

Mid([YourDateString], 5, 2) & "/" & Right([YourDateString], 2)

--
HTH
Van T. Dinh
MVP (Access)



TLuebke said:
This is so frustrating!
I need to run an update query that changes the date field value to a more
readable format.

I need this 20050830 to read 08/30

I'm driving myself crazy with Lefts and rights and Mids and getting junk
like 0820054 or 0830/005 you get the picture.

Please help
Click to expand...
Click to expand...
 
Back
Top