Solved Filtering records on a form by month selected from a combobox on another form (1 Viewer)

[Sam Summers]

Hi everyone i have been chasing this problem around but still not really there with it.

I have a form with a combobox (cboMonth) with the RowSource -

01;January;02;February;03;March;04;April;05;May;06;June;07;July;08;August;09;September;10;October;11;November;12;December

On the AfterUpdate event opens the form (ViewByMonth).

The Record Source of the (ViewByMonth) form is:

SELECT Casualty.CategoryID, Casualty.DateOfIncident, Casualty.[Name(Optional)], Casualty.TradeID, Casualty.InjuryTypeID, Casualty.IngestInhaleID, Casualty.Position1, Casualty.Position2, Casualty.TypeID, Casualty.Position3, Format([Casualty].[DateOfIncident],"mm") AS [DATE]
FROM Casualty;

So what i am trying to do is filter the records using (DateOfIncident) to only show records of the selected Month.

I have tried various ways and functions but not got it yet.

Many thanks in advance

 

[Minty]

Something like

WHERE Datepart("m",Casualty.DateOfIncident) = Forms!YourForm!cboMonth

Should work.

Just as an aside [Name(Optional)] is probably the worst field name I have seen in years.
Name and Optional are both reserved words and using ( ) in a field name is asking for problems.

 

[Sam Summers]

Something like

WHERE Datepart("m",Casualty.DateOfIncident) = Forms!YourForm!cboMonth

Should work.

Just as an aside [Name(Optional)] is probably the worst field name I have seen in years.
Name and Optional are both reserved words and using ( ) in a field name is asking for problems.

Hi Minty,

Point taken. I don't know how i did that unless i meant to put that in the Caption? Anyway i have changed that now.

I eventually got it working with this:

SELECT Casualty.CategoryID, Casualty.DateOfIncident, Casualty.CasualtyName, Casualty.TradeID, Casualty.InjuryTypeID, Casualty.IngestInhaleID, Casualty.Position1, Casualty.Position2, Casualty.TypeID, Casualty.Position3, Format([Casualty].[DateOfIncident],"mm") AS [DATE]
FROM Casualty
WHERE (((Format([Casualty].[DateOfIncident],"mm"))=[Forms]![MainForm]![cboMonth]));

Thank you for your help