How to form a query for my need

[sarathicse]

Guys,

I have a table as shown below


I need a query to get all the records for January Column. My SQL view of query looks like below.

SELECT [Record Count].id_Date, [Record Count].[Total number of Successful Jobs], [Record Count].[Number of Jobs completed after manual run], [Record Count].[Number of Jobs completed after restart], [Record Count].[Number of jobs failed - No-follow-up Needed], [Record Count].[Number of jobs failed - Resolved by SPC Team], [Record Count].[Total Number of Jobs Monitored], [Record Count].Remarks
FROM [Record Count]
WHERE ((([Record Count]![id_Date]) Like "*January*"))
ORDER BY [Record Count].id_Date;



I am not getting what i need. Is there any modification i need to do to get only the records having the values January in the Date Field.

Thanks in advance for any help you can offer on this.



Thanks,
Sarathi.

[Rawb]

The problem is that, even though Access is displaying the date like "Monday, January 16, 2012", that's not how the data is actually being stored. Probably the easiest way would be to use the DatePart Function to pull out only the portion you want to compare:

WHERE ((DatePart("m",[Record Count]![id_Date]) = 1) AND (DatePart("yyyy",[Record Count]![id_Date]) = 2012))

If you use the above WHERE condition, it will look for any results that include both the year 2012 and the month 1 (January).

[Bob Fitz]

Try:
WHERE (((Month([Record Count]![id_Date])) = 1))

[sarathicse]

Thanks guys both the condition worked. Now I have one more question. I am using this in a Form.

The form looks like below.

Now i have two parameter in query to get the value from the form.

The year part i have no difficulties. But in Month part my client expect me to have a List of all the months.

But in the query we are having number based conditions for Month.

Is there a way to convert the Month we got from the list to numeric.

Thanks in advance.

Sarathi.

[Rawb]

Just make the Combo Box two columns instead of one. Then put the month numbers in the first column and set the column width to 0" to hide it.

[sarathicse]

Thanks Rawb,

That worked Perfect. I am entirely new to Access and this is my first post in this forum and I am more than satisfied with you guys response.

Thanks again.

[Rawb]

I'm glad we could help!