report by date

[billa856]

Hi,
My project is in MS Access 2002.
In that I have one form which I am using to generate report by date.
In that form I have two three fields StartDate,EndDate,ItemNo all are of type Text.
Now when I select StartDate and EndDate both of same Year then its working properly.
But if I select StartDate from Previous Year and Today's Date as EndDate then it will simply show results of current year and only of StartDate.
Means if i enter StartDate=1/1/2007
and EndDate=02/19/2008
then it will show all results of current year(1/1/2008-02/19/2008) and results ,match date only 1/1/2007
but its not showing results between 1/1/2007 to 1/1/2008.

I am using this query

SELECT *
FROM PRODUCTION
WHERE ((PRODUCTION.ItemNo=Forms!Form1!ItemNo) And ((PRODUCTION.ODate) Between Forms!Form1!StartDate And Forms!Form1!EndDate));

I think my all fields are of type Text that's why this problem occur.
So I don't know what to do?
Thanks for Help.

[Scott Price]

Have you tried changing the data types in each date field to the Date data type?

Regards,
Scott

[billa856]

Have you tried changing the data types in each date field to the Date data type?

Regards,
Scott

No I don't know how to do that?
Is it like this
PARAMETERS [Forms]![Form1]![StartDate] DateTime, [Forms]![Form1]![EndDate] DateTime;
SELECT *
FROM PRODUCTION
WHERE (((PRODUCTION.ODate) Is Not Null And (PRODUCTION.ODate) Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]));

Or something else?
whenever i run this query it will show me that you cancled previous operation.

[Scott Price]

Open the table concerned in Design view. Click on the field that contains your date entries and you will see in the second column the data type. Change to Date/Time.

This will force valid Date/Time comparisons rather than what you have now, which is a String comparison.

Regards,
Scott

[billa856]

Sorry but I can't change its type from text to DateTime bcz some of entries in Date field are blank.
I have to use the tables created by some other peoples working before me on this project.So for easy doing they use Text as type instead of DateTime and also its format of entering date is mm/dd/yy instead of longDate,mediumDate or shortDate.And now if I change it then I have to change the many forms and tables in entire project and it will take time more than a week bcz its not only one project but its two projects merge in one.So its not easy for me to do that.

[Scott Price]

As you are finding out, "for easy doing" is a very deceitful argument. In the moment it may seem easier, however, in the long run it ALWAYS turns out harder. Date/Time literals are made for exactly what you are trying to do, but if you choose to not use them, it is NOT 'for easy doing', sorry to break this to you.

VBA provides the CDate() function which can be used in vba code to create a Date/Time value from a String value. However, I don't think you can use it inside an Access query. You can try if you like.

Otherwise you will find that looking for the 'easy way out' will cost you more time, money and frustration than just doing it right in the first place.

Good luck!

Regards,
Scott

[NeoPa]

Scott is absolutely right of course. We HIGHLY recommend you redesign your table for easier use.

However, to get you moving you can try the following.

SELECT *
FROM PRODUCTION
WHERE (([ItemNo]=Forms!Form1![ItemNo])
  AND  (CDate([ODate]) Between Forms!Form1!StartDate AND Forms!Form1!EndDate))

PS It clearly doesn't take too much imagination to see why a string comparison on dates formatted as m/d/y would make a pig's ear of the whole process ;)

[billa856]

I didn't create or use that thing for my sake of easy doing, but programmer working before me who created the database, did that for his convinience and now I have to use it.But I think now I have to change all things on my own.When I started on project ,at that time I have doubt that it will create problems in future and now its becoming true.It will take time to change all things but I have to do it otherwise it will create more problems afterwards.

And ya ur CDate() function is not working in Query.

By the way thanks for ur help.

[NeoPa]

...
And ya ur CDate() function is not working in Query.

By the way thanks for ur help.

That's interesting. I tested it in mine & it worked fine.
When you say it didn't work, what did you mean exactly?
Are you sure it was the CDate() part that wasn't working?

And lastly, you're very welcome. I hope I can help you get past this little problem to allow you to focus on updating your database without the added pressure of producing this query still being required.

[billa856]

That's interesting. I tested it in mine & it worked fine.
When you say it didn't work, what did you mean exactly?
Are you sure it was the CDate() part that wasn't working?

And lastly, you're very welcome. I hope I can help you get past this little problem to allow you to focus on updating your database without the added pressure of producing this query still being required.

Ya whenever I used CDate() then it will show nothing.But if I remove it then it will show results but with the same problems I mentioned before.But now I am updating the database.
Thanks for ur help.

[NeoPa]

It's good that you're going the redesign route.

However, not having CDate() available is something I'd regard as worrying. Would you be able to do a quick test in the VBA immediate window (Alt-F11 from Access + Ctrl-G) :

Debug.Print CDate("1 Dec 2008")

I'd be interested to see what's displayed.

Also, check in the VBA window what references are linked in your project. Select Tools / References to see the currently selected list.

[billa856]

It's good that you're going the redesign route.

However, not having CDate() available is something I'd regard as worrying. Would you be able to do a quick test in the VBA immediate window (Alt-F11 from Access + Ctrl-G) :

Debug.Print CDate("1 Dec 2008")

I'd be interested to see what's displayed.

Also, check in the VBA window what references are linked in your project. Select Tools / References to see the currently selected list.

its showing no results.
By the way, Date format is also different as I mentioned in previous posts..Its not one of the ShortDate(mm/dd/yyyy),MediumDate(mm/ddd/yy) or LongDate(Sunday,june 19,1994).its mm/dd/yy.

And Ya list of references are listed below.
->Visual Basic For Application.
->Microsoft Access 10.0 Object Library.
->OLE Automation
->Microsoft Active X Data Objects 2.1 Library
->Microsoft Windows Common Controls-2.6.0(SP3)
->Microsoft Calender Control 10.0

[Scott Price]

This is indeed strange... btw you can also use the shorter syntax in the immediate window:

?CDate("12/23/02")

(it's easier to type :-)

The mm/dd/yy format is not the problem here as it is a valid date format that Access will recognize and convert to a date.

I would suggest two things:

A. If this test doesn't work in a brand new, clean database, you probably will need a re-install.

B. If it does work, you likely have some kind of corruption in your existing database.

Regards,
Scott