Dcount and form not updating.

[abodi]

Hi everyone.



I have 2 questions today. I'm making some gains on my project and want to thank everyone for their help.

Question 1:
In the screen shot below, I have a box highlighted that displays the total number of leave in the requested with the appoved status. I've achieved this using Dcount, however I need to ensure it's only counting the leave for the day listed above in [vdate1].

PBaldy provided an answer i used previously (http://www.baldyweb.com/OverLap.htm), however i'm not sure it can be combined with dcount. so my question is how would I achieve such a thing.

Essentially I need to have something that does the same thing to "WHERE OffStartDate <= Forms!FormName.txtEndDate And OffEndDate >= Forms!FormName.txtStartDate" for dcount.



Question 2:
In the same screen shot above you'll see the number "5"in the "Quota" text Box. This figure is generated using Dlookup and also based on the date shown[vDate1] which in turn is generated from the dropdown box at the top of the screen.

The issue is the figure does not update. Currently the form opens with the combobox blank, I can choose a date and all the date boxes below populate, but not the Quota box. Currently the only reason i know it works is because if I go to design view and back to form view, it decides to update. Thoughts?

[June7]

Domain aggregate functions have a criteria argument. It follows the same rules as sql WHERE clause.

=DCount("*", "tblLeaveRequests", "OffStartDate BETWEEN #" & Forms!FormName.txStartDate & "# And #" & Forms!FormName.txtEndDate & "#")

[abodi]

# means literal date yes?

Currently my code is

=DCount("*","[tblLeaveRequests]","Status='Approved'")

where the first criteria is in bold is it a matter of using AND and then the between criteria?

June7 I don't think the above between code will work as the leave is captured as a startdate and an end date.
hence i think i need to append

WHERE (((tblLeaveRequests.LeaveStartDate)<=[vDate1]) AND ((tblLeaveRequests.LeaveEndDate)>=[vDate1]));

to the end of it. My guess is something like

=DCount("*","[tblLeaveRequests]","Status='Approved'" And WHERE (([tblLeaveRequests].[LeaveStartDate])<=[vDate1]) AND ((tblLeaveRequests.LeaveEndDate)>=[vDate1]));

However these is some expression error there.

[June7]

The WHERE word is not used in domain aggregate.

"Status='Approved' AND #" & [vDate1] & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")

[abodi]

Didn't work my me June7, although i expect that it should have.

I've attached the database, if you or anyone could have a look it would most appreciated.

[June7]

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Bus_Avail_Pct and Qty_Individual.

What is purpose of the form? It is not bound to data.

DCount expression has misplaced quote marks, shown in red, remove them:

=DCount("*","[tblLeaveRequests]","Status='Approved'" And "#" & [vDate1] & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")

Change default value of combobox to (do not use = sign):
CDate([cboLAStartDate].[ItemData](0))

tblSiteNumbers is not normalized structure.

[abodi]

I pulled out the other forms. that are actually bound to data, as that arent important to the issue i'm having.

The point of this form is so someone can look at a 4 week period and see on what days leave is available. and if there are others that have leave in waiting.

Thanks June7, I'll try your suggested changes.

[abodi]

Can someone explain to me why the boxes update only if specific combo box is updated.

Quota only updates when the product combox is updated (despite having the date as a search criteria.

Approved and Waiting use the same formula (other than the criteria), and only update with the date is updated. (despite also having location and product as search criteria).

Available is just [quota]-[approved] and as such updates depending on the field changed.



i'm missing some sort of basic understand about when things poll for new information. is it possible to jsut have the text boxes constantly checking?

in case it matters here is the code, but perhaps it's a form thing (although i looked and couldn't find a setting.)

Quota

=Round(DLookUp([cboLAProduct],"[tblLeaveAvailablility]","[vDate1]=[tblLeaveAvailablility]![Scheduling_Date]")*(DLookUp("[Individual]","[tblSiteNumbers]","'UMG'=[tblSiteNumbers]![Site]"))/100)

Approved/Waiting

=DCount("*","[qryLeaveAvailability]","Status='Approved' And PrimaryProduct=[cboLAProduct] And location=[cbosite] And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")

[June7]

Want to provide latest version of db for analysis?

[abodi]

yeah sure.

[abodi]

Any thoughts?

[June7]

DLookup syntax is wrong. Try:

=Round(DLookUp([cboLAProduct],"[tblLeaveAvailablility]","Scheduling_Date=#" & [vDate1] & "#") * DLookUp([cboLAProduct],"[tblSiteNumbers]", "Site='" & [cboSite] & "'") / 100)
=Round(DLookUp([cboLAProduct],"[tblLeaveAvailablility]","Scheduling_Date=#" & [vDate2] & "#") * DLookUp([cboLAProduct],"[tblSiteNumbers]", "Site='" & [cboSite] & "'") / 100)
etc

However, choice of 'Superannuation' errors because the field in tblSiteNumbers is named Super.

Be aware that Round function in Access uses even/odd rule.

Round(8.5) = 8

Round(9.5) = 10

[abodi]

thanks June7 i'll give that a try.

I am aware of the super issue, i just hadn't renamed it yet.

Wow, what general purpose would that odd/even rule play? Seems crazy.

[June7]

It's also called Banker's rounding. Search Wiki if you really want to know about different rounding concepts. I guess MS chose it because Access is considered a business tool, not a scientific/statistical analysis application. Note that Round in Excel does not use this rule.

Also, Format() function does not use even/odd but the result is a string value, not a true number.

[abodi]

unfortunately the code you provided only fixes week1, as all of week one uses [vdate1] however other weeks use the vdate at the start of their weeks.
and as vdate6 is actually [vdate1]+7 it doesn't work.

So i tried telling it not to look for a specific date using # # ('m using international dates)

=Round(DLookUp([cboLAProduct],"[tblLeaveAvailablility]","Scheduling_Date=[vDate1]") * DLookUp([cboLAProduct],"[tblSiteNumbers]", "Site='" & [cboSite] & "'") / 100)

the numbers are back but it's not updating correctly again, no so good.

Is there another way to do the dates at the top to work with the code provided? or an easy fix to the code so it'll work with that other vDates