Get the Selected date from the calender

[kbsudhir]

Hi All,

I have calender named "SelDate" on my form.
I wnat to create a query which will get the data as per the date & month selected in the calender control.

I have used calender control 11.0. And I am using Access 2003.

Please guide.

Thanks
Sudhir

:help :help

[kbsudhir]

Well I am able to capture it using the below line of code.

Forms!FrtEd!SelDate

But the problem is the query is not pulling any data.

The Date in the filed is in the belwo format :

"9/16/2008 2:31:04 PM"

As I am capturing the date time of the incoming mails to my inbox hence the format is like that.

Please guide me how to get the query recognized it as a date & pull the desired data.

:dunno :dunno

Thanks
Sudhir

[OBP]

Create a seperate Column, in that column type in
realdate: format([Your date fields name], "mm/dd/yyyy")

where "Your date fields name" is the name of the Incoming mail date field.

In the Criteria Row of the new column type in your
Forms!FrtEd!SelDate

[kbsudhir]

When I used the Below query which is without any criteria, it worked

SELECT Assigned.Subject, Assigned.ReceivedDateTime
FROM Assigned
GROUP BY Assigned.Subject, Assigned.ReceivedDateTime, Format([ReceivedDateTime],"mm/dd/yyyy");

But when I inserted the criteria then it did not pull any records


SELECT Assigned.Subject, Assigned.ReceivedDateTime
FROM Assigned
WHERE (((Format([ReceivedDateTime],"mm/dd/yyyy"))=#9/18/2008#))
GROUP BY Assigned.Subject, Assigned.ReceivedDateTime;


And I have records for that date in the query.

Please help

Sudhir

[CreganTur]

Did you try:

SELECT Assigned.Subject, Assigned.ReceivedDateTime
FROM Assigned
WHERE (((Format([ReceivedDateTime],"mm/dd/yyyy"))=#9/18/2008#))
GROUP BY Assigned.Subject, Assigned.ReceivedDateTime, Format([ReceivedDateTime],"mm/dd/yyyy");

[kbsudhir]

Same results Randy,

Its not pulling the data

[OBP]

Can you post a zipped copy of the database with a little dummy data in it?

[Slyboots]

Do this instead:

SELECT Assigned.Subject, Assigned.ReceivedDateTime
FROM Assigned
WHERE DateValue(Assigned.ReceivedDateTime) = #9/18/2008#
GROUP BY Assigned.Subject, Assigned.ReceivedDateTime

[kbsudhir]

I am getting the error as "Data Type Mismatch in Criteria Expression"

[kbsudhir]

I have uploaded a database with dummy data as you requested

[CreganTur]

I may be way off base here, but I think the issue has to do with the fact that you're pulling the full General Date into your tables. You may want to look into using the Format method to change the general date that the calendar gives you so you're only pulling a short date (ie: 9/18/2008) into your table.

Once you make this change, then it is easy to query your table using another short date as a part of the WHERE clause for your SQL statement.

For Example:
open the db1 that you provided us, and go into the Table. Find your 9/18/2008 dates and overwrite the general formatted date with a short date of 9/18/2008. There are 2 records you can overwrite. Now close the table and pull a query using this statement:

SELECT Table1.EntryID, Table1.ConversationIndex, Table1.Subject, Table1.ReceivedDateTime
FROM Table1
WHERE (((Table1.ReceivedDateTime)=#9/18/2008#));


I really think it's coming down to a simple issue of data formats that aren't palying nice.

HTH:thumb

[kbsudhir]

Well got it, but I can delete the time as it is also very important for me.

I not possible to format the filed while writing the query to "mm/dd/yyyy" format..???? then give the parameter..??

[kbsudhir]

I tried the below query but of no use, no errors but will not pull the required data.

SELECT Table1.EntryID, Table1.ConversationIndex, Table1.Subject, Table1.ReceivedDateTime
FROM Table1
WHERE (((Format([ReceivedDateTime],"mm/dd/yyyy"))=#9/19/2008#))
GROUP BY Table1.EntryID, Table1.ConversationIndex, Table1.Subject, Table1.ReceivedDateTime;

[CreganTur]

Yeah, I tried using the Format Function as well, to no avail.

If the time is important data, why can't you have 2 different fields? A Date field and a Time field. When you pull your date and time from the calendar into your table, you can format so that only the date ("mm/dd/yyyy") is pulled into Date field and then format it again to get the time into the Time field.

[Carl A]

Is this what you want?

SELECT Table1.Subject
FROM Table1
WHERE (((DateValue([Table1].[ReceivedDateTime]))=CStr("9/18/2008")))
GROUP BY Table1.Subject, Table1.ReceivedDateTime;

[kbsudhir]

Yes, it works.

[kbsudhir]

Well,

Now I tried to create query that joins two tables & criteria is the same as above.

But now its not working.

Table1 has a filed name called a"ssignedby" this filed captures the userId of the person now I am joining the table called names so that I can get the name of the person assiging instead his userID.

Without criteria it works but when I enter the criteria in thw way as advised by Carl, its not working ......??????

:think:

[CreganTur]

Table1 has a filed name called a"ssignedby" this filed captures the userId of the person now I am joining the table called names so that I can get the name of the person assiging instead his userID.


Do you have to use SQL? You could use a DLookup function to get the name instead of trying to add a join.

[kbsudhir]

Thanks Randy,

I am still learning Access, I learned a lot & still I have to learn a lot.
I will do some research on Dlookup & try to understand this concept & implemnt in my case.

I will let you know the progress made by me in this regard.