Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    Unanswered: sql as controlsource for textbox

    I have a syntax problem inthe following:

    Me.Text16.ControlSource = "SELECT Count(referrals.Name) FROM referrals WHERE ((referrals.Concern)='&DSTLA&'));"

    the return should be a number calculated by counting the number of records based on the surname field. from the referrals table where the field concern=DSTLA.

    on form me text16 is a textbox.
    I am using VBA to update the textbox after a command button is pressed (at present this is a testcase scenario as I am trying to fix the syntax).

    the textbox currently returns ?Name, rather than the number, and yet the code compiles ok.


    I intend to build the sql with further criteria, so would not like to use
    me.text16=dcount[field1,source,criteria] etc

    many thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi rodmead

    As you have suggested using DCount, what is wrong with this

    Me.Text16.ControlSource = DCount("Name","referrals","Concern= '" & DSTLA & "'")

    ??

    You can add many criteria to the 'Criteria' part, provided thay all refer to field in the same table (or stored query).

    I don't think the control source for a Textbox can be a select statement or its resulting recordset (even if it is only one record with one field). Is that correct ?


    MTB

    p.s. looking at yout post again I am not sure if you don't mean this

    Me.Text16.ControlSource = DCount("Name","referrals","Concern= 'DSTLA')

    depents wether DSTLA is a variable in the code or a value in the table!?

  3. #3
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53
    ok this works, when placed in the controlsource field in the properties menu:
    =DCount("Name","referrals","Concern= 'DSTLA' ")

    Now how do I use multiple criteria? for example when the concern is DSTLA and Sex=M. Am I to use semi-colon separators and then where do the single and double quotes go?

    Cheers

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    =DCount("Name","referrals","Concern= 'DSTLA' AND Sex = 'M'")
    Paul

Posting Permissions

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