Help needed with coding a string

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

0
Utf
4/2/2007 11:16:08 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
449 Views

Similar Articles

[PageSpeed] 35

=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.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"JWCrosby" <JWCrosby@discussions.microsoft.com> wrote in message 
news:86B66266-FD17-40FC-A6DD-4E67EC98CB92@microsoft.com...
> 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
> 


0
Douglas
4/3/2007 12:38:55 AM
JWCrosby wrote:

>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)

-- 
Marsh
MVP [MS Access]
0
Marshall
4/3/2007 12:39:28 AM
Reply:

Similar Artilces:

Help for ICT Gcse c/w
Hi Basically I am making a model for my ICT coursework (AQA), it's about dog training courses for a vets surgery. For part of it, we have to work out the minimum number of dogs needed for a course to run (has to be less than �50). I have made a table, which links to other worksheets.. No. of dogs: Cost per course: Cost per dog: Can course run? 1 �348.35 �348.35 No 2 �350.60 �175.30 No 3 �352.85 �117.62 No 4 �355.10 �88.78 No 5 �357.35 �71.47 No 6 �359.60 �59.93 No 7 �361.85 �51.69 No 8 �364.10 �45.51 Yes 9 �366.35 �40.71 Yes ...

Need to group elements using XSLT
Hi, I am trying to group nodes in a huge xml file by one of the common fields. I have tried using "for-each-group" and "group-by" but am unable to get it working. Here, I have a list of Ports under each DSLAM entry and would hence like to group in a hierarchy all such Ports under each of these DSLAM entries as shown below. Here's a sample xml file (similar to the one I am trying to transform, but simplified to remove a lot of junk fields) and the required xml output - ----Sample XML file----- <?xml version="1.0" encoding="UTF-8"?> <Conv...

Help! ESEUTIL /r (3 character logfile base name)
Who knows the (3 character logfile base name) mean? Thank you so much! "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 ...

First attempt at VBA coding problem
I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),&...

Divide by 0 Help
=(SUM(E98:F98)+D98/2)/(I98-G98) Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 Error in any of the calculations. I am a bit of a novice. Check your earlier post. On 05/17/2010 06:48, joey_boy wrote: > =(SUM(E98:F98)+D98/2)/(I98-G98) > > Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 > Error in any of the calculations. I am a bit of a novice. joey_boy wrote: >=(SUM(E98:F98)+D98/2)/(I98-G98) > >Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 >Error in any of the c...

Need to dynamically re-center a form
Hi everyone, I have an Access form which I'm dynamically sizing based on the number of entries in a list box. Simple code to dynamically re-center it doesn't quite seem to be working as I would expect (AutoCenter is on, then simply move form up by half the difference of height change). I realize that algorithm won't be perfect, due to things like title bar height, but I would've thought it would be close enough. Instead, with large numbers of entries in the list box, my form goes WAY above the top of the Access "work area" (i.e., the usable display area aft...

Leading zero in zip code won't print
I just tried to print a Christmas list many of whom live in N.J. which has many zip codes that start with a zero. They don't print. This is with Office 2003. That has to be a fix for this. Any help please?? Thanks Are you using excel as your database and using mailmerge in MSWord? Debra Dalgleish posted this to a similar question: There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx And if you prefer the old Mail Merge helper, Word MVP Suzanne B...

I just got office 2007 help!
After downloading both steps on MS site. I was upgraded for each utility.....ie excel word etc. except outlook it still remains 2003 why? Everything else went to 2007 "Rob" <Rob@discussions.microsoft.com> wrote in message news:9C6D0578-01E5-4E49-BCC1-A1B2B3A1AD69@microsoft.com... > After downloading both steps on MS site. I was upgraded for each > utility.....ie excel word etc. except outlook it still remains 2003 why? > Everything else went to 2007 You need to uninstall Outlook 2003 first...... Because you must first uninstall Outlook 2003 before you install ...

Excel Date
want to be able to have a cell auto fill the first time the sheet i saved so i have the creation date. Then if poss - never let them chang the cell so it keeps record of when the file was first created. Thanks - any help will get some free chocolate! ;-) Arie Astor Chocolate Corp. www.astorchocolate.co -- Message posted from http://www.ExcelForum.com Try some code in the workbook module ; e.g. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Range("Sheet1!A1") = "" Then Range("Sheet1!A1") = Date End If End Sub Then set Sheet1...

