FILL TEXTBOX WITH SQL RESULT

[laman]

How would I use the following statement to fill a textbox on a form with the result. The form is "switchboard" and the textbox is "text1"

SQL = &quot;SELECT Sum(IIf(([cl]<>'y'),1,0)) FROM master;&quot;

Thanks

 

[jhall156]

in the control source of the text box try
=DSum(&quot;[cl]&quot;,&quot;master&quot;,&quot;[cl]<>'y'&quot JHall

 

[laman]

I GET #ERROR in the textbox.

 

[jhall156]

open your form in design view
copy everything to the right of the = in the control source for the text box to the clipboard

open the code module window
in the immediate window type ?<ctl>V this should put a ? plus what you copied to the clipboard. Press ENTER.

What error message do you get.

The reason I'm asking you to do this is because I've used DSum and DLookup hundreds of times and it's usually the criteria that bites you in the behind. The error message you get here should indicate the problem.

Out of curiosity, is the [cl] field a Yes/No field? If so you should try
=DSum(&quot;[cl]&quot;,&quot;master&quot;,&quot;[cl]=0&quot JHall

 

[jebry]

Hi!

You can't use DSum in this case because [cl] is a text field. Try using DCount instead since a count of the records that meet your criteria is all you are really looking for.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jhall156]

Yep, don't know how I missed that. JHall