Solved

A Date conversion issue in Access VBA

Posted on 2004-10-13
13
189 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

758 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

19 Experts available now in Live!

Get 1:1 Help Now