Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Text to Date in Query and Search Date Range

Posted on 2013-01-07
17
Medium Priority
?
462 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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