Bed days query help

[rodmc]

Hi folks been trying to get my head round this one for a while, and I seem to have hit a brick wall. Im looking to get the number of bed days (overnight stays) from my database. Ive used a query I found on here to get the number of nights based on a start date and end date.

SELECT tblEpsiodes.EPID, tblEpsiodes.ENQSTART, tblEpsiodes.ENQEND, Month([ENQSTART]) AS MontOfENQSTART, Month([ENQEND]) AS MontOfENQEND, DateSerial(Year([ENQEND]),Month([ENQEND]),1) AS 1stDateOfEndMonth, IIf([MontOfENQSTART]=[MontOfENQEND],DateDiff("d",[ENQSTART],[ENQEND]),(DateDiff("d",[ENQSTART],[1stDateOfEndMonth])-1)) AS NoOffDaysForStartMonth, IIf([MontOfENQSTART]=[MontOfENQEND],0,DateDiff("d",[1stDateOfEndMonth],[ENQEND])+1) AS NoOffDaysForEndMonth
FROM tblEpsiodes
WHERE (((tblEpsiodes.ENQSTART)>=[Forms]![frmCountDays]![StDte] And (tblEpsiodes.ENQSTART)<=[Forms]![frmCountDays]![EndDte]) AND ((tblEpsiodes.CANCELLED)=False)) OR (((tblEpsiodes.ENQEND)>=[Forms]![frmCountDays]![StDte] And (tblEpsiodes.ENQEND)<=[Forms]![frmCountDays]![EndDte]));

StDte and EndDte are the text fields that are the parameters for the query. The results of the query are in the attached image. it gives me 2 columns "NoOffDaysForStartMonth" and "NoOffDaysForEndMonth". What I need to do is only give the number of days for say November (month 11), was thinking an additional column with a nested IIf, but the more I think about it the more I think Im looking in the wrong direction. Cant see the woods for the trees you might say. HELP!

PS, I know Ive spelled Episodes worng

 

[rodmc]

Solved it, for anyone else with the same problem, check the DaysByMonth example here (sample db by khawar), just switch his form1 date fields with the fields from your query/table (start/end date) and bingo your done!