Solved

Convert Text to Date in Query and Search Date Range

Posted on 2013-01-07
17
450 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

828 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