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
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
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?
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]?
ASKER
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.
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 '#'
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?
When I use date delimiter ('#') in either of my queries, I get no records.
Can you post your SQL?
ASKER
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]));
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.
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.
ASKER
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.
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?
What is external db type?
ASKER
The external is also Access 2010.
Database3.accdb
Database3.accdb
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.)
(Not conversant on that method myself.)
ASKER
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.
Select cdate(yourfield) from yourTable where cdate(yourfield) between #1/1/2013# and #1/3/2013#