Solved

employee anniv date with text field type

Posted on 2016-11-14
9
32 Views
Last Modified: 2016-11-16
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
0
Comment
Question by:fordraiders
  • 4
  • 4
9 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 41886966
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41886983
i have in a table
field1       field 2
1               january
2               february

etc...

the combbox is bound to column 1
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886991
then your query should work...

can  you upload a copy of the db?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41887026
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41887074
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41887129
Between [forms]![Maintenance]![Combo25] +1 And [forms]![Maintenance]![Combo27]+1

I had to do this in order to get it to work ?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41887152
are you saying this

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

made it work?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41888287
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41889906
I changed the bound column. and kept field type as date/time. worked
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now