Form CheckBox Question

[hatstand]

Hi All,

I have a simple problem that I can't solve. I have a Checkbox on a form and want a Query to pick up if it is selected. So I have used the line:

tester: IIf([Forms]![Months]![JanChk]=-1,"Y","N")

When the Query is run it asks for a Parameter Value. If I click OK I get 'N'. It seems so simple but I'm obviously doing something wrong/

 

[Joe4]

Query calculated fields can only reference Table fields. They cannot reference unbound fields found on a Form.
One option is to create a field on your Table, and make your Form field bound to this table field.
Then it should be available for Query calculated fields.

 

[hatstand]

Hi,

Thanks for the reply. I understand. I will need to create Checkbox for each month then when the Query is run it will only run for the checked months. So I will need to create a field in the months table to hold the Checkbox values. Then Query will pick up the values from there. At least that's the theory.

 

[xenou]

Not sure but I don't think that unbound fields can't be referenced. I have done so before. You should also check for the usual suspects: make sure your form is open, make sure that the spelling is correct in the query. Not that I don't like having a table - it can help you if you want to run the query without using the form too.

 

[Joe4]

Not sure but I don't think that unbound fields can't be referenced. I have done so before. You should also check for the usual suspects: make sure your form is open, make sure that the spelling is correct in the query. Not that I don't like having a table - it can help you if you want to run the query without using the form too.

I could be wrong, maybe it is possible. But I have never seen it done.
I have seen it used it Criteria (the WHERE clause), but never in calculated fields (the SELECT clause).

I probably should have asked what kind of Form are they working with.
Is it an unbound form (so just a single record)?
Or a bound form with this unbound field (amongst other bound fields)?

I don't think the later would make much sense, as an open Form would really only affect that one record, and not all the records in the Table.

 

[Micron]

I tested because I'm the curious sort. You can use a reference to an unbound control (at least a text box) on a form in a calculated query field. Can't post pics here, otherwise I would. This
SELECT [Forms]![Form1].[Text12] AS tester;
produced "one" which is the unbound control's value.

This
SELECT IIf([Forms]![Form1].[check34]=-1,"True","False") AS test;
produced True or False, depending on the selection status of a checkbox on the form.

 

[Joe4]

This
produced True or False, depending on the selection status of a checkbox on the form.

Interesting...
But wouldn't it return TRUE/FALSE for ALL of the records being returned by the form then?
If so, I am not sure exactly what the point of doing that would be.

 

[Micron]

Yes it would. IMHO, it would have to be a 1 record source of some type, in which case one could just as easily do a DLookup. I suspect that once the parameter prompt is fixed (it must be a typo kind of problem) the OP would realize the expression as a calculated query field isn't much good if there are multiple records.