Link to home
Start Free TrialLog in
Avatar of tammyborden32
tammyborden32

asked on

Convert Text to Date in Query and Search Date Range

Hello All,

I have a Access 2010 database.  I need to search by Date.  The field however is text.  I do not want to change the format of the field.

How can I use a query to change the text to date format, and then search Between dates?

Thanks,

Tammy
Avatar of jerryb30
jerryb30
Flag of United States of America image

Does the field naturally convert to date with CDTAE or DateValue function?  If you use those, the field will be regarded as a date.
Select cdate(yourfield) from yourTable where cdate(yourfield) between #1/1/2013# and #1/3/2013#
Avatar of tammyborden32
tammyborden32

ASKER

In the query grid I added:

Exp1:  CDate([TicketDate])

For Criteria:  Between [Start Date] And [End Date].

When I run the query I enter 1/1/13 for Start Date and 1/7/13 for End Date.  The results give me any date in January 2012  and 2013.  We just using the dbase in 2012.

I don't think the CDate is changing the field type?
Can you give samples of [TicketDate]?
2/6/2012
2/13/2012
10/1/2012
1/3/2013

Also, just discovered in the query that I can now format as Date.  Puzzled why the results are not correct.
I use:
SELECT ([ticketDate]) AS Expr1
FROM tblTammy
WHERE (((([ticketDate])) Between [start date] And [enddate]));
and enter 1/1/2013 and 1/7/2013, with no '#'

Works for me.

Also:
SELECT CDate([ticketDate]) AS Expr1
FROM tblTammy
WHERE (((CDate([ticketDate])) Between [start date] And [end date]));

same dates, no '#'
BTW: Formatting as a date in a query is more of a display issue.
When I use date delimiter ('#') in either of my queries, I get no records.
Can you post your SQL?
Yep, I realize that the format is for display purposes.  Although, if it were a text field, it would not provide the date formats in the list.  So I am assuming that the CDate did the conversion.  I just don't understand why the weird results.

SELECT TimeTicketDet.JobNo, OrderDet.PartDesc, CDate([TicketDate]) AS Expr1, TimeTicketDet.ManHrs, [ManHrs]*[ActualPayRate] AS LaborCost, [ManHrs]*[BurdenRate] AS BurdenCost, CDate([SearchDate]) AS Expr2
FROM OrderDet RIGHT JOIN TimeTicketDet ON OrderDet.JobNo = TimeTicketDet.JobNo
WHERE (((CDate([TicketDate])) Between [Enter Start Date] And [Enter End Date]));
Are you using '#' delimiter?
Linked to another database?
If answer is 'No' to both questions, I am confused, too. My simple re-creation is not giving me problems.
If all else fails, please post a small db with only necessary tables, dummy data.
Nope, I am not using # delimiter.  

The underlying table is linked from another database.

Importing the table to this database does not resolve the issue though.

I've attached a sample.  Thanks for your help.
Sample did not attach.
What is external db type?
The external is also Access 2010.
Database3.accdb
I did just try typing in the criteria row Between #1/1/13# and #1/7/13#.  This produced the correct results.  However, the end user is running a run-time version and needs to enter the date values each month in a command prompt.
My results:
Hardcoding
between  #1/1/2013# and #1/7/2013#
AND
between 1/1/2013 and 1/7/2013
AND
between >= [Enter Start Date] (1/1/2013, no '#') and <= [Enter end date] (1/7/2013, no '#')

returns the necessary 103 records.

Trying to use #1/1/2013# and #1/7/2013#  for a parameter prompt gives error.

I note TicketDate is date field in attached db.

What results are you getting?

I am now on Access 2010.
ASKER CERTIFIED SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I cannot speak for the run-time version here. Try it without the '#' in the prompt.
I am a little confused myself reference the prompted parameters.
A recommendation: Create a form for entry of the start and end date. This allows for checking of the date formats, maybe allowing for a DatePicker calendar control, too.
(Not conversant on that method myself.)
Thanks for your help.  I'm going to change the field type to date.  It works correctly that way.   I forgot I changed it in the sample.  I was reluctant to do that as this is a third party purchased program.  The database that I'm working on is for customized reports not included in the base system.