Solved

Convert Text to Date in Query and Search Date Range

Posted on 2013-01-07
17
446 Views
Last Modified: 2013-01-07
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
0
Comment
Question by:tammyborden32
  • 10
  • 7
17 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752004
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
 

Author Comment

by:tammyborden32
ID: 38752158
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752224
Can you give samples of [TicketDate]?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:tammyborden32
ID: 38752258
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752328
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752522
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
 

Author Comment

by:tammyborden32
ID: 38752536
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752576
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
 

Author Comment

by:tammyborden32
ID: 38752607
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752616
Sample did not attach.
What is external db type?
0
 

Author Comment

by:tammyborden32
ID: 38752628
The external is also Access 2010.
Database3.accdb
0
 

Author Comment

by:tammyborden32
ID: 38752653
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752715
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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 38752749
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752759
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752822
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
 

Author Closing Comment

by:tammyborden32
ID: 38752842
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question