Avatar of Wedmore
Wedmore asked on

Access Report Names in a loop inside a Macro

I have about 15 reports in access.  6 of them are required daily with a date stamp.  3 of them are to be saved in one location, the other 3 in another.  Because they require the time stamp, I am creating a macro to generate and save the reports in the correct locations.

I have given the reports a good naming convention so I know out of the fifteen, which ones should be daily.  In the macro though I dont know how to pull the report names to create a loop which I can use to output the reports.    I did create the following query in Access to pull the report names...

"SELECT msysobjects.Name FROM msysobjects WHERE msysobjects.Type=-32764"  (called qReports)

... but i cant see how I can use the query name qReports in the macro.

Coding sample is one loop for one location.  It crashes on the "Set rstData" line stating "Runtime error #13 Type Mismatch".

I think it should be fairly straightforward to what I what I want to achieve.  Let me know if further explanation is required.
strPath = "c:\dailys\"
 
Dim rstData As Recordset
  
Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName LIKE 'Daily Sales%' ")
 
Do While Not rstData.EOF
  strRep = Trim(rstData!ReportName)
  strFile = strRep & Format(Forms.Date, "yyyy-mm-dd") & ".pdf"
    
  DoCmd.OutputTo acOutputReport, strRep, acFormatPDF, strPath & strFile
    
  rstData.MoveNext
Loop

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Wedmore

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
koutny

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try this:

Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName LIKE " & Chr(34) & "Daily Sales%" & Chr(34))


The problem may be the % sign ...

mx
ASKER
Wedmore

DOH!!  ok now it works but the filter isnt.  There are reports which begin with the name "Daily Sales", but it returns no records.  If I remove the WHERE clause it does pull the list of reports.

I know this isnt really the original question but adding 100 points in fairness.
ASKER
Wedmore

DatabaseMX - tried that as you suggest but still the same.  I agree, I wonder if it has something to do with the percent.  I even tried putting the LIKE filter on the query itself, for a test, and no records returned.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

try this

Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName LIKE " & Chr(34) & "Daily Sales%" & Chr(34) & "*")
ASKER
Wedmore

No dice.
ASKER
Wedmore

Syntax error missing operator.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Humm ... ok, try it w/o the % sign:

Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName LIKE " & Chr(34) & "Daily Sales" & Chr(34) & "*")

mx
SOLUTION
Sean Strickland

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Wedmore

No luck.  It doesnt like the "Daily Sales"*".   I even removed the asterisk so the original LIKE condition without the percentage and it doesnt return results.

I have found an alternative solution to use WHERE LEFT(ReportName, 11) = "Daily Sales" and that works.  But i will leave this open overnight in case someone comes up with a reason why the LIKE condition doesnt work.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

What are examples of some actual names?

mx
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Did you try this: http:#a21619524 

?
Sean Strickland

Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName = 'Daily Sales*'")

It doesn't have a choice.  You have to make it like *!
ASKER
Wedmore

Sorry Sean, I posted my last response at the same time as you posted your solution.

Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName Like 'Daily Sales*'")

Works!!!  You have to use the asterisk instead of the percentage sign and use LIKE not EQUALS (last solution wont work).

Thanks all.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.