Maximum number of characters on a line exceeded 2

[florens]

Hi,

I have the following code in vb:

If dropdownlist = 16 Then
Me.RecordSource = "SELECT * FROM checkbox WHERE checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 772 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 772 AND checkbox_status_rs = 0 OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 773 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 773 AND checkbox_status_rs = 0 OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 774 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 774 AND checkbox_status_rs = 0 OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 775 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 775 AND checkbox_status_rs = 0 OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 776 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 776 AND checkbox_status_rs = 0 OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 777 AND checkbox_status_rs IS Null OR checkbox_resgrp_rs = 10 AND checkbox_parent_rt = 777 AND checkbox_status_rs = 0 ORDER BY checkbox_omschr_rs"
Else

This doesn't work because the line starting with Me.recordsource is too long. But I really do need all these criteria so I see no way to make this any shorter.
Does anybody have any ideas?

Thanks in advance

Florens

 

[mp9]

Try replacing your SQL statement with:

SELECT * FROM checkbox WHERE checkbox_resgrp_rs = 10 AND checkbox_parent_rt IN (772, 773, 774, 775, 776, 777) AND (checkbox_status_rs = OR checkbox_status_rs IS Null) ORDER BY checkbox_omschr_rs

If checkbox_parent_rt only takes integer values, you could use BETWEEN rather than IN.

HTH.

http://www22.brinkster.com/accessory/

 

[MichaelRed]

Instead of the (endless) list of conditionals, incliude a calculated field in a query and use that as your criteria.

MyCalc: [checkbox_resgrp_rs[ = 10 AND [checkbox_parent_rt] = 772 AND ...

"SELECT * FROM checkbox WHERE MyCalc = True;"

Of course it MAY be more convenient to have a set of the cals fields instead of just one HUGE one and do the select based on the set (or even sub sets).

On the otherhand, you have several groups / sets of similar conditions (6 groups of 6 conditions each), so the structure of the recordset seems a bit weird (possibly de-normalized) and you MIGHT want to review that aspect of the app design.

A third option could be to just shorten the names of the fields. You appear to be only a few chars chars per field over the limit, so a different naming convention would squeeze this one in. Something as simple as changing "checkbox" to 'chkbx' would save five chars per field reference. Loosing the undescore (just make the first letter of each 'part' of the field name a CAP would save a few more. Combining these (and 'expanding hte concept), I was able to shorten your query string to ~~ 850 characters.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[florens]

Thanks a lot to you both, I didn't know that you could use comma's in a query. And I never thought of shortening my fieldnames. Anyway, thanks a lot

Florens

 

[mp9]

Whoops, spotted a typo in my earlier post - the revised SQL should be:

SELECT * FROM checkbox WHERE checkbox_resgrp_rs = 10 AND checkbox_parent_rt IN (772, 773, 774, 775, 776, 777) AND (checkbox_status_rs =

0

 OR checkbox_status_rs IS Null) ORDER BY checkbox_omschr_rs

http://www22.brinkster.com/accessory/