Solved

A Date conversion issue in Access VBA

Posted on 2004-10-13
13
196 Views
Last Modified: 2011-08-18
Hi all,

I have a two column table in my Access database which is used to store dates. The dates are stored as mm/dd/yy say like 10/13/04. But i want to get the dates passed to a string variable as 101304 i.e without the slashes. The reason being that this string variable is used to pass on this date to an external script file which will only accept the dates as mmddyy i.e again without any slashes or any other symbol in between. I have taken a look at format but i can't see how i can acheive the desired functionality using it. Alternatively is there any way in which i could store the dates in my table as mmddyy so that i do not have to convert it and also retain the property associated with the date type.

Chow
Goels
0
Comment
Question by:Goels
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 6

Assisted Solution

by:bkthompson2112
bkthompson2112 earned 60 total points
ID: 12299001
Have you tried Format(dDate, "mmddyy")?
0
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 65 total points
ID: 12299074
Hi Goels,
Try this method as well.

Dim dDate As Date
Dim strText As String
dDate = Date
strText = Replace(CStr(dDate), "/", "")

-Shahid :)
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12299090
I have just used the replace function to find the slashes ('/') and replace them with nothing ('') :).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Goels
ID: 12299477
Thanks a lot guys. Both the methods are working fine. I would request the administrator to please split the points equally. Mr. Thompson could you please provide me with a bit more explanation of the way you have used the Format function above.

Chow
Goels
0
 
LVL 6

Expert Comment

by:bkthompson2112
ID: 12299611
Sure, the format function will take your date and format it
into the format "mmddyy", which is a 2 digit month, 2 digit day and
a 2 digit year.

So, the date "10/14/04" becomes the string "101404".

The format function can be used for much more than this, though.
See here:  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctformat.asp

Also,
>I would request the administrator to please split the points equally

you can do this yourself by clicking on the "Split points" link.
0
 

Author Comment

by:Goels
ID: 12299694
Well guys i split the points 60, 65 since i am new to programming and thus the second commented answer was immediately clear to me.
Thanks all.

Chow
Goels
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12300062
thompson's method is by typing...
strDate = Format(dDate, "mmddyy")

Thanks for the points. Btw, you can split the points yourself. The link is on the bottom of the page. And secondly, according to the guidelines, when two commentds posted are correct, you are supposed to award all to the first comment ;). But, ultimately, the decision is in your hand. :)
0
 

Author Comment

by:Goels
ID: 12300118
oops please excuse me if i have made a mistake. I will definitely make sure that i read the marking guideline thoroughly. Although i feel in this case both of you did deserve the marks :)

Goels
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12300159
I am new in here too. This is my first week :)
0
 

Author Comment

by:Goels
ID: 12300399
Welcome to the ee community !
0
 
LVL 6

Expert Comment

by:bkthompson2112
ID: 12300548
>when two commentds posted are correct, you are supposed to award all to the first comment

Actually, according to http://www.experts-exchange.com/help.jsp#hi68
"In case of duplicate or similar comments, you should select the first comment posted."

eeshahidt's and I provided different solutions to the question.  Both solutions produce the same
result, just different ways of getting to that result.  So I believe split points
is appropriate in this case.
0
 

Author Comment

by:Goels
ID: 12300575
Well as they say all's well that ends well :) And i am better informed for the future !

Chow
Goels
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12300744
I must be having a sixth sense :). When I got an e-mail notification, the proverb that you posted was just the thing I had in mind ;).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question