run a sum query on an id in a combobox

[gbmarlysis]

Hi
i have an athlete db


i have a form with a combo box
i want a cell to tell me how many races an athlete has had once i pick the athlete in the combo box
there is also a cell with the date in it
i want the "how many races cell" to only show the number of races the athlete competed in before the date in the "date cell"
thanks

[June7]

Could use DCount in textbox ControlSource.

=DCount("RaceID", "Races", "AthleteID=" & Me.comboboxname & " AND " & "RaceDate<#" & Me.datetextbox & "#")

[gbmarlysis]

cant get it to work june. comes up with 'name?, the combobox is unbound and its a subform im working with is this the issue

[June7]

Show the exact expression you used. I assume you use your fields and control names. Are the combobox, ID, and RaceDate all on the subform?

[gbmarlysis]

=dcount("*", "Race Results", "Athlete Id" = & [Me].[Combo35]) and a million other variations the only thing on the subform is the athlete id combo box. the main use of the subform is to put in all the athletes on the form with there results for the day etc. then i'll macro each one into the race results form. but i want to show how many races they have run in up to that date
eg
ath 1(combobox) 5 races so far today came 1st time
ath 2 2 races so far today came 4th time
ath 3 12 races so far today came 6th time

where races so far is the text box i want to populate when the athletes name is selected from the combo box

[June7]

Race date is on main form? You actually have space in the Athlete ID field and Race Results table names? Advise to avoid using spaces and special characters in any names. If you do, must remember to enclose in []. Athlete ID is a number datatype field?

=DCount("[Athlete ID]", "[Race Results]", "[Athlete Id]=" & Me.Combo35 & " AND RaceDate<#" & Forms!mainformname.datetextboxname & "#")

or if textbox with this expression is on main form:

=DCount("[Athlete ID]", "[Race Results]", "[Athlete Id]=" & Me.subformcontainername.Form.Combo35 & " AND RaceDate<#" & Me.datetextboxname & "#")

I always give subform containers a name different from the object they hold, like: ctrResults.

[gbmarlysis]

=DCount("[Athlete ID]", "[Race Results]", "[Athlete Id]=" & Me.Combo35) Didnt work June even tried to shorten it as shown to take out the date aspect and no luck with it working. Changed the expression to "*", didnt work,, took out criteria and finally got a value but its just counting the number of all records....hmmmmm. seems like the criteria might b it. any other ideas.. the textbox is in the sub form also..

[June7]

Textbox and combobox are on subform? Is Athlete ID a text or number data type? If it is text need apostrophe delimiters.

=DCount("[Athlete ID]", "[Race Results]", "[Athlete Id]='" & Me.Combo35 & "'")

You want to provide project for analysis?

[gbmarlysis]

athlete id is text june sorry what are the apostrophe delimiters

[June7]

They define the value as a text string. I show the syntax in previous post. Dates require # and numbers nothing.

[gbmarlysis]

Still no luck June still comes up with NAME? in text box..i tried just as you typed out..hhmmmm

[gbmarlysis]

its something to do with the criteria, when i remove it it gives me the value of entries in the domain but as soon as i try to add criteria no luck

[gbmarlysis]

ok June got it..... this is it...=DCount("[Athlete Id]","[Race Results]","[Athlete Id]='" & [Combo35] & "'").. now if i could get your advice on how to add on the part about only count the records that are before a certain date in a text box on the main form..
thanks

[June7]

I showed example of that in post 6.

[gbmarlysis]

the date isnt its own field. it is used as the race primary id eg lathl15/06/11r1 can i use wildcards in dcount? how would this look.
(the reason i did this is when i want to see a race result i know the date and race no and who hosted the meet so i can put that in a combo box to recieve the results)