Display of a text field yy/mm/dd as mm/dd/yy in a report header

  • Thread starter Thread starter Marv Trott
  • Start date Start date
M

Marv Trott

The date field is in a linked table that I cannot change. The dates are
stored as text yy/mm/dd. I want the user to be able to enter mm/dd/yy and
the report header to display the selected date range as mm/dd/yy To
mm/dd/yy.

The query is: Qry Counselor Activity by Date Range.

The CounselDate criteria is: Between [Enter Start Date yy/mm/dd] And [Enter
End Date yy/mm/dd]

The control source in the reports is:

="Qry Counselor Activity by Date Range" & " " & [Enter Start Date
yy/mm/dd] & " To " & [Enter End Date yy/mm/dd].

Only the "yy/mm/dd To yy/mm/dd" is displayed in the report header.

What code do I need to make this happen?

Thanks for your help.

Marv
 
Are you saying that the date is actually stored as a text field with only a
two digit year? Or is the date really a date/time datatype that is simply
formatted to look like yy/mm/dd?
 
There are 3 places where this can go wrong.

1. Original field.
You say this field is text. You will need to enter an expression into the
Field row in query design, to convert this into a real date. Parse the text
with Left(), Mid(), and Right(). Convert to date with DateSerial(). Of
course, that only works if the expression can be understood as a date, so
test with IsDate(). Be sure to use CVDate() to the result is understood as a
date. And finally, the result needs to be aliased with a new name to avoid
confusion with the original field.

So, type this expression (it's one line) into a fresh column in query
design:
CounselDt: CVDate(IIf(IsDate([CounselDate]),
DateSerial(Left([CounselDate],2), Mid([CounselDate],3,2),
Right([CounselDate],2)), Null))

2. Parameters
The parameters can also be misunderstood, so declare them by choosing
Parameters on the Query menu (in query deign.) Access opens a dialog box.
Enter the names on 2 rows:
[Enter Start Date] Date/Time
[Enter End Date] Date/Time
Now use *exactly* those names in the Criteria row under the calculated field
you entered above:
Between [Enter Start Date] And [Enter End Date]
Data entry will need to be according to your regional settings for normal
dates, so it does not make sense to specify the criteria as part of the
parameter names (since it could be different on someone else's computer.)

3. Display
On your report, set the Control Source of the text box to:
=[Enter Start Date])
and set the Format property of this box to whatever you want.

(BTW, don't forget to change the Control Source of the date field to the
calculated field CounselDt instead of CounselDate.)

More info on typecasting and ensuring Access understands you in this
article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Pat,

That is correct.This is part of a data collection system in a all volunteer
organization used to track cases All the 50 fields in the table are text
with the exception of the System Date and several memo fields. In a previous
version of the program the date text was mm/dd/yy and I used CDate to
convert all the dates and it worked fine. Apparently the programmer decided
that it would be easier to sort on yy/mm/dd.

Marv

Pat Hartman(MVP) said:
Are you saying that the date is actually stored as a text field with only
a two digit year? Or is the date really a date/time datatype that is
simply formatted to look like yy/mm/dd?

Marv Trott said:
The date field is in a linked table that I cannot change. The dates are
stored as text yy/mm/dd. I want the user to be able to enter mm/dd/yy and
the report header to display the selected date range as mm/dd/yy To
mm/dd/yy.

The query is: Qry Counselor Activity by Date Range.

The CounselDate criteria is: Between [Enter Start Date yy/mm/dd] And
[Enter End Date yy/mm/dd]

The control source in the reports is:

="Qry Counselor Activity by Date Range" & " " & [Enter Start
Date yy/mm/dd] & " To " & [Enter End Date yy/mm/dd].

Only the "yy/mm/dd To yy/mm/dd" is displayed in the report header.

What code do I need to make this happen?

Thanks for your help.

Marv
Click to expand...
Click to expand...
 
Allen,

Thanks for the advice. Since I'm in a volunteer organization I will work on
this later in the week.

Marv

Allen Browne said:
There are 3 places where this can go wrong.

1. Original field.
You say this field is text. You will need to enter an expression into the
Field row in query design, to convert this into a real date. Parse the
text with Left(), Mid(), and Right(). Convert to date with DateSerial().
Of course, that only works if the expression can be understood as a date,
so test with IsDate(). Be sure to use CVDate() to the result is understood
as a date. And finally, the result needs to be aliased with a new name to
avoid confusion with the original field.

So, type this expression (it's one line) into a fresh column in query
design:
CounselDt: CVDate(IIf(IsDate([CounselDate]),
DateSerial(Left([CounselDate],2), Mid([CounselDate],3,2),
Right([CounselDate],2)), Null))

2. Parameters
The parameters can also be misunderstood, so declare them by choosing
Parameters on the Query menu (in query deign.) Access opens a dialog box.
Enter the names on 2 rows:
[Enter Start Date] Date/Time
[Enter End Date] Date/Time
Now use *exactly* those names in the Criteria row under the calculated
field you entered above:
Between [Enter Start Date] And [Enter End Date]
Data entry will need to be according to your regional settings for normal
dates, so it does not make sense to specify the criteria as part of the
parameter names (since it could be different on someone else's computer.)

3. Display
On your report, set the Control Source of the text box to:
=[Enter Start Date])
and set the Format property of this box to whatever you want.

(BTW, don't forget to change the Control Source of the date field to the
calculated field CounselDt instead of CounselDate.)

More info on typecasting and ensuring Access understands you in this
article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Marv Trott said:
The date field is in a linked table that I cannot change. The dates are
stored as text yy/mm/dd. I want the user to be able to enter mm/dd/yy and
the report header to display the selected date range as mm/dd/yy To
mm/dd/yy.

The query is: Qry Counselor Activity by Date Range.

The CounselDate criteria is: Between [Enter Start Date yy/mm/dd] And
[Enter End Date yy/mm/dd]

The control source in the reports is:

="Qry Counselor Activity by Date Range" & " " & [Enter Start
Date yy/mm/dd] & " To " & [Enter End Date yy/mm/dd].

Only the "yy/mm/dd To yy/mm/dd" is displayed in the report header.

What code do I need to make this happen?

Thanks for your help.

Marv
Click to expand...
Click to expand...
 
Back
Top