Extracting data from query

[Gamezy]

I have already asked for help on this matter once before, but I'm trying to do something slightly different this time. Bare with me...

Basically, my query so far has 5 check boxes, for each working day of the week. I have a working function (DateDiffExclude - thank you everyone that helped so far! I cleaned up my code now, don't know what I was thinking ), but I'd like it to check the tickboxes in a query before calculating a value for the available days someone can work.

Example of the VB code currently used (doesn't work, not sure on syntax)

If [Query]![qryMain]![M].Value = False Then
string = string & "2"
End If

The idea being it will append to the string if the checkbox is unticked.
Currently the function works excellently, but it doesn't take into account the days available. I can get it working within a Form ([Forms]![frmMain]![M], etc), but this isn't really how I'd like it to work.

Again, any help would be great.

Also, would the "criteria" section of the query be what I'm looking for? Could that dynamically update depending on what boxes are ticked? or is it only for user input?

 

[KeithG]

You need to open up a recordset with the sql statement of the query. Then you can refer to the fields in the recordset.

 

[Gamezy]

Could you please demonstrate an example of this? I'm still at noobie level with VBA Code (the lack of a decent API is a bitch!)

 

[KeithG]

Sure, can you post the SQL statement from the query?

 

[Gamezy]

SELECT tblSicknessData.EntryID, tblSicknessData.[Payroll ID], tblSicknessData.[User ID], tblSicknessData.[Daily Hours], tblSicknessData.M, tblSicknessData.Tu, tblSicknessData.W, tblSicknessData.Th, tblSicknessData.F, -([M]+[Tu]+[W]+[Th]+[F]) AS [Days Per Week], tblSicknessData.Location, tblSicknessData.[Start Date], tblSicknessData.[End Date], IIf(IsNull([Start Date])=True Or [Start Date]>Date(),0,(DateDiffExclude([Start Date],IIf(IsNull([End Date])=True,Date(),[End Date])))) AS [Available Days], tblSicknessData.[Total Sick Days]
FROM tblUser RIGHT JOIN (tblBuilding RIGHT JOIN tblSicknessData ON tblBuilding.BuildingID = tblSicknessData.Location) ON tblUser.UserID = tblSicknessData.[User ID];

I think this is what your after. My SQL knowledge is about as good as my VBA so I've avoided even touching the stuff.

Note: The red bit is where I am trying to calculate Available Days using my DateDiffExclude function, this is where I need to find if the tickboxes are checked so I can use them to create a string which can then be passed into DateDiffExclude (currently this bit has been removed while I work on it).