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.

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

The date hired field looks like  


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

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"
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
FordraidersAuthor Commented:
i have in a table
field1       field 2
1               january
2               february


the combbox is bound to column 1
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?
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...
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.
FordraidersAuthor Commented:
Between [forms]![Maintenance]![Combo25] +1 And [forms]![Maintenance]![Combo27]+1

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

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

made it work?
FordraidersAuthor Commented:
I changed the bound column. and kept field type as date/time. worked
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.