Convert linked text field to date and then filter

[DJ Notion via AccessMonster.com]

I have a form that filters between two dates and need it to work with a text
field that I have made look like a date. The text field is in a linked table
that I can't change the field type.

The linked table has a field called CollectionDate that is 14 digits in the
order by year, month, day, hours, minutes, seconds and displays as
yyyymmddhhmmss. On another form I have an unbound text box with the
following as the control source: =Mid([CollectionDate],5,4) & "2008".
This makes the CollectionDate look like 00/00/0000. I don't need the time at
all. The problem is that it isn't really a date and when I try to filter it
it doesn't work. As a side note, the form that actually filters the dates
works beautifully with other databases so no changes need to be made to the
code I am using; the reason why I haven't provided it. I just need to
manipulate this CollectionDate text field that is creating the problem.

Is there someway I can filter the CollectionDate text field between two dates
or do I need to convert it to a date and then filter. If so, how can I
accomplish that? Any help is appreciated. Thanks.

 

[Duane Hookom]

You can convert your text with an expression like
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val(Mid([CollectionDate],7,2)))

 

[DJ Notion via AccessMonster.com]

This converted the text to look exactly how I had it which is perfect because
now it is a date and not just text.

I am still having trouble getting the report to filter the dates though.
This is how I have everything setup.

I have a form with an unbound text box with the control source set to
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val
(Mid([CollectionDate],7,2)))
This displays the date perfectly on my form.

I have my report with a text box with the control source set to
CollectionDate. This also displays the date perfectly on my report.

However, when I define the criteria in the query behind the report to filter
between the dates input, I get no results. My query consists of all the
fields on the report with a criteria on the CollectionDate field set to:
Between [Forms]![Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]

SQL statement is: SELECT UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.
Attention, UPS_SHIPPING_EB.[Address 1], UPS_SHIPPING_EB.[Address 2],
UPS_SHIPPING_EB.[Address 3], UPS_SHIPPING_EB.[City or Town], UPS_SHIPPING_EB.
[State/Province/County], UPS_SHIPPING_EB.[Postal Code], UPS_SHIPPING_EB.
[Country/Territory], UPS_SHIPPING_EB.Telephone, UPS_SHIPPING_EB.[Tracking
Number], UPS_SHIPPING_EB.Weight, UPS_SHIPPING_EB.[Reference 1],
UPS_SHIPPING_EB.[Package Type], UPS_SHIPPING_EB.[Service Type],
UPS_SHIPPING_EB.CollectionDate, UPS_SHIPPING_EB.[Total Shipment/Handling
(Published Charges)], UPS_SHIPPING_EB.[Total Shipment/Handling (Negotiated
Rates Charge)]
FROM UPS_SHIPPING_EB
ORDER BY UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.CollectionDate;

This is the same criteria I use in another database and it works beautifully.
What do I need to change in the control source for that text box on my report
or in my query to have the data display when filtered with the dates entered?


Your help is appreciated. Thanks.

-----

You can convert your text with an expression like
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val(Mid([CollectionDate],7,2)))

I have a form that filters between two dates and need it to work with a text
field that I have made look like a date. The text field is in a linked table

[quoted text clipped - 14 lines]

or do I need to convert it to a date and then filter. If so, how can I
accomplish that? Any help is appreciated. Thanks.

 

[DJ Notion via AccessMonster.com]

Correction, the SQL statement with the criteria is below. The one I have
above doesn't include the criteria.

SELECT UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.Attention,
UPS_SHIPPING_EB.[Address 1], UPS_SHIPPING_EB.[Address 2], UPS_SHIPPING_EB.
[Address 3], UPS_SHIPPING_EB.[City or Town], UPS_SHIPPING_EB.
[State/Province/County], UPS_SHIPPING_EB.[Postal Code], UPS_SHIPPING_EB.
[Country/Territory], UPS_SHIPPING_EB.Telephone, UPS_SHIPPING_EB.[Tracking
Number], UPS_SHIPPING_EB.Weight, UPS_SHIPPING_EB.[Reference 1],
UPS_SHIPPING_EB.[Package Type], UPS_SHIPPING_EB.[Service Type],
UPS_SHIPPING_EB.CollectionDate, UPS_SHIPPING_EB.[Total Shipment/Handling
(Published Charges)], UPS_SHIPPING_EB.[Total Shipment/Handling (Negotiated
Rates Charge)]
FROM UPS_SHIPPING_EB
WHERE (((UPS_SHIPPING_EB.CollectionDate) Between [Forms]!
[Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date] Between [Forms]!
[Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]))
ORDER BY UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.CollectionDate;

This converted the text to look exactly how I had it which is perfect because
now it is a date and not just text.

I am still having trouble getting the report to filter the dates though.
This is how I have everything setup.

I have a form with an unbound text box with the control source set to
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val
(Mid([CollectionDate],7,2)))
This displays the date perfectly on my form.

I have my report with a text box with the control source set to
CollectionDate. This also displays the date perfectly on my report.

However, when I define the criteria in the query behind the report to filter
between the dates input, I get no results. My query consists of all the
fields on the report with a criteria on the CollectionDate field set to:
Between [Forms]![Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]

SQL statement is: SELECT UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.
Attention, UPS_SHIPPING_EB.[Address 1], UPS_SHIPPING_EB.[Address 2],
UPS_SHIPPING_EB.[Address 3], UPS_SHIPPING_EB.[City or Town], UPS_SHIPPING_EB.
[State/Province/County], UPS_SHIPPING_EB.[Postal Code], UPS_SHIPPING_EB.
[Country/Territory], UPS_SHIPPING_EB.Telephone, UPS_SHIPPING_EB.[Tracking
Number], UPS_SHIPPING_EB.Weight, UPS_SHIPPING_EB.[Reference 1],
UPS_SHIPPING_EB.[Package Type], UPS_SHIPPING_EB.[Service Type],
UPS_SHIPPING_EB.CollectionDate, UPS_SHIPPING_EB.[Total Shipment/Handling
(Published Charges)], UPS_SHIPPING_EB.[Total Shipment/Handling (Negotiated
Rates Charge)]
FROM UPS_SHIPPING_EB
ORDER BY UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.CollectionDate;

This is the same criteria I use in another database and it works beautifully.
What do I need to change in the control source for that text box on my report
or in my query to have the data display when filtered with the dates entered?

Your help is appreciated. Thanks.

-----

You can convert your text with an expression like
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val(Mid([CollectionDate],7,2)))

[quoted text clipped - 4 lines]