Help needed with coding a string

[Guest]

Here’s the situation:

Master Form and Subform. The link fields are in two combo boxes on the
Master form. That part is working, i.e., selecting something different in
either one, or both, of the combo boxes changes the data displayed in the
subform.

In the footer of the subform I have a calculated field, which uses a
function called MedianF (gleaned from another discussion group). If know the
function works properly if I can only send the right instructions to it.

Here’s a statement that DOES work:
=MedianF("tblTest","Missy","Field = 'Canada' AND Year = 2005")

The general format is MedianF(“table where the data isâ€, “field name you are
calculating the median on, criteria of what records to select as a string).

On the master form the control with the year is named cboYear and the
control with the Field is named cboFIeld. The former is a number; the latter
a string.

What I need to say is “calculate the median on the field called Missy from
the table called tblTest where the field is cboField on the parent form and
the year is cboYear on the parent form.

That’s where the problem is. Once I move away from hard coding it as shown
above, totrying to use the variables in the Main form, I get messed up with
the necessary single quotes, double quotes, brackets, etc.

From what I’ve read, when I’m in the subform I should reference the fields
in the parent form using Parent![fieldName} but I’m not doing something
correctly.

I apologize for my murder of this explanation. Can anyone help me write
what I need?

Thanks in advance.

Jerry

 

[Douglas J. Steele]

=MedianF("tblTest","Missy","[Field] = '" & Me.cboField & "' AND [Year] = " &
Me.cboYear)

Field and Year are actually bad choices for field names: both are reserved
words. If you cannot (or will not) rename them, at least use square
brackets, as I've done above.

 

[Marshall Barton]

Here’s the situation:

Master Form and Subform. The link fields are in two combo boxes on the
Master form. That part is working, i.e., selecting something different in
either one, or both, of the combo boxes changes the data displayed in the
subform.

In the footer of the subform I have a calculated field, which uses a
function called MedianF (gleaned from another discussion group). If know the
function works properly if I can only send the right instructions to it.

Here’s a statement that DOES work:
=MedianF("tblTest","Missy","Field = 'Canada' AND Year = 2005")

The general format is MedianF(“table where the data is”, “field name you are
calculating the median on, criteria of what records to select as a string).

On the master form the control with the year is named cboYear and the
control with the Field is named cboFIeld. The former is a number; the latter
a string.

What I need to say is “calculate the median on the field called Missy from
the table called tblTest where the field is cboField on the parent form and
the year is cboYear on the parent form.

That’s where the problem is. Once I move away from hard coding it as shown
above, totrying to use the variables in the Main form, I get messed up with
the necessary single quotes, double quotes, brackets, etc.

From what I’ve read, when I’m in the subform I should reference the fields
in the parent form using Parent![fieldName} but I’m not doing something
correctly.

Try this kind of thing:

=MedianF("tblTest","Missy","Field = '" & Parent.cboField &
"' AND Year = "& Parent.cboYear)