Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ConvertDatetoString

Posted on 2001-07-19
16
Medium Priority
?
299 Views
Last Modified: 2008-08-15
Hi there,

This is my problem:

I have a datefield 1-1-00 and want to convert this to 20000101

I have a function in VBA:
I made a errorhandler to avoid runtime errors
The syntax below doesnt work

Function dateConvYYYYMMDD(pIn As String) As Date
On Error GoTo dateconvYYYYMMDDerrhandler
Dim day As Integer
Dim month As Integer
Dim year As Integer

    day = Right(pIn, 2)
  month = Mid(pIn, 5, 2)
   year = Left(pIn, 4)

    dateConvYYYYMMDD = DateSerial(year, month, day)

dateconvYYYYMMDDerrhandler:

    Resume Next
End Function

Function numConv(pIn As String) As Integer
    numConv = pIn
End Function
'*************************
And then in a query:
calcDatumAanlevering: dateConvYYYYMMDD([dateDelivery])

What is going wrong?

fmew
0
Comment
Question by:fmew
[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
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 1

Accepted Solution

by:
dkaygee earned 300 total points
ID: 6298306
You can use the "format" function with the date. I don't have the exact code but its something like:

Cstr(Format(Date,"yyyymmdd"))
0
 
LVL 2

Expert Comment

by:DanielBlais
ID: 6298354
try that :

dim result as string

result = cstr(year(dateDelivery)) + cstr(month(dateDelivery)) + cstr(day(dateDelivery))
0
 

Author Comment

by:fmew
ID: 6298499
Hi Daniel,

I tried this directly in a query, like:

ShowField: CStr(Year([dateDelivery]))+CStr(Month([dateDelivery]))+CStr(Day([dateDelivery]))

And I get only one char for the month and day like:
200174 for 4 july 2001
Is there a solution you can think of?

fmew
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Expert Comment

by:DanielBlais
ID: 6298524
ok

it's a bit longer query to solve

like this :

CStr(Year([dateDelivery]))
+  right("0" + CStr(Month([dateDelivery])), 2)
+  right("0" + CStr(Day([dateDelivery])), 2)
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298569
>And I get only one char for the month and day like:
>200174 for 4 july 2001

To fix this, you can use the Format function:

   ShowField: Format(Year([dateDelivery]),"00")+Format(Month([dateDelivery]),"00")+Format(Day([dateDelivery]),"00")


I do not recommend this approach, however. Instead, you should simply use the Format() function to format the date into your desired format:

   ShowField: Format([dateDelivery],"yyyymmdd")


-Dennis Borg
0
 

Author Comment

by:fmew
ID: 6298621
Every time I try something with a comma (,), I get an error in Access mlike in

ShowField: Format(Year([dateDelivery]),"00")+Format(Month([dateDelivery]),"00")+Format(Day([dateDelivery]),"00")

WHY....?

fmew
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298623
fmew:

Although I still strongly recommend that you use dkaygee's solution ...

    Format([dateDelivery],"yyyymmdd")

(I removed the CStr() because it's not necessary as the Format() function returns a string anyway)

... it still may be helpful to know what was wrong with your original function.

   day = Right(pIn, 2)
 month = Mid(pIn, 5, 2)
  year = Left(pIn, 4)

If you look at these 3 lines of code, you can see that you're assuming that ' pIn ' is *already* in YYYYMMDD format (take note of where you're looking for ' day ', and for ' month ', etc)

Instead, you'd either want to use the split function (available if you have Access 2000) to split apart the individual date components and reassemble them in a different order.

Or, you could use the Month(), Day(), and Year() functions to extract the individual components from the date passed to the function.

Or, you could use Mid() and InStr() functions to disect the string containing the date and get the individual components that way.



The other problem with your function is that it does not return a string in "YYYYMMDD" format. Instead, it is returning a date type, and thus undoes any of the work you've done in the first place.



You have written the following function:

      Function numConv(pIn As String) As Integer
         numConv = pIn
      End Function

Access and VB actually has a built-in function to convert a string into an integer: CInt()

So, you can simply use CInt(pIn) where needed.

There are also other similiar conversion functions built into VB/VBA:

   CInt() - Converts parameter to Integer
   CLng() - Converts parameter to Long
   CDbl() - Converts parameter to Double
   CSng() - Converts parameter to Single
   CStr() - Converts parameter to String
   CDate() - Converts parameter to Date
   CBool() - Converts parameter to Boolean

Those are the ones I can thing of off the top of my head.

Hope this info helps.


-Dennis Borg
0
 
LVL 2

Expert Comment

by:DanielBlais
ID: 6298642
put ; instead of ,
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298647
>ShowField: Format(Year([dateDelivery]),"00")+Format(Month([dateDelivery]),"00")+Format(Day([dateDelivery]),"00")

This should actually be:

   ShowField: CStr(Year([dateDelivery])) & Format(Month([dateDelivery]),"00") & Format(Day([dateDelivery]),"00")


(the Year is going to be 4 digits anyway, and use the concatenate operator '&' instead of '+')

But I don't think this is the cause of your error.

What is the error message that you get? You'll want to make sure that [dateDelivery] is the actual name of your field.


-Dennis Borg
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6298656
fmew:

I still suggest that you simply use dkaygee's solution:

   ShowField: Format([dateDelivery], "yyyymmdd")

This is much simpler, and would be much more efficient.

Have you tried this yet?


-Dennis Borg
0
 

Author Comment

by:fmew
ID: 6298687
The field name is correct.

I have a Dutch version, but the messege is like:

The syntax of the expression is not good
There is missing an oprand or operator
And then the cursor jumps to the comma

I have to go home now
I am for sure here tomorrow


fmew
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6299182
fmew:

You say you are getting an error message, but with what code? Is it with ...

ShowField: Format([dateDelivery], "yyyymmdd")

... which is THE way to do what you want, or with...

ShowField: CStr(Year([dateDelivery])) & Format(Month([dateDelivery]),"00") & Format(Day([dateDelivery]),"00")

... which is not to be recomended. Experts we may be, but few of use are psycic.

Does the Dutch version use something other than a comma to delimit parameters in function/sub calls?
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6299199
>Does the Dutch version use something other than a comma to delimit parameters in function/sub calls?

I was wondering this myself.
0
 

Author Comment

by:fmew
ID: 6299216
i am home now, busy with other things
in fact i have to use the ; instead of the , in the dutch version

Tomorrow I will look again

I still do not know how to accept an answer!
In this answer/commant page there are several positions to accept.
Do I do this in one place where I want to do this?
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6299245
I have not submitted a question before, so I do not know what the screen looks like.

Perhaps someone else has and can explain to you what you need to do in order to select the answer you've chosen.

-Dennis Borg
0
 

Author Comment

by:fmew
ID: 6301398
This Dutch version of MsAcces doesnt accept the comma (,) but I found out that I have to use the (;)
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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