var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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
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 ?
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!?
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?
=DCount("Name","referrals","Concern= 'DSTLA' AND Sex = 'M'")