SMTP connector, do i really need one?
i only have 1 exchange server in my organization. why do i need an smtp connector? wouldnt the default virtual smtp server be enough to handle everything? as it stands now, the internet mail wizard created the default smtp connector, and set it to forward all mail to itself basically. i dont think the server needs to act as a bridgehead server for itself, am i right? im getting frequent errors in my logs relating to smtp and dns... however both my smtp and dns setups seem to be working fine. can i get rid of the smtp connector? The SMTP Virtual Server acts as an SMTP connector so you do not...

Code to Shade a Field if Criteria Met
Can I do this on my subform without using Code on an event? I am not quite ready to do the code on my own yet. This is my statement in english If the ThpyTypeID_fk is equal to 88, 81,70,73,74,43 (these are my AutoNumberIDs of the therapy types that could be entered)......Make the backcolor to the EquipID_fk field light yelllow The tricky part to this is both of these are combo boxes. For the ThpyTypeID_fk, My column count is 2 and the bound column is 1 which is the ID field in my tblTherapyType. For the EquipID_fk field, the bound column is 3 which is the EquipID in my Equipment T...

Loading Speed of Form
Hi all, I have a question regarding the loading speed of forms. I have about 6 forms in my database, each is loaded with lot of controls and VBA driven action that calls numerous queries. So as expected, they do take a while to load, 10 - 30 seconds. For some remote desktop users, it usually take even longer. These forms, however, do not have any onLoad. procedures. All the code are in click of buttons of afterUpdate of dropdown menu and others. The interesting thing is, these form load much much faster (3 secs) when one form opens the next, instead of manually open it from the database wind...

Formatting Odd Number Strings
I have imported a text report into Excel. For some reason the date string on the text file reads as 20/05/0728. Is there a quick way to reformat the string so it reads 7/28/2005 or even 2005/07/28, for that matter. I really don't want to manually edit 300+ lines! Thanks. Assuming your fubared date is in column A, insert a new column immediately to the right of that, and enter this formula: =DATE(LEFT(A1,2)&MID(A1,4,2),MID(A1,7,2),RIGHT(A1,2)) This parses the existing text string into arguments used by the DATE function, which generates a date useable by Excel. What is the cell...

error code "publisher cannot load files from a different version"
yes a popular error i know. but no i do not have norton basically the computer crashed, i had to reinstall everything, now previously made pub's on office 2k will no longer load on publisher 2k. or on another version of publisher 2k. i looked into the files and they do say publisher 3 embedded in the codes. i am at a loss as to what to do, i have hundreds of files , i havent checked them all of course Do an installer cleanup, Description of the Windows Installer CleanUp Utility http://support.microsoft.com/default.aspx?kbid=290301 -- Mary Sauer MS MVP http://office.microsoft.com/...

HELP!! Formula for calculating overtime
I'm hoping someone can help me. I can't seem to figure out a formula to calculate OT for my staff. Regular hours per day are 7.5. I'm looking to create a formula for banking time which will break out in two columns hours worked over the regular 7.5 hours per day up to 11 hours per day, and in the second column the hours worked over 11 hours per day. Does anybody have any ideas? Thanks so much!! Something like this Hours Bank1 Bank2 6 0 0 7.5 0 0 8.5 1 0 9.5 2 0 10.5 3 0 11.5 3.5 0.5 12.5 3.5 1.5 13.5 3.5 2.5 14.5 3.5 3.5 15.5 3.5 4.5 assum...

HELP -- XP Address Book: No Microsoft Solution Listed
What is the plain and step-by-step simple solution for this very annoying error: "A new entry could not be added. You must have a personal address book (PAB) to create an entry. Add a PAB to your profile using the E-mail accounts command on the Tools Menu." It doesn't allow me to create one!!! Even when I do this: right click on my contacts folder, click on 'Properties', then 'Outlook address book' tab. The option 'show this folder as an email address book' is blanked out and not available for me to check or uncheck. HELP You first. What is it t...

Need to add a line with a value in addition to x & y axis
I am trying to create a chart in Excel as clustered columns with th following data (I have also attached a copy of my file from Exce 2002): Division 1 Division 2 Division 3 Division 4 Division 5 2002 18.60 3.49 5.14 8.52 3.75 2003 14.54 3.53 1.96 10.22 7.29 2004 3.50 1.77 0.00 7.28 3.71 The years are shown in the legend, the category axis (division names are at the bottom, and the value axis is on the side. There is also a industry standard that applies to all values (not by year) and I want t have a line that shows where this value is in relation to the yearl values for each division. I ...

Need help formatting text
I am using OE 6 to read newsgroups and I sometimes post articles that I find on the web, often copying and pasting articles into new messages. However, when I do this, it formats it in such a way that when I open my posts, the paragraphs are separated by at least 3 lines, rather than 1 line. I have tried adding an additional step of pasting articles as unformatted text in word before I paste them in a new message in OE. It seems that no matter what I do, however, I get this weird extra spacing between the paragraphs. Anyone have any idea how to solve this problem? -- Aloha, G-Ride &qu...

Help needed with Circular References
Hi, I am developing a Excel based solution and that requires circular reference in the formulas. However I am ok with setting the limit that each cell be calculated only once. My question is what the order of recalculating cells is. Is it row first starting from A1 or column first starting from A1 or is it the order of entering the cell formulas? Depending upon the order in which the cells are calculated results can change. For example, if the formulas in the cells A1 to A5 are as follows (value in the bracket indicates initial value when formula is entered): A1: A5+1 (1) A2: A1+1 ...

Countif help!!!
Thanks in advance for any help offered. I have a table with GPA's and need to calculate the different ranges. I have the function for 3.0 an above and less than 2.5. I am having trouble figuring out the formul to count how many students are in the range of 2.50-2.99. I thought I could do this: =countif(f2:f406, ">=2.5+<=2.99") I get "0" as a result. any help is appreciated : -- Message posted from http://www.ExcelForum.com Hi, You can use Sumproduct... =SUMPRODUCT(--(F2:F406>=2.5),--(F2:F406<=2.99)) Hope this helps -- Message posted from h...

HELP! #18
...

How can I find the alpha numeric codes in excel please,Thank you.
Can someone please tell me how I can find the alpha numeric codes in excel and also are they english,greek or other ? Thank you. choose font <symbols> and type a you will get alpha character map in system tools will have alphanumeric codes in English =char(65) is A =char(90) is Z =char(97) is a etc "Crazylady_2" <Crazylady_2@discussions.microsoft.com> wrote in message news:5E69866F-FB14-47A4-A662-83087A361B22@microsoft.com... > Can someone please tell me how I can find the alpha numeric codes in excel > and also are they english,greek or other ? Thank you. ...

[ANN] Entourage Help Blog
For News, Views and Clues abut Entourage visit the Entourage Help Blog. We are a group of long-time Entourage users who enjoy sharing our expertise with anyone who wants to make better use of Entourage. We�ve started by doing some reminiscing about Claris Emailer and the revolt on the Emailer Talk. If you have any memories to share, please add your comments. If you are wondering what the heck has Claris Emailer got to do with Entourage, then read about how Entourage got it�s start. <http://blog.entourage.mvps.org/> -- Diane Ross, Microsoft Mac MVP Entourage Help Page <http://www.en...

If cell contatin string like .... copy in Offset mode
Hi , and a good day everybody Please to help me with this problem : I have in a range (f or example : A1:E100 ), in different cells , numbers like in the follow format /(string) like : 12-16-18-25-33-43 ; The other cell contain another values , like : 3 , - , etc . I need a code to loop through my range cell by cell , and , IF the value of cell is in string mode like : 17-28-34-36-39-42 ( can be different numbers , but with the sign - beetwen them ) to copy them in another range , for example G1 , useing Offset function , like : Offset ( 1, 0) . A lot of tha...

Xml error: Namespace Manager or Xslt context needed
I am using VB.Net. My program is to connect to a remote IPAddress. Once, it verifies the login information it should display the SessionID and enable some button . I appreciate your help and thanku in advance When I run the pgm , I get the error: Can't parse login information. Namespace Manager or XsltContext needed. This query has a prefix, variable or userdefined function. I have added the Try-catch in all my functions. In ParseLoginResponse function, it catches the above exception. I am inclding all the functions I am using below: Private Function SendXmlRequest(ByRef xml As St...