Date Parameter includes ALL dates 1

[kmkland]

I created a date parameter to basically search for all dates within a certain month. However, the results include dates from the same month but different years (i.e., I need all of Jan 05 dates but not 2004 etc...), which is not what I need. Here is my SQL statements:

SELECT [90 Day Review Date].[SS#], [90 Day Review Date].[Employee Name], [90 Day Review Date].Shift, [90 Day Review Date].Position, [90 Day Review Date].[Immediate Supervisor], [90 Day Review Date].[90 Day Review]
FROM [90 Day Review Date]
WHERE ((([90 Day Review Date].[90 Day Review]) Between [Enter Start Date in mm/dd/yyyy Format] And [Enter End Date in mm/dd/yyyy Format]));

Please, any assistance would be greatly appreciated!!!
Kim

 

[GingerR]

I couldn't replicate your problem no matter what format of dates I tried, even when I just typed in "1/1" and "1/15". Perhaps you did not type in "2005" or "05" when you ran it. If that's the case, Access will default to the current date's year, which is 2004.

By the way, the user could just type in m/d/yy or m/d and no year (defaults to current year) instead of the full "mm/dd/yyyy" and it still works fine.

 

[kmkland]

Hi Ginger,
I did enter "05", but my results still included other years. I also tried "1/1/05" and I got no results.
Not sure what to do....
Kim

 

[kmkland]

Ginger,
Would it be helpful to know that this query is based off of the following query??:

SELECT [Employee Information and Status Inner Join].*, [Hire Dates].[Hire Date], DateAdd("d",90,[Hire Date]) AS [90 Day Review]
FROM [Hire Dates] RIGHT JOIN [Employee Information and Status Inner Join] ON [Hire Dates].[SS#] = [Employee Information and Status Inner Join].[SS#];

Thanks!
Kim

 

[GingerR]

i thought the first post, you said for Jan you got lots of results. But the last post said you got no results?

what happens if you substitute in i.e. 1/1/05 and 1/31/05 instead of the [Enter blah] prompts?

can you please list some sample data that is the result of the query "90 Day Review Date" (not SSN of course, but Hire Date, Review Date)

also, is "[Employee Information and Status Inner Join]" a query as well? What is it? please post the code, all the way back to the table structure. thanks.

 

[kmkland]

Hi Ginger,
Just to clarify: if I enter the date parameter for the month of, let's say, Jan 05 in mm/dd/yy format, I get results which include dates in Jan for all years. If I enter the same dates in m/d/yy format, I get no results.

This is the statement for retrieving the dates within the parameter:

SELECT [90 Day Review Date].[SS#], [90 Day Review Date].[Employee Name], [90 Day Review Date].[Hire Date], [90 Day Review Date].Shift, [90 Day Review Date].Position, [90 Day Review Date].[Immediate Supervisor], [90 Day Review Date].[90 Day Review]
FROM [90 Day Review Date]
WHERE ((([90 Day Review Date].[90 Day Review]) Between [Enter Start Date in mm/dd/yy Format] And [Enter End Date in mm/dd/yy Format]));

This is the statement for the query from which the previous statement is based:

SELECT [Employee Information and Status Outer Join].*, [Hire Dates].[Hire Date], DateAdd("d",90,[Hire Date]) AS [90 Day Review]
FROM [Hire Dates] RIGHT JOIN [Employee Information and Status Outer Join] ON [Hire Dates].[SS#] = [Employee Information and Status Outer Join].[SS#];

This is the statement for the outer join:

SELECT [Employee Information].[SS#], [Employee Information]![First Name] & ' ' & [Employee Information]![Last Name] AS [Employee Name], [Employee Status].[Shift], [Employee Status].[Position], [Employee Status].[Immediate Supervisor]
FROM [Employee Information] LEFT JOIN [Employee Status] ON [Employee Information].[SS#]=[Employee Status].[SS#];

This is a sample of the data results I get from the date parameter query:
1. If I enter "1/1/05-1/31/05", I get no results.
2. If I enter "01/01/05-01/31/05", I get:
90 Day Review
01/09/2005
01/23/2005
01/16/2005
01/09/2005
01/22/2004
01/15/2004
01/11/2001
01/03/2001
01/30/2000
01/23/2000

Hope this helps...

Regards,
Kim

 

[GingerR]

ok, after much messing around, it's not reading the entries as dates.....Try adding your prompts as Parameters and set them as Date/Time. If you don't know how: Copy the exact prompt including the square brackets ([Enter Start Date etc...]), right-click in the grey area above the query grid and choose Parameters, paste it in on the left, and put the type as Date/Time. do the same for the other prompt. Should work now.

g

 

[kmkland]

Ginger,

Thank you sooo much!!!!


Regards,
Kim