Solved

A Date conversion issue in Access VBA

Posted on 2004-10-13
13
197 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 65
MS SQL store procedure to calculate and return result 6 74
TT Auto Dashboard 13 104
fso.FolderExists("\\server\HiddenFolder$") 4 96
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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

732 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