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
tammyborden32Asked:
Who is Participating?
 
jerryb30Connect With a Mentor Commented:
I changed ticketDate to a text field, and used dateValue in lieu of cdate.
Hard coded with or without '#' works.  Parameter of [start date] and [end date] works with or without dateValue AND no '#'.

So, basically, same result as cdate.
And format of TicketDate.
0
 
jerryb30Commented:
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#
0
 
tammyborden32Author Commented:
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?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jerryb30Commented:
Can you give samples of [TicketDate]?
0
 
tammyborden32Author Commented:
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.
0
 
jerryb30Commented:
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 '#'
0
 
jerryb30Commented:
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?
0
 
tammyborden32Author Commented:
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]));
0
 
jerryb30Commented:
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.
0
 
tammyborden32Author Commented:
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.
0
 
jerryb30Commented:
Sample did not attach.
What is external db type?
0
 
tammyborden32Author Commented:
The external is also Access 2010.
Database3.accdb
0
 
tammyborden32Author Commented:
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.
0
 
jerryb30Commented:
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.
0
 
jerryb30Commented:
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.
0
 
jerryb30Commented:
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.)
0
 
tammyborden32Author Commented:
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.
0
All Courses

From novice to tech pro — start learning today.