View and Printing Access Report from VB!

MoAfrika
MoAfrika used Ask the Experts™
on
hi!
i urgently need help,as i have to present the new system to management.I actually want to view an access report then print it from VB, or directly print the report
this is the code i used:

Const normal = 0
Const preview = 2
Const axit = 2
Const dbname = "c:\customer care system\customer care.mdb"
Const rname = "fault"
Const app = "asset manager"
On Error Resume Next
Dim Access As Object
Set Access = CreateObject("Access.Application")
Access.OpenCurrentDatabase dbname
Dim where$

where$ = "((Date1=DateSerial(08,11,2002)) and (Date2=DateSerial(08,11,2002)))"
MsgBox (where$)
Access.DoCmd.OpenReport rname, acViewNormal, , where$
Access.Action = 1

Set Access = Nothing
Unload Form1

if possible please respond before monday the 18/11/02.I appreciate your transfere of knowledge.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
and where is the problem?

Author

Commented:
It prints very well but does not print according to my inputs of dates,what it doe is it prints the entire report.I also dont like it prompting me to enter the dates whreas i did in the program.

Thanks
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Are Date1 and Date2 fields of the report (or fields of the tables/views used to create the report?

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Access.DoCmd.OpenReport rname, acViewNormal, "", where$
Access.Printout

Commented:
try putting this in where$ "WHERE " before your other text:

where$ = "WHERE ((Date1=DateSerial(08,11,2002)) and (Date2=DateSerial(08,11,2002)))"

& running your code again..

geo
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
zabba_du, you seem to have never use it. The WHERE keyword won't work there!

Commented:
duly noted.

wow! I totally skipped over that in the help files.. thanks for putting me back on the straight & narrow, emoreau..

geo
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
MoAfrika, we need your help if you need it by monday morning!

Commented:
well, here's my latest take on the whole thing:

rather than guess what Date1 & Date2 are (fields or variables), I would suggest trying this:

if they're fields, try this:
where$ = "[date1] between dateserial(08,11,2002) and dateserial (08,11,2002)"

just checking, though, my documentation for dateserial says dateserial(yyyy,mm,dd), does it change for different locales?

if Date1 & Date2 are variables, then maybe this might be the issue (let's call the date field DateField):

where$ = "[DateField] between #" & dateserial(08,11,2002) & "# and #" & dateserial(08,11,2002) & "#"

good luck..
geo

Author

Commented:
thanks for a responses,
This is triky, the solution you send did not work and sometimes it does not print at all. i'm also trying to have the funtion working and also thinking of switching to the Crystal reports.
Thanks a lot for your help, it means a much
MoAfrika,
Did you try my solution of
Access.DoCmd.OpenReport rname, acViewNormal, "", where$
Access.Printout
?

It may save time over switching to Crystal since mgt is breathing down your neck.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Can you show the query as you use it from Access with the dates parameters?

Author

Commented:
twalgrave
i did try your solution, it does work but it does not print the report per date range

thanks,
Sebata sa MaAfrika
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- refund and close
Please leave any comments here within the
next seven days.
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial