Access 2000 date query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access 2000 date query

Author

Topic

edie442
Starting Member

3 Posts

Posted - 2004-08-16 : 16:44:41

Hello

I wonder if any of you could help me. I have had to convert a database from Lotus Approach to Access 2000. The database tracks employee sickness. One of the things they need is to query the database so that a user can type in two dates (say 1st April 2003 and 1st April 2004) to see who has been off sick. That’s easy enough. The problem is when someone’s sickness starts outside the search criteria (e.g. March 25th) and then runs into it. The query doesn’t pick it up, so a false result is returned. How can I code the SQL so that the query picks up dates that start outside the search criteria but finish inside it? Below is the code I’ve been using:

SELECT SICK.NAME, SICK.SECTION, SICK.TEAM, SICKDAY.[First Day], SICKDAY.[Last Day], SICKDAY.[Working Days]
FROM SICK INNER JOIN SICKDAY ON SICK.NI_NUMBER = SICKDAY.NI_NUMBER
WHERE (((SICKDAY.[First Day]) Between [Type The Beginning Date (dd/mm/yyyy):] And [Type The End Date (dd/mm/yyyy):])) OR (((SICKDAY.[First Day]) Like "**/03/2003") AND ((SICKDAY.[Last Day]) Like "**/04/2003"))
ORDER BY SICK.SECTION, SICK.TEAM, SICKDAY.[First Day], SICKDAY.[Last Day];

I’ve used "**/03/2003" and "**/04/2003" purely to see how the code acts. I should say that the user could key in any dates, so the code will have to allow for this.

I would be most grateful for any help!

Cheers

Dave
Taunton, England

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-16 : 19:14:51

I don't think you can use the LIKE operator when you're dealing with dates.
If you're trying to pick up all sickness events that either start or end in the query period, you should just need:

WHEN [First Day] BETWEEN [Type The Beginning Date] And [Type The End Date] OR
     [Last Day] BETWEEN [Type The Beginning Date] And [Type The End Date]

You can reference the same user-entered parameter many times in the same query; you just need to make sure that the parameter names (or prompts) are identical.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-08-17 : 00:19:53

Timmy's dead right.

What you're really doing in trying to use the LIKE operator is to search for dates which have the same month and year only. You should probably use Month and Year functions to make it clear what you mean.

Alternatively (if you're in for the hack) you could CONVERT your dates to strings, and then use the LIKE operator. Very hacky, but in theory OK, as long as you're explicit in your type casting between date and string.

If you're looking to make a comparison to see whether someone was sick anytime between dayX and dayY you really need to check the following cases (believe me, I learned the hard way)
1. Whether any sick periods end in the period in question
2. Whether any sick periods start in the period in question
3. Whether any sick period wholely contains the period in question

One last thing. If the user is able to "type the date", obviously you need to check whether what they typed in was a valid date, however, you also need to reflect back to them what date you think they meant. As there are a variety of possible date formats people will enter, I always reflect back the date in the dd-mmm-yyyy format, (or use the ISO standard format if you prefer). By "reflect", I mean to update the text box they typed into, with the converted date (ie convert their string to date, convert back to string in the given format). This ensures a common understanding of the date, and gives them an opportunity to check that you have taken their string to mean what they intended, and not some meaningless variation - eg 6/2/4 or 3/2.

Hope that (as they say) helps - feel free to ask more

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

edie442
Starting Member

3 Posts

Posted - 2004-08-19 : 15:25:57

Cheers for your help guys. The code works, but only picks up code that starts before the start date criteria. Any sickness that starts inside the search dates but finishes after the end date is left out. It seems strange because of the OR used in the statement. Any ideas?

Thanks

Dave

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-08-19 : 23:52:46

Sorry Dave

Just to make that more clear you need to change your query to the following


SELECT SICK.NAME, SICK.SECTION, SICK.TEAM, SICKDAY.[First Day], SICKDAY.[Last Day], SICKDAY.[Working Days]
FROM SICK INNER JOIN SICKDAY ON SICK.NI_NUMBER = SICKDAY.NI_NUMBER

--sick leave starts during the period
WHERE ((SICKDAY.[First Day]) Between [Type The Beginning Date (dd/mm/yyyy):] And [Type The End Date (dd/mm/yyyy):])
--sick leave ends during the period 
OR ((SICKDAY.[Last Day]) Between [Type The Beginning Date (dd/mm/yyyy):] And [Type The End Date (dd/mm/yyyy):])
--sick leave wholely contains the period in question
OR (
	((SICKDAY.[First Day]) < [Type The Beginning Date (dd/mm/yyyy):] )
	And 
	((SICKDAY.[Last Day]) < [Type The End Date (dd/mm/yyyy):])
) 

ORDER BY SICK.SECTION, SICK.TEAM, SICKDAY.[First Day], SICKDAY.[Last Day];

Enjoy

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

edie442
Starting Member

3 Posts

Posted - 2004-08-20 : 10:03:05

Sorry to be a pain lads.

The query runs, but prompts the user for four different dates ie:

Sickday.First Day
Type The Beginning Date (dd/mm/yyyy):
Type The End Date (dd/mm/yyyy):
First Day

Whatever date the user inputs to the last prompt becomes the first day of sickness for every record pulled out, and the database is pulling out practically every record! Tell me, what is the suicide rate for people who work with Access?

Any ideas? Or shall I bin it and go and live in a small cave somewhere and eat berries?

Cheers lads

Dave

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-20 : 10:17:08

The best thing for you to do is to break down the query into parts. Also, remove the parameters initially until you're happy with the results. So:
1. Run the query just checking if the sick start date is between 2 known dates
2. Ditto for the second condition (sick end date)
3. Ditto for the last one (2 known dates are within the start and end dates.
Then, tack the whole lot together using OR and bracketing each of the 3 clauses. Only when you're happy with the result should you start entering parameters into the equation.
btw - If Access can't resolve a field name it will assume it's a parameter. So you might not be entering them correctly in the query.
Or you could choose the cave option. Just don't eat the green berries (they're not ripe yet...)

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-08-31 : 19:16:25

If I could add to Timmy's wisdom, then I would, but alas, you can't improve on perfection...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources