Need help limiting displayed records in a query (1 Viewer)

[eepok]

Hello again my forum o' saviours,

Current filter:

WHERE (Assignments.[AcademicYear]) Like ([Please Enter Academic Year])

AcademicYear is a field in the Assignments table (duh).

In this same query, I have a couple fields (equations and if/thens built on the assignments table) that will need to be used as further filters.

I thought something like

WHERE ((Assignments.[AcademicYear]) Like ([Please Enter Academic Year]) )& (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

would work, but I keep getting pop-ups asking what the values of the three query-based calculations are.

Where did this noob screw up?

 

[jzwp22]

In your WHERE clause you have to use the word AND not &

WHERE ((Assignments.[AcademicYear]) Like ([Please Enter Academic Year]) )AND (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

Also, are groupwinterbox, groupspringbox and groupfallbox fields from the assignments table or do they refer to expressions?

 

[eepok]

In your WHERE clause you have to use the word AND not &

WHERE ((Assignments.[AcademicYear]) Like ([Please Enter Academic Year]) )AND (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

Also, are groupwinterbox, groupspringbox and groupfallbox fields from the assignments table or do they refer to expressions?

Expressions in the same query.

 

[jzwp22]

Is academic year a numeric field? If so, you might try to replace the LIKE with =. If it is a text field then you might want to include a wildcard


if numeric:
WHERE ((Assignments.[AcademicYear]) = ([Please Enter Academic Year]) )AND (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

if text:

WHERE ((Assignments.[AcademicYear]) LIKE "*" & [Please Enter Academic Year] & "*" )AND (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

Back to the groupwinterbox, groupspringbox.. Do the names represent the alias for the expression?

If the above changes do not work, you might have to repeat the expressions in the WHERE clause rather than using the alias for the expressions.

 

[eepok]

Is academic year a numeric field? If so, you might try to replace the LIKE with =. If it is a text field then you might want to include a wildcard

if text:

WHERE ((Assignments.[AcademicYear]) LIKE "*" & [Please Enter Academic Year] & "*" )AND (len([groupwinterbox])+len([groupspringbox])+len([groupfallbox]) > 3)

Back to the groupwinterbox, groupspringbox.. Do the names represent the alias for the expression?

If the above changes do not work, you might have to repeat the expressions in the WHERE clause rather than using the alias for the expressions.

AcademicYear is Text because the values are "2001/2002", "2002/2003", etc. which all comes from a long story about the users wanting a very specific UI... but that on its own works great.

GroupWinterBox et al. are aliases for expressions and your comment about possibly needing to use the actual expressions is, well, scary given how involved the expressions are.

I am yet to put in the AND instead of "&" and I'll toss that in there right now. I'll report back soon.

 

[eepok]

ok, I've done the above and no dice. I have made a potentially important observation, though.

When the expression:

TEST: Len([groupspringbox] & [groupwinterbox] & [groupfallbox])

is entered into the query, the query spits out the appropriate numbers. All is good.

However, once I add ">3" to the criteria/WHERE, the query acts like it's never heard of "[groupspringbox] et al.

 

[jzwp22]

Can you explain in words what this part of the WHERE clause is trying to do?

Len([groupspringbox] & [groupwinterbox] & [groupfallbox]) >3

The Len() function is used to return the length of a text field. do the groupboxes return a text value?

 

[eepok]

Can you explain in words what this part of the WHERE clause is trying to do?

Len([groupspringbox] & [groupwinterbox] & [groupfallbox]) >3

The Len() function is used to return the length of a text field. do the groupboxes return a text value?

Ok, the first part of the where clause asks the user to type in the academic year of the report s/he wants to see. "2007/2008," s/he types in, and a report of a all kinds of different classes and grades pops up for that year.

The second part of the filter is checking whether or not any one of those three expressions turns up information. If there is no information, then I don't want the record displayed because there are a ton of records in any given academic year.

Theoretically, I could use "Is Not Null", but I wasn't sure if line breaks (used to sort information for report display reasons) counted as characters. There's one linebreak used in each of the expressions, so, just in case they did, I put ">3". I ended up trying "Is Not Null" and I got the same results -- the query gave me pop-ups asking for the value of those "groupbox" expressions.

The groupboxes do return text. Where I think there may be an issue is that "groupboxes..." are built on 2 more layers of equations. So:

Field + Field -> expression1 + Stuff -> Expression 2 + stuff -> Groupboxes (nice and formatted for display in a report)

Technically, I can write out "groupboxes" as entire expressions instead an expression built on an expression but that's really a lot of, well, stuff, and I am still kinda hoping that I needn't do so.

/smashes head into desk

Thanks, though, for all your help thus far.

 

[jzwp22]

I think you will have to repeat the full expression in the WHERE clause. I ran a test on a very simple DB and was able to recreate your error. If I repeated the full expression in the WHERE clause, it worked with no problem.

 

[eepok]

I think you will have to repeat the full expression in the WHERE clause. I ran a test on a very simple DB and was able to recreate your error. If I repeated the full expression in the WHERE clause, it worked with no problem.

Ha! Well this is going to be messy.

Thanks for all your help!