fmew
asked on
ConvertDatetoString
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([dateDeli very])
What is going wrong?
fmew
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([dateDeli
What is going wrong?
fmew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Daniel,
I tried this directly in a query, like:
ShowField: CStr(Year([dateDelivery])) +CStr(Mont h([dateDel ivery]))+C Str(Day([d ateDeliver y]))
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
I tried this directly in a query, like:
ShowField: CStr(Year([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
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)
it's a bit longer query to solve
like this :
CStr(Year([dateDelivery]))
+ right("0" + CStr(Month([dateDelivery])
+ right("0" + CStr(Day([dateDelivery])),
>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")+Fo rmat(Month ([dateDeli very]),"00 ")+Format( Day([dateD elivery]), "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],"yyy ymmdd")
-Dennis Borg
>200174 for 4 july 2001
To fix this, you can use the Format function:
ShowField: Format(Year([dateDelivery]
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],"yyy
-Dennis Borg
ASKER
Every time I try something with a comma (,), I get an error in Access mlike in
ShowField: Format(Year([dateDelivery] ),"00")+Fo rmat(Month ([dateDeli very]),"00 ")+Format( Day([dateD elivery]), "00")
WHY....?
fmew
ShowField: Format(Year([dateDelivery]
WHY....?
fmew
fmew:
Although I still strongly recommend that you use dkaygee's solution ...
Format([dateDelivery],"yyy ymmdd")
(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
Although I still strongly recommend that you use dkaygee's solution ...
Format([dateDelivery],"yyy
(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
put ; instead of ,
>ShowField: Format(Year([dateDelivery] ),"00")+Fo rmat(Month ([dateDeli very]),"00 ")+Format( Day([dateD elivery]), "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
This should actually be:
ShowField: CStr(Year([dateDelivery]))
(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
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
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
ASKER
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
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
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?
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]))
... 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?
>Does the Dutch version use something other than a comma to delimit parameters in function/sub calls?
I was wondering this myself.
I was wondering this myself.
ASKER
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?
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?
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
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
ASKER
This Dutch version of MsAcces doesnt accept the comma (,) but I found out that I have to use the (;)
dim result as string
result = cstr(year(dateDelivery)) + cstr(month(dateDelivery)) + cstr(day(dateDelivery))