Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup in a Form textbox (2k)

    I have:
    tblBUDGET with Fields: JobID and FTE
    qryJobTitleBasePay with fields: JobID and JobBasePay

    frmBUDGET RecordSource = tblBUDGET continuous form with fields: JobID FTE + unbound textbox: txtBASEPAY

    I want the unbound txtBASEPAY to display the results of a lookup ( the JobBasePay from the qry based on the Job ID) for each record and multiply the base times the FTE.

    I tried the following in the control source of the txtBASEPAY but am getting an error:

    =DLookup("JobBasePay","qryJobTitleBasePay",'[qryJobTitleBasePay]![JobID]=[JobID]')*[FTE]

    Any suggestions?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DLookup in a Form textbox (2k)

    If JobID is numeric, recommend try this syntax:

    <pre>=DLookup("JobBasePay","qryJobTitleBasePay", "[qryJobTitleBasePay]![JobID]=" & [JobID])*[FTE]</pre>


    If JobID is text field, try this:

    <pre>=DLookup("JobBasePay","qryJobTitleBasePay", "[qryJobTitleBasePay]![JobID]='"&[JobID]&"'")*[FTE]</pre>


    The last argument for DLookup is a text string so you need to enclose it in double-quotes, if criteria is text enclose in single quotes. In either case JobID is a variable, I assume the form provides its value, syntax reflects this.

    HTH

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup in a Form textbox (2k)

    Thanks for the quick reply. I have tried both the following syntax (both causing a "syntax error msg."
    JobBasePay is an "expression" field in my query and JobID is a numeric from the table underlying the qry.



    =DLookup("JobBasePay","qryJOB_TITLE_BASE","[qryJOB_TITLE_BASE]![JobID]="&[JobID])*[FTE]

    and

    =DLookup("JobBasePay","qryJOB_TITLE_BASE","[qryJOB_TITLE_BASE]![JobID]='"[JobID]&"'")*[FTE]

    (I tried this "text - variable" syntax just to make sure, but to no avail.)

    It appears that if I were doing a lookup in a TABLE rather than a QUERY, the correct criteria syntax would be:

    " Forms ![tblJOB_TITLE_BASE]![JobID]="&[JobID]" starting with the word Forms! . Is there an equivalent Query!

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DLookup in a Form textbox (2k)

    The syntax in first example appears correct if JobID is numeric field. The name of query (qryJOB_TITLE_BASE) seems to have changed since previous example. So where does the JobID used as criteria in 3rd argument of DLookup function come from? If JobBasePay is an expression in query (ie, calculated field) and its value is determined by JobID, and JobID is a field in form's RecordSource, why not perform the calculation on form directly? Dlookup function applies to both tables & queries. As noted in help, the 2nd argument, domain, is defined as "A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name." Maybe providing the SQL statement used for qryJOB_TITLE_BASE query will assist in resolving issue.

    HTH

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup in a Form textbox (2k)

    Mark

    Let's see if the following table will help with an explanation: The end goal is to display on the Dept Form a LABOR BUDGET Subform. The subform will provide entry/viewing controls for each JobID and FTE based on the tblLABOR_BUDGET then lookup and display the cost of wages and benefits for each JobID in the Dept.
    These lookup controls are what is missing and need the DLookup formulas. From everything I have read, the formulas you provided SHOULD work, but Im getting a "syntax error."

    ???????????????

    <table border=1><tr><td valign=bottom>TABLES</td><td valign=bottom>tblJOB_TITLES</td><td valign=bottom>tblEMPLOYEES</td><td valign=bottom>tblLABOR_BUDGETS</td><td align=right valign=bottom>

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup in a Form textbox (2k)

    And here is the SQL statement:

    SELECT qryEMPL_BASE_PAY.JobID, Avg([MoBaseTotal]*12) AS JobBase
    FROM qryEMPL_BASE_PAY
    GROUP BY qryEMPL_BASE_PAY.JobID
    ORDER BY qryEMPL_BASE_PAY.JobID;

    thanks

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DLookup in a Form textbox (2k)

    Post your zipped database and we can hopefully see what you are doing.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup in a Form textbox (2k)

    In your DLookup expressions, you are trying to look up a field named "JobBasePay", but according to the SQL you posted, the field names are "JobID" and "JobBase". So "JobBasePay" will not be recognized.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DLookup in a Form textbox (2k)

    As HansV noted, the query SQL uses JobBase as alias for calculated field, not JobBasePay, so that may be causing problem. Recommend change DLookup expression to reflect correct name for the JobBase field.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •