Access VBA

Resolved · Low Priority · Version Standard

Sun 9 Mar 2008, 08:44 Delegate Mohammed said...

Mohammed has attended:
Access Intermediate course
Access Advanced course
Access VBA course

Access VBA

how can you concatenate a date field from yyyymmdd to read dd/mm/yyyy in access?

Tue 18 Mar 2008, 12:36 Trainer Rich said...

RE: Access VBA

Hi Mohammed,

A few things you can try...

1) You could try the Format function:

Format ( expression, [ format, [ firstdayofweek, [firstweekofyear] ] ] )

eg. Format ( yyyymmdd, "dd/mm/yyyy")

See more: http://www.techonthenet.com/access/functions/date/format.php

2) If it's an import, you can adjust it in Excel first, like this:

If A1 equals the yyyymmdd value, enter this in B1:
=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

... to get the proper format.

Hope this helps.

Regards, Rich

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds