employee anniv date with text field type

I have a query i'm trying to correct:
In my table...someone set up the "datehired" as text.

In my sql i'm trying to select employees within a given months range...september  to  december...i.e.
etc...

combo25 is the start month "september  to         combo27 "december" end month

The date hired field looks like  

02/01/2010
05/06/1995

Problem:
qry is pulling back all records..


I changed the  field type to date/time   "Short date"
but it did not help ?



SELECT [First Name] & " " & [Last Name] AS [Full Name], [Employees Information2]![Date Hired] AS [Date Hired], [Employees Information2].[Report to] AS [Reports To], [Employees Information2].Title, Year(Date())-Year([Employees Information2]![Date Hired]) AS [Years of Service]
FROM [Employees Information2]
WHERE (((Month([Employees Information2]![Date Hired])) Between [forms]![Maintenance]![Combo25] And [forms]![Maintenance]![Combo27]))
ORDER BY Month([Employees Information2]![Date Hired]), Year(Date())-Year([Employees Information2]![Date Hired]) DESC;

Open in new window



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Your best option is to bite the bullet and change the date field to BE a real datetime data type.  Then you won't have any difficulty working with it as a date.

No "between" logic will work correctly when comparing two dates stored as text in mm/dd/yyyy format.  Since "01/30/2016" will ALWAYS be less than "02/01/2001"
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
what is the row source of your combo boxes?
add a column to represent the Months in Numeric values
1 January
2 February
..

12 December

and set the Bound Column of the combo to 1

your criteria
Month([Employees Information2]![Date Hired])  - will return a numeric value  1 to 12
0
 
FordraidersAuthor Commented:
i have in a table
field1       field 2
1               january
2               february

etc...

the combbox is bound to column 1
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Rey Obrero (Capricorn1)Commented:
then your query should work...

can  you upload a copy of the db?
0
 
FordraidersAuthor Commented:
so if i choose the  9 - september   to 12 - december...I only want to pull months from 9 to 12..the year and day will not come into play...
0
 
Rey Obrero (Capricorn1)Commented:
yes that is correct, all records with Month _  sept to dec will be return regardless of the year.

if you want to consider the Year and day the criteria will be different.
0
 
FordraidersAuthor Commented:
Between [forms]![Maintenance]![Combo25] +1 And [forms]![Maintenance]![Combo27]+1

I had to do this in order to get it to work ?
0
 
Rey Obrero (Capricorn1)Commented:
are you saying this

Between [forms]![Maintenance]![Combo25] +1 And [forms]![Maintenance]![Combo27]+1

made it work?
0
 
FordraidersAuthor Commented:
I changed the bound column. and kept field type as date/time. worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.