Access Date Format

J

Janelle

Using Access 2002. Is it possible to change the date format to have spaces
between each number?
eg. D D M M Y Y Y Y (0 4 2 7 2 0 0 7)
 
K

KARL DEWEY

Continue the below for all characters of your date.
Mid(Format([YourField],"ddmmyyyy"),1,1) & " " &
Mid(Format([YourField],"ddmmyyyy"),2,1) & " " &
Mid(Format([YourField],"ddmmyyyy"),3,1) & " " &
 
6

'69 Camaro

Hi, Janelle.
eg. D D M M Y Y Y Y (0 4 2 7 2 0 0 7)
Click to expand...

It's not clear from your example whether you want to start the string with
the day (27) or the month (04). This example assumes you want to start with
the day.

Dim sDt As String
Dim sStretchDt As String

sDt = Format(Date, "dd mm yyyy")

sStretchDt = Mid$(sDt, 1, 1) & " " & Mid$(sDt, 2, 2) & Mid$(sDt, 4, 1) &
" " & _
Mid$(sDt, 5, 2) & Mid$(sDt, 7, 1) & " " & Mid$(sDt, 8, 1) & " " & _
Mid$(sDt, 9, 1) & " " & Mid$(sDt, 10, 1)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
T

Todos Menos [MSFT]

yeah I believe in Access Data Projects you would merely do this in ONE
PLACE

I just checked books online though; and I sure don't see anything like
that format.
why are you using such a wierd format?

I'd make a server-side User-Defined Function that would allow you to
reuse this function in any app-- without copying and pasting it



SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime
or smalldatetime data.

Syntax
SET DATEFORMAT { format | @format_var }

Arguments
format | @format_var

Is the order of the dateparts. Can be either Unicode or DBCS converted
to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym.
The U.S. English default is mdy.
 
M

M. D'Arque

There are two ways to handle the implementation:
Quick & Dirty - Use a text box on the report and paste into it a formula
specific to the date field being reported.
Using the HB Claims database for example, make a text box with the contents
of:
= Format(Format([Anniversary Date], "ddmmyyy"), "# # # # # # # #")

Slower and maybe more efficient - Use a function if you have a lot of sapced
date formatting to do in your reports.
The advantage of this way is that if the format requirements change then you
only have to change one (1) function instead of many text boxes on many
report forms.
a) First create the necessary function (Objects/Modules/New):
Function SpacedDate(aDateField)
SpacedDate = Format(Format(aDateField, "ddmmyyy"), "# # # # # # # #")
End Function

- Remember to compile the function! (Debug/Compile)

b) Secondly, where required on the report(s), use a text box that calls the
function with the specific date field, for example if HB Claims database:
= SpacedDate([Anniversary Date])
 
M

M. D''Arque

Oops, please note I dropped a y. Corrected below.

:

There are two ways to handle the implementation:
Quick & Dirty - Use a text box on the report and paste into it a formula
specific to the date field being reported.
Using the HB Claims database for example, make a text box with the contents
of:
= Format(Format([Anniversary Date], "ddmmyyyy"), "# # # # # # # #")

Slower and maybe more efficient - Use a function if you have a lot of spaced
date formatting to do in your reports.
The advantage of this way is that if the format requirements change then you
only have to change one (1) function instead of many text boxes on many
report forms.
a) First create the necessary function (Objects/Modules/New):
Function SpacedDate(aDateField)
SpacedDate = Format(Format(aDateField, "ddmmyyyy"), "# # # # # # # #")
End Function

- Remember to compile the function! (Debug/Compile)

b) Secondly, where required on the report(s), use a text box that calls the
function with the specific date field, for example if HB Claims database:
= SpacedDate([Anniversary Date])
 
M

M. D''Arque

Here's the final version of the function

Function SpacedDate(aDateField)
'Purpose: Inserts spaces between the digits of dates with the numeric form
of ddmmyyyy
' Returns "d d m m y y y y" or "null date"

'Caution: No handling of months with more than 99 days

If Not (IsDate(aDateField)) Then
SpacedDate = "null date"

Else

'Does the inside Format() return a leading 0?
If Left(Format(aDateField, "ddmmyyyy"), 1) = 0 Then
'It does, so the outside Format() will substitute a leading space for
the leading 0
'so supply the expected leading zero for days 1 to 9
SpacedDate = "0" & Format(Format(aDateField, "ddmmyyyy"), "# # # # # # #
#")

Else
'days 10 to 28,29,30, or 31
SpacedDate = Format(Format(aDateField, "ddmmyyyy"), "# # # # # # # #")

End If

End If

End Function
 
Top