?
Solved

A Date conversion issue in Access VBA

Posted on 2004-10-13
13
Medium Priority
?
212 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 180 total points
ID: 12299001
Have you tried Format(dDate, "mmddyy")?
0
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 195 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
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…
Suggested Courses
Course of the Month12 days, 21 hours left to enroll

578 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