dcount if has required else and within area

[Ruegen]

I have a form unbound textbox which with

=DCount("NewSchoolsID","tblSchools","AreaID=" & [AreasID])

gives me a count of the schools within the area

however

I would like a count of the schools in the area that have an enrollment over 100 (from [Enrollment] field)

I thought of having an IF so that IF it didn't count it would show 0

not sure how to do this with a dcount - all I get are errors.

I thought something like this would work

=DCount("NewSchoolsID","tblSchools","AreaID=" & [AreasID] And "Enrollment">100)



This doesn't work for me either

=DCount("NewSchoolsID","tblSchools","Enrollment >=100" And "AreaID=" & [AreasID])

This will count all the records above 100

=DCount("NewSchoolsID","tblSchools","Enrollment >=100")

but I also need to narrow it down by area at the same time

[ItsMe]

Is there supposed to be an s in [AreasID])

The other thing is your Dcount is going to return a number. Do you have something to store it in?
Maybe try this.

Dim lngID As Long
lngID = Me.AreaID
Dim lngCount As Long
lngCount = DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & lngAreaID)
msgBox lngCount

[Ruegen]

I used this

=DCount("NewSchoolsID","tblSchools","Enrollment >=100" & "And AreaID=" & [AreasID])

not sure if it is correct though if someone could let me know...

[Ruegen]

Is there supposed to be an s in [AreasID])

The other thing is your Dcount is going to return a number. Do you have something to store it in?
Maybe try this.

Dim lngID As Long
lngID = Me.AreaID
Dim lngCount As Long
lngCount = DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & lngAreaID)
msgBox lngCount

I didn't get a chance to respond before posting again, it doesn't need to store - only display.

[ItsMe]

Well does the code I offer give you a message box? That it the first step to make sure it works. Then you can edit the code to assign the result to wherever, textbox, data field.

[ipisors]

I don't see any absolute necessity to have a variable here, you can certainly use Dcount() as an expression in a textbox's control source.

Try:

=Iif(DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID])>100, DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID]),0)

However, I do agree that in these situations you ought to code VBA, not use an expression. Because for starters, what I posted makes 2 trips to the data, this would be better - on the Form Current event, or maybe load event, depending on the usage of the form.

Dim lngCount as Long
lngCount=DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID])
If lngCount>100 then
   Me.Controlname.Value=lngCount
else
  Me.Controlname.Value=0
End If

[ItsMe]

I don't see any absolute necessity to have a variable here, you can certainly use Dcount() as an expression in a textbox's control source

I actually missed or forgot the part where the unbound textbox was mentioned. I was reading the other part of the OP's post and started believing he was in the VBE. I posted the extra lines of code so the OP would have a way to step through it. Your example is more complete.

[Ruegen]

I actually missed or forgot the part where the unbound textbox was mentioned. I was reading the other part of the OP's post and started believing he was in the VBE. I posted the extra lines of code so the OP would have a way to step through it. Your example is more complete.

Cheers thanks for the response etc

[Ruegen]

I don't see any absolute necessity to have a variable here, you can certainly use Dcount() as an expression in a textbox's control source.

Try:

=Iif(DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID])>100, DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID]),0)

However, I do agree that in these situations you ought to code VBA, not use an expression. Because for starters, what I posted makes 2 trips to the data, this would be better - on the Form Current event, or maybe load event, depending on the usage of the form.

Dim lngCount as Long
lngCount=DCount([NewSchoolsID], "tblSchools", "[AreaID=]" & [AreasID])
If lngCount>100 then
   Me.Controlname.Value=lngCount
else
  Me.Controlname.Value=0
End If

But would this work on a continuous form? I would have to make it step through each record to work...?

[ItsMe]

The current event is only going to fire when you click on the record by selecting it or a control within the form.

Sounds like your up against running a report to view the summary in the format you want.

[Ruegen]

The current event is only going to fire when you click on the record by selecting it or a control within the form.

Sounds like your up against running a report to view the summary in the format you want.

Yeah that is why I made it an expression as apposed to using VBA. No data is needed to be kept. When the person opens the form they need only see what is current for that area to assess which staff member should be allocated to what area. So if the area displays many schools in number (these are the schools that are over 100 enrolled) then the manager can allocate who thinks would be best suited to handle that area.

There isn't much point in storing the value because I won't be doing anything with it. Since it's a continuous form and I need all the areas to display their count as soon as the form is opened I have used an expression. The vba I am guessing which would give the same result won't execute for all records until that record is selected (even on a continuous form).

I'm pretty much going to say this is solved. I'm getting the results I need etc.

Thanks for the help!