A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date to string convert function



 
 
Thread Tools Display Modes
  #1  
Old August 18th, 2005, 05:48 AM
00Eric Clapton
external usenet poster
 
Posts: n/a
Default date to string convert function

What is the function which can convert date field to string in a specific
format? Thanks.


  #2  
Old August 18th, 2005, 05:50 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

Try:
Format()

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"00Eric Clapton" wrote in message
...
What is the function which can convert date field to string in a specific
format? Thanks.



  #3  
Old August 18th, 2005, 05:57 AM
Marshall Barton
external usenet poster
 
Posts: n/a
Default

00Eric Clapton wrote:

What is the function which can convert date field to string in a specific
format?



The function you're looking for is Format()

Check Help for the function and the Format Property for
details.

--
Marsh
MVP [MS Access]
  #4  
Old August 18th, 2005, 06:45 AM
00Eric Clapton
external usenet poster
 
Posts: n/a
Default

Actually, a field value 7/7/05 and how do I change it to 07072005 as a
string? Thanks.


"Marshall Barton" wrote in message
...
00Eric Clapton wrote:

What is the function which can convert date field to string in a specific
format?



The function you're looking for is Format()

Check Help for the function and the Format Property for
details.

--
Marsh
MVP [MS Access]



  #5  
Old August 18th, 2005, 06:56 AM
00Eric Clapton
external usenet poster
 
Posts: n/a
Default

And how I change a field with value 1 to " 1" with leading 5 empty space
and string datatype?


"00Eric Clapton" wrote in message
...
What is the function which can convert date field to string in a specific
format? Thanks.



  #6  
Old August 18th, 2005, 07:19 AM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default

"00Eric Clapton" wrote in message
...

Actually, a field value 7/7/05 and how do I change it to 07072005 as a
string? Thanks.


As others mentioned, you can use the format function.

Thus,

format(dateValue,"mmddyyyy")

So in a report, you could place a text box on the report, and bind it to the
following expression:

=(format([mydatefield],"mmddyyyy"))

In code, you could use:

dim dtMyDate as date


.....code here sets the date value from the table, or whatever....

msgbox "the date value is " & format(dtMyDate,"mmddyyyy")

It is not clear when, or where you want this value displayed. And, as
mentioned, it is assumed the data is in a date type field...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal



  #7  
Old August 18th, 2005, 11:50 AM
Graham Mandeno
external usenet poster
 
Posts: n/a
Default

Five spaces followed by a numeric value? Try:
" " & MyValue

This may not give you what you want if the value is, say, greater than 9,
but then you do not have a good history for expressing your questions
clearly, nor, indeed, for showing gratitude for the learned advice you do
receive.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"00Eric Clapton" wrote in message
...
And how I change a field with value 1 to " 1" with leading 5 empty
space and string datatype?


"00Eric Clapton" wrote in message
...
What is the function which can convert date field to string in a specific
format? Thanks.





  #8  
Old August 19th, 2005, 12:15 AM
John Griffiths
external usenet poster
 
Posts: n/a
Default

How about -

Space(5-Len(Format(MyNumber, "0")))&Format(MyNumber, "0")

Any help? John

"Graham Mandeno" wrote in message
...
Five spaces followed by a numeric value? Try:
" " & MyValue

This may not give you what you want if the value is, say, greater than 9,
but then you do not have a good history for expressing your questions
clearly, nor, indeed, for showing gratitude for the learned advice you do
receive.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"00Eric Clapton" wrote in message
...
And how I change a field with value 1 to " 1" with leading 5 empty
space and string datatype?


"00Eric Clapton" wrote in message
...
What is the function which can convert date field to string in a

specific
format? Thanks.







  #9  
Old August 19th, 2005, 03:09 AM
00Eric Clapton
external usenet poster
 
Posts: n/a
Default

I got it guys. I just wrote a script to do the conversion. Thanks


"John Griffiths" wrote in message
...
How about -

Space(5-Len(Format(MyNumber, "0")))&Format(MyNumber, "0")

Any help? John

"Graham Mandeno" wrote in message
...
Five spaces followed by a numeric value? Try:
" " & MyValue

This may not give you what you want if the value is, say, greater than 9,
but then you do not have a good history for expressing your questions
clearly, nor, indeed, for showing gratitude for the learned advice you do
receive.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"00Eric Clapton" wrote in message
...
And how I change a field with value 1 to " 1" with leading 5 empty
space and string datatype?


"00Eric Clapton" wrote in message
...
What is the function which can convert date field to string in a

specific
format? Thanks.









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to convert TEXT to a STRING Andre Laplume via AccessMonster.com Running & Setting Up Queries 2 March 31st, 2005 02:57 PM
using nz function to rid #error msg in query Alex Running & Setting Up Queries 5 March 17th, 2005 05:49 PM
Converting to Word STOOTS General Discussion 9 March 8th, 2005 01:08 AM
How to convert string to a date galsaba Worksheet Functions 3 March 4th, 2005 06:20 PM
Calendar Object Steve Setting Up & Running Reports 1 May 18th, 2004 04:44 PM


All times are GMT +1. The time now is 12:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.