I'm trying to run a report generating mailing labels. Here's what I have:
Case SmallJrMailingLabels
strCurrentYear = "Table1.[Dues '" & Format(Now(), "yy") & "]"
strSQL = strCurrentYear & "<> 0 " & "AND Membership Type = JUNIOR"
DoCmd.OpenReport "5160(small) Jr membership labels", acViewPreview, , "strSQL"
strCurrentYear becomes Due '14
I have a query that has name and address as selected fields from the main table and is associated with the main table in the query properties. I want to have 4 type of mailing labels:
All big labels
All small labels
Juniors big labels
juniors small labels
The only difference is parsing for the membership type.
When I run this I get a prompt for strSQL. If I take off the quotes around strSQL in the DoCmd I get an error.
I've tried a number of things to get it to work. I've even put "Dues '14" in place of strSQL for the DoCmd and get an error.
Need some direction to straighten me out.
Thanks,
Ric
If so, remove that single quote after "Dues":
strCurrentYear = "Table1.[Dues " & Format(Now(), "yy") & "]"
Next, enclose "Membership Type" in square brackets, and (assuming [Membership Type] is a String value) enclose the word JUNIOR in single quotes, and remove the &:
strSQL = strCurrentYear & "<> 0 AND [Membership Type] = 'JUNIOR'"
This should result in strSQL looking something like:
Table1.[Dues 14] <> 0 AND [Membership Type]='JUNIOR'
Finally, don't enclose strSQL in quotes (you only do that with literal values, not with variables):
DoCmd.OpenReport "5160(small) Jr membership labels", acViewPreview, , strSQL
A word of caution: You really, really, really should not name Fields with spaces. In fact, you really should only use AlphaNumeric values and underscores for field names. Instead of [Membership Type], use MemberShipType. Instead of [Dues 14], use Dues14
Also, when we see columns name with a numeric value (like "Dues 14") we often find an improperly built database. If you have Dues that are collected per year, you should have a Dues table, and each Row of that table would be the value of a specific member's Dues that is collected, along with the Date of the Dues. From there, it's a simple matter to query that table for all Dues collected in a specific Year.