Solved

A Date conversion issue in Access VBA

Posted on 2004-10-13
13
192 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now