Report loop -slight issue with ' or "" (1 Viewer)

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
Lots of swearing ..
OK I have a report and I intend to email out - (I can handle this )
I want to export Statementrptemail as pdf - (not an issue)
however when I run this I get all of the statemetns instead of one AccountNo
so
Brown Eggs - 1 report
Sugar Daddy 1 report

currently i am getting 2 reports but with both of the above - ..i know its to do with " or @ but its thrown me .
:eek::eek:


DoCmd.OpenReport "Statementrptemail", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & " '"
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
AccountNo is numberic as is rsEmail.Fields(0
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
- Solved - had a cup of coffee and

DoCmd.OpenReport "Statementrptemail", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & "" '"

-sometimes I am such a plonker

G
 

vbaInet

AWF VIP
Local time
Today, 16:29
Joined
Jan 22, 2010
Messages
26,374
I can still see a quote there GaryPanic. For a numeric field it should be:
Code:
"AccountNo = " & rsEmail.Fields(0)
But since you're exporting to pdf, you don't necessarily need to open the report beforehand. As an alternative, you can set the filter property of the report in its Open event. The filter will be gotten from a global variable which gets sets prior to calling the OutputTo method.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
I can still see a quote there GaryPanic. For a numeric field it should be:
Code:
"AccountNo = " & rsEmail.Fields(0)
But since you're exporting to pdf, you don't necessarily need to open the report beforehand. As an alternative, you can set the filter property of the report in its Open event. The filter will be gotten from a global variable which gets sets prior to calling the OutputTo method.


I stared at this for a good 45 minutes .. drove me bonkers ..
I'll tidy up later

Not sure how to mark post as solved ..?
 

vbaInet

AWF VIP
Local time
Today, 16:29
Joined
Jan 22, 2010
Messages
26,374
Within the frame of your first post, you'll see a drop down button and within that you should be able to find the mark as solved button.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
Ahh - run against a very minor issue ..

can i put an if statement in a loop

IF rsEmail.Fields(22 ) = "Client "
then do
DoCmd.OpenReport "Statementrptemail", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & "" '"
esle

IF rsEmail.Fields(22 ) = "Broker "
then do
DoCmd.OpenReport "StatementrptemailB", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & "" '"elds(0) & "" '"

thought i would ask before i do stuff
 

vbaInet

AWF VIP
Local time
Today, 16:29
Joined
Jan 22, 2010
Messages
26,374
Do stuff and see the result ;)

And I can see that you've chosen to still open the report beforehand.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
did stuff - went pears- shaped ..
reviewing - and yes - should of started a new thread...
 

vbaInet

AWF VIP
Local time
Today, 16:29
Joined
Jan 22, 2010
Messages
26,374
Best way to learn and remember ;) What code did you use and what was the result?

It's all related so the same thread is fine.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
resolved after some twerking ... - not a pretty sight
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
If rsEmail.Fields(7) = "Broker" Then

DoCmd.OpenReport "Statementrptemail", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & "" '"


DoCmd.OutputTo acOutputReport, , acFormatPDF, "S:\Apex Data\ARC INTERNATIONAL\Statements\" & .Fields(6) & ".pdf", False
DoCmd.Close acReport, "Statementrptemail", acSaveYes

End If

If rsEmail.Fields(7) = "Client" Then
DoCmd.OpenReport "StatementrptemailClient", acViewPreview, , "AccountNo = " & rsEmail.Fields(0) & "" '"

DoCmd.OutputTo acOutputReport, , acFormatPDF, "S:\Apex Data\ARC INTERNATIONAL\Statements\" & .Fields(6) & ".pdf", False
DoCmd.Close acReport, "StatementrptemailClient", acSaveYes

End If

a little clunky - but as there is only every two options - and not lots of them
 

vbaInet

AWF VIP
Local time
Today, 16:29
Joined
Jan 22, 2010
Messages
26,374
I said that in jest :)

But seeing the code you most likely need an Else or Else If statement instead of two If blocks.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 09:29
Joined
Nov 8, 2005
Messages
3,296
Its working - I'' tidy up laters .

(tonight and see which works quickest ) there should never be more than say 100 statements to go through - as I pre select the ones to create (and email)

so GBP may only have 20
EUR may be 50
USD 5
AUD 5
Rands 10 etc

I have selected by currency - and we can cherry pick which ones to run
 

Users who are viewing this thread

Top Bottom