Solved

Problems with the DoCmd.OpenReport command

Posted on 2014-01-16
10
318 Views
Last Modified: 2014-01-18
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
0
Comment
Question by:Joppa
  • 5
  • 3
  • 2
10 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39787189
Do you have a Table named "Table1"? And does that table have a column that would be named "Dues 14"?

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.
0
 

Author Comment

by:Joppa
ID: 39787462
Scott,

  Thanks for the suggestions and I'll give them a try in a bit.  I inherited this database with the naming convention as it is(including the single quote in Dues '14).  Maybe I'll clean the table up before trying this.  It might take a few days so I'll get back to here and let you know how I'm making out early next week.

Thanks,
Ric
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39787618
I agree with everything said above, ..

I also believe that you have complicated this more than it needs to be...

Note that it seems that by "All", (AFAICT), your code really is looking at "All from the Current year", ...(Not technically "All" records", because I see no reference to any year in your 4 criteria...)

Based on this, ...and what Scott posted above, I see something like this:

JeffCoachman
Access-EEQ28340758SelectReportAn.mdb
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 84
ID: 39788097
If the field is actually named [Dues '14] then you'll have to add that back in (obviously)
I inherited this database with the naming convention as it is
I feel your pain ...
0
 

Author Comment

by:Joppa
ID: 39788409
Jeff,

   I can see where from what I wrote that juniors would appear to be years in high school.  These blurbs in a forum don't give all the information.   However, this is a club where the members are FULL, ASSOCIATE or JUNIOR(under 18).  The database contains dues for membership in prior years.  The committee wants to be able to look at prior active and in-active members so there's is a history of dues(dues '12, dues '11 and so on).  When sending the monthly membership letter out, the secretary needs to print labels only for current members. I hope this gives you a clearer picture of what I need to do.

Scott,

  I was able to get the table field names fixed up last night,  Now I have to fix everything else to make sure I haven't broken anything.  I work from home and may be able to get to it today........unless my boss reads this forum, then I'm too busy.  :-)

Ric
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39788709
Joppa,

Then feel free to take what I posted in the sample database and adapt it t your needs.

I am still a bit confused on your date criteria, ...but in general you would have a date field, (that only contains a date)
And you can do things like this to specify the year:

Current Year:
Year(YourDateField)=Year(Date())

Last Year
Year(YourDateField)=Year(Date())-1

Next Year
Year(YourDateField)=Year(Date())+1

Please continue working with Scott to tune up your database.
;-)

JeffCoachman
0
 

Author Comment

by:Joppa
ID: 39789947
Ok, I fixed the table, all the queries, forms and reports to the new naming conventions.  I fixed the VB and I'm back to the labels.

Just for clarification since I'm not explaining it well.  The Dues14 field in the table is not a year.  It contains a currency amount pertaining to the amount of dues paid by the individual for that year.  Anything other than NULL or 0 means they are a current member of the club.  Juniors are defined as a family member under the age of 18.  The club wanted to be able to go back to prior years to see what members there were, so every year a new column is added for the current year.  I hope this makes more sense.

Scott,

  I tried the changes you suggested and I still get a pop up box.  To simplify things for me, I'm just working with the Dues field <> 0 at this time.  I can add the JUNIOR parsing later.

Here's what I have:

Case SmallMailingLabels
       
            strCurrentYear = "Table1.[Dues" & Format(Now(), "yy") & "]"
           
            strSQL = strCurrentYear & " <> 0 "
            DoCmd.OpenReport "5160(small) membership labels", acViewPreview, , strSQL
           

strCurrentYear correctly becomes Table1.[Dues14]

strSQL  becomes Table1.[Dues14] <> 0

If I leave the quotes off in the DoCmd around strSQL I get a pop up asking for a value for Table1[Dues14]

If I have the quotes in the line I get a pop up asking for a value for strSQL.

Not sure what I'm doing wrong at this point.

Thanks,
Ric
0
 
LVL 84
ID: 39789960
If I leave the quotes off in the DoCmd around strSQL I get a pop up asking for a value for Table1[Dues14]
Then you don't have the name correct. Access is asking because it cannot locate the table or field.

Does your report's Recordsource contain the table named "Table1" - spelled exactly that way, with no spaces?

Does that table contain a field named "Dues14" - and spelled EXACTLY like that, with no single quotes?
0
 

Author Comment

by:Joppa
ID: 39790140
Hmmm, I know that's the problem because I took those fields out of the source query.  My thinking was I'm using valid fields in the SQL located in vb.  If I have to have that field specified in the source query than every year I need to add the new year to the query.  That is, this year  strCurrentYear = "Table1.[Dues" & Format(Now(), "yy") & "]" will be Dues14  next year it will be Dues15 and I would have to remember to add the new field to the source queries.

  Hope this makes sense.  Is there anyway around this problem?
0
 

Author Comment

by:Joppa
ID: 39790577
Figured it out.  The original reports had a query for their source.  I changed that to Table1 and everything worked.

Thanks for the help.

Ric
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question