Updating query

[Gamezy]

In the attached databse, there is an Available Days function in qryMain. I'd like it so that when a day of the week tickbox is checked, to update the query. However I can't seem to get the Available Days function to access information from the query. I've had this working using a form previously, but can't seem to get it working with a query. I know my code is messy, still figuring out access, and VBA stuff.

I appreciate I also posted about this a while back, and that was a big help. Since then though, I've had to rebuild the database, and this is the best version so far. Would love to get it working like I imagine in my head!

I'd also like to get the frmUserList page working so that when Open User button is selected, it will open the users page, but give access to all user so you can switch through them. There doesn't seem to be a wizard way of doing this, do I need to do more VBA code?

 

[TedMartin]

Thats some fantastic calculation in the Available Days field. I wouldn't know where to start. It needs to be simplified into smaller calcultaions that progressively lead from one to the other. Hopefully you will then be able to pick the result and deduct the Days per Week field without messing up the overall calculation. Hope this helps the thought process.

 

[Gamezy]

In all fairness I think I may have deleted the original persons tag on those lines of code. I did not write that, and yes, who ever did write it does deserve credit (I shall sort that now).

Update: By using the Acess Query builder

Available Days: IIf(IsNull([Start Date])=True Or [Start Date]>Date() Or [End Date]<[Start Date],0,(DateDiffExclude([Start Date],IIf(IsNull([End Date])=True,Date(),[End Date]),("1" & IIf([M]=-1,"","2") & IIf([Tu]=-1,"","3") & IIf([W]=-1,"","4") & IIf([Th]=-1,"","5") & IIf([F]=-1,"","6") & "7"))))

I've gotten the "available days" to update as the checkboxes on the query/form are selected. It is accurate and gives the feedback I wanted. (Ammount of days someone can work given the start date, end date, and the days they will be working). I finally realised that using a custom function to get information form a table was pointless, and ended up finding the wonder of the & sign. Can't believe it's taken me so long to do this! (literally weeks).

Anyways, can anyone help with the rest of my post?

I'd also like to get the frmUserList page working so that when Open User button is selected, it will open the users page, but give access to all user so you can switch through them. There doesn't seem to be a wizard way of doing this, do I need to do more VBA code?