Search by Month

[Shepps]

Another question re : searching by month only

Sorry about this guys, got another question to ask

Im trying make a query, that will produce a report of anyone who has a date in their record that = the month and the year of the current date (i.e todays date).

Theres going to be 8 date fields, all of which need to be in the query, and i want it to produce a report if any of the dates match mm/yyyy.

Cheers for your help guys

 

[Brianwarnock]

Sorry about this guys, got another question to ask

Im trying make a query, that will produce a report of anyone who has a date in their record that = the month and the year of the current date (i.e todays date).

Theres going to be 8 date fields, all of which need to be in the query, and i want it to produce a report if any of the dates match mm/yyyy.

Cheers for your help guys

WHERE Format([flddate],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format([fldate2],"mm/yyyy")=Format(Date(),"mm/yyyy") etc etc;

Brian;

 

[Mile-O]

Theres going to be 8 date fields, all of which need to be in the query, and i want it to produce a report if any of the dates match mm/yyyy.

What are these date fields for?

 

[Shepps]

where would that go in thew SQL?

i have

SELECT [Staff Details].[Staff Name], [Staff Details].[Staff Grade], [Mandatory Lectures].[CPR retrain date], [Mandatory Lectures].[Fire lectures retrain date], [Mandatory Lectures].[Manual Handling retrain date], [Mandatory Lectures].[Blood Protocol retrain date]
FROM [Mandatory Lectures] INNER JOIN [Staff Details] ON [Mandatory Lectures].[Mandatory ID]=[Staff Details].[Madatory ID];

Atm.. and i was trying to add...

WHERE Format([CPR retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Fire lectures retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Manual Handling retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Blood Protocol retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy")

 

[Brianwarnock]

where would that go in thew SQL?

i have

SELECT [Staff Details].[Staff Name], [Staff Details].[Staff Grade], [Mandatory Lectures].[CPR retrain date], [Mandatory Lectures].[Fire lectures retrain date], [Mandatory Lectures].[Manual Handling retrain date], [Mandatory Lectures].[Blood Protocol retrain date]
FROM [Mandatory Lectures] INNER JOIN [Staff Details] ON [Mandatory Lectures].[Mandatory ID]=[Staff Details].[Madatory ID];

Atm.. and i was trying to add...

WHERE Format([CPR retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Fire lectures retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Manual Handling retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Blood Protocol retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy")

There are syntax errors in the above but I assume that you retyped it onto the post, so what happened when you added the Where clause?

You did delete the ; from the end of the FROM clause and put it at the end of the WHERE?

Brian

 

[Shepps]

i get a syntax error, i've attatched a screen dump of it.

edit : this is the SQL i have

SELECT [Staff Details].[Staff Name], [Staff Details].[Staff Grade], [Mandatory Lectures].[CPR retrain date], [Mandatory Lectures].[Fire lectures retrain date], [Mandatory Lectures].[Manual Handling retrain date], [Mandatory Lectures].[Blood Protocol retrain date]
FROM [Mandatory Lectures] INNER JOIN [Staff Details] ON [Mandatory Lectures].[Mandatory ID]=[Staff Details].[Madatory ID]
WHERE Format([CPR retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Fire lectures retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Manual Handling retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy") OR Format(Blood Protocol retrain date],"mm/yyyy")=Format(Date(),"mm/yyyy");

 

[Brianwarnock]

there are lots [ missing around field names, correct these and try again

Brian

 

[Shepps]

Works a treat, cheers mate

 

[Brianwarnock]

Works a treat, cheers mate

Happy to help

Can I suggest that in future you avoid having blanks in field names, infact any Access object names as they lead to alsorts of problems, if your fields had not had blanks I think Access would have added the[], atleast you may have got amore meaningful error message

Cheers
Brian