Visual Basic :: Export MS Access 2002 Parameter Report Using VB6

Using VB.NET And MS Access 2002 - Need Import And Export And FTP
Hello, I am working on a database created in MS Access and Using VB.Net Datagrid view and update the database. I would be using SQL Server 7.0/2000 if the examples I found in Microsoft Help and Wrox VB books would work but the don't. I have already create VBA in Access to import and export database to .csv files but they are not simple steps and I want to control these steps from the VB.Net application I am designing. What I get is a csv file to import with specific specification that is setup in Access. The problem I need to fix is to be able to import the file into access using access specification file where the file name format it "nameoffile_DATEformat_timeformat.csv" - name_08272002_0815.csv where the date and time is always different. Meaning the date is the today's date and the time different every time. My goal is to be able to import this file on a schedule time. Also I will need some how probably by adding extra column named "Import Date" to capture the date which the records have been imported recently. This file is manually download from a ftp site and manually imported into access which my goal is to automate this. Same thing will need to be done on exporting. I will need to some how capture the date when the record is exported. file name format is same name_[current date]_[current time].csv

what I have in access is:
Import Click Form Button

Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg As String

   ImportFile = "C:ftpfiles est.csv" '[where test.csv not hard coded.]
   tblName = "Main Table"
   DoCmd.TransferText TransferType:=acImportDelim, _
   SpecificationName:="table specification", _
   TableName:=tblName, _
   FileName:=ImportFile, _
   HasFieldNames:=True

End Sub

Export portion
Private Sub Command1_Click()
Dim OutputDir, ExportFile As String, tblName As String
Dim OutputMsg As String

   ExportFile = "C:ftpfiles estexport.csv" '[where testexport.csv not hard coded.]
   tblName = "Main Table Query"

   DoCmd.TransferText TransferType:=acExportDelim, _
   SpecificationName:="table specification", _
   TableName:=tblName, _
   FileName:=ExportFile, _
   HasFieldNames:=True

End Sub

Will need to add a error handler for import because some days we don't have an file to import. The import want to automate as well as have a manually way to import if errors occur. Export will be done manually base on a query looking to see if the information is either [cleared and registered or rejected] and not exported before.

Thanks for any suggestions.

How Do I Pass Access Password With Crystal Report 4.6 And Access 2002?
Hi!

I'm having a little problem. I can't do any report with crystal, because it's asking me a password when I'm trying to open database. It gives me an error message which is "Error detected by database DLL. Database DLL is corrupt".

I have reports, which have been made somewhere else and so the path to the database has been changed. Well, I made a new database and tried to do report with that, but it didn't work, because it's asking me a microsoft access password again (and I don't know what it is) and after that it gives me an error message which I ment above.

So I can't do any reports with existing or with new database. I don't understand where is the problem? Is it some .dll library which I should update or something else? I'm using visual basic 6.0, microsoft access 2002 and crystal report 4.6. I'd be very grateful if someone could help me...

Access 2002 Report
When my sub-report is empty access don't open it. I wont a my report because i need the header of th sub report in my report.

What can I do?

Access 2002 Report
Hi all,
I have a catalogue which is based on a access2002 database and the catalogue is made using the access2002 report, the report has 4 pictures per page which are in straight columns. how do i go about having the reports randomly place the pictures on each page or place the picture on the page where I want them to go..

Thanks

Regards

Tony

Report Errors Using VB6 And Access 2002
I have written a program that keeps track of employees' paid time off. I have also created various reports in Access 2002 that my program calls and runs. The program and database are stored on a server and the employees access the program over our network. My problem is that I have about 8-10 users that can access the program and run it fine, but not every one is able to open the reports. I would say about 3/4 of the people that run the program can open the reports fine, but there are a couple that have an error when they try to run the report. All of the PCs are running Win2000 or WinXP and have Office 2002 installed, and all PCs are accessing the same program, but I do not know why certain PCs can open the reports and others can't. Any help that anyone can provide is greatly appreciated! Thank you!

Access 2002 Concatenate Fields In A Report
Okay this should be easy as pie.

I have three fields:
City
Province
PostalCode

In separate textboxes in the report they display fine.

In a new textbox, I set the Control Source to

Code:
=[City] & " " & [Province] & " " & [PostalCode]
Shouldn't this work in Access 2002? In the function list Concatenate doesn't see to exist anymore.

Access 2002: Call VB Module From Report
Hello,
Do you know what is the best way to call VB modules from reports.
Thanks in advance.

How To Code A Vb Program To Print A Access 2002 Report
Whats an easy way to call a report in VB from Access 2002? I need to call a report named "PayrollSchedule". The program is written in VB6.0 and connected through a ADODC control to an Access 2002 database. I have a command button designed to print the report with no code. I need it to print by PayrollID. Any help or suggestions would be greatly appreciated, thanks.

VBA Export MS Access Report As .doc
Hi All,

I am using MS Access 97, and I would like to know the VBA code for exporting a MS Access report as a word document.

Thanks for the help,

Access Report Export
I have built an access database that creates dynamic reports and then sends them automatically via lotus notes. However a customer has pointed out that the letter looses the company logo during transit. I have tested it and it looks as though the image is not exported to the word file with everything else. Does any one know how I can retain the logo on the exported letter (its a image object)

Thanx in advance

Export An Access Report To PDf
I know, wrong forum but someone here will know how to do this.

How do I programmatically export a MS Access 97 report to PDF?

How To Export MS Access Report
Hi all

I have a Microsoft Access report that i want to export to a folder on my harddisk but the problem is, each month, i would like the script to run that will export this report to a folder on my Hard dirve.
each time the report is exported, it should be saved with the user id of the recipient, this process will continue until all the recipients have reports attached to their names . there are about 300 recipients

Pleas help!!! I am new in Macro and VBA

Iyke

Passing Parameter To Access Report
Hmmm... it's late in the day and brain has stopped working.

I have a report running off a query in Access 2000. I want to print this report from Visual basic except change the criteria for the query b4 printing. Clues gratefully appreciated.

Scrooge

Open Report With Parameter VB - Access
Hi
I wonder if anyone would be kind enough to demonstrate the code required to pass a parameter to the Report created in MSAccess which uses a Query Created in MSAccess. The Query has a Criteria of =? against the staff ID which I want to pass from the VB app

Here's what I have

Code:
Case "STAFF_MANDATORY"
Set accApp = New Access.Application
accApp.OpenCurrentDatabase data_conn.training_connection.ConnectionString
accApp.DoCmd.OpenReport " "
accApp.DoCmd.Maximize
accApp.Visible = True
Set accApp = Nothing
Its the open report bit I can't figure on how to give the parameter

Thanks for any help

Export Access Report To Excel
Goodday Experts

I need to automate the process of exporting the data in an Access report into Excel. I initialy thought of writing a macro, however when I opened the VBA window it said "Project locked: Project Unviewable". This is a project that someone(no longer works for the company) else worked on and I just need to modify it to carry out the above mentioned process.

I am currently using version 2000 of Access and the program was writen in a previous version. However when I try to convert it, the convert menu is disabled.

Is there another way of going about this? I would appreciate any help.

Kind Regards

Export Access Report To Word
Is there a way to programmatically export an Access report to Word (that is, to a ".doc" file)? If so, could someone show me how this is done? Thanks in advance!!!

Export Access Report To Word
is there a way to bring an image from my access report onto my word document when i export it?

it's important that i have the company logo on the word document

Use VB To Export Access Report To Excel
Hi,
I have an Msaccess report, I would like to export this report to excel, and setup my margins, my title, fonts, columns, etc..
I would like to use VB in Access to generate this kind of report in Excel.

Please need your help.

Thank you

Export Access Report To Snapshot
I'm trying to use output method to export an active report to local disc, and I receive the following error message:

error number: 2487
The Object Type argument for the action or method is blank or invalid,

Can you let me know what is my mistake in my following code?

Code:
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OutputTo acOutputReport, , "Snapshot Format", "c:" & Me.Name & ".snp"
End Sub

Best regards
Michael Sakellis

Michael Sakellis
Athens-Greece

[Access] Export A Report To Jpg File
Hi,
someone know how to do it?

Thanks

nec

Problem With Printing An Access Report With Parameter
Hi
I am trying to print an access report from a VB6 applicatio using the following code:

Screen.MousePointer = vbHourglass
PrintAccessReport dbname:="database.mdb" rptname:="rptMaintenanceList", preview:=False
Screen.MousePointer = vbDefault

The report is based on a query that has a parameter in it. Th parameter is a Period (ie. 200410,200411, etc). Can anyone hel with code to pass the parameter to my VB application. As it is when I press print a parameter box appears. How do I stop thi from happening?

Thanks to anyone that can help

Displaying Access 2002 Listbox In Powerpoint 2002 Slideshow
Hi
I'm an archetypal newbie. I have created an Access 2002 database as a prototype for a web-based application. I would like to be able to use Powerpoint to demonstrate or emulate a couple of forms I have created in the database. The forms include simple text boxes, check boxes, option boxes and list boxes.

I can 'cheat' in PowerPoint by inserting simple ADOs with dummy data to emulate all boxes except the list boxes. I would prefer to have fully functional integration but if it is easier just to create dummy list boxes that would do.
Can anyone advise me of a simple way forward? I thank you in advance for your assistance
kind regards
Michael

Help!!! To Export Label In Access Report In Excel
Hi Friends

I am exporting the report in access in to excel sheet by clicking on the export option in the file menu of access report. My problem is that I am unable to export captions which are in the labels.

PLz!!! help me its really really urgent

Thanks in advance

Shivpreet2k1

Export Formatted Report Access->Excel
Hi I need to export a fully formatted report from Access to excel using only Access and VBA.

I am familiar with both the transferspreadsheet command and the outputTo command, but from my experience, these do not provide a fully formatted report with headings, subheadings and page and report totals.

Can anyone suggest something that would work?

It's Urgent! How To Export A Report From Access To Excel
HI!
I'm searching for a way how to export a complete report from Access to Excel with page-heads an -feet and the data. Please, can anybody help?

Thank you,

Access Report Export To Excel Issue
Hello!
I got a problem when exporting a report from access to excel.
This report gets data from a very simple query (SELECT * FROM Table), and it got a very simple layout

- Page header:
Contains some static descriptions
- Details:
Some Textboxes on a single line bound to fields from the query.
Some other Textboxes showing the result of formulas.
- Page Footer:
Just a page number and a print date field.
- Report Footer
Some Textboxes showing the SUM() of the fields in the details section.

Now, surprise.
I can export to excel with DoCmd.OutputTo perfectly.

The second step involves the creation of formulas in the report footer section.
In details section I have fields like "=A/B".
In the footer I have fields like "=SUM(A)/SUM(B)".
When I preview the report I get both type of field working.
But in the EXCEL file I can't see the second type.
I tryed some solutions but I still can't see that kind of formula.

Thanx

-- EDIT WOOOOOOOOOOOOOPS !!!

Dtpicker,access Parameter Query,report Error
Good day to all!

I am new to this forum and i have an ongoing project in vb 6.0 that is simple POS system. I have not completed this project yet.

I am creating a form called sales report with two dtpicker, and three command buttons. One dtpicker is for selecting a starting date and the second one is for selecting an ending date. The values from these dtpicker controls will be sent to a parameter query in MS Access 2000. I have tried several codes and have not been successful. The data report should access the parameter query in the database and will be viewed when the user choose the button cmdViewReport.

My parameter query in ms access has the following SQL Code:
This code will prompt the user to enter start and end dates to generate a sales report within the date range
The query name is qrySalesReport

SELECT OrderMain.OrderId, OrderMain.OrderDate, orderdet.Item_Code, Item.Item_Name, Item.Item_Price, Item.Item_Unit, orderdet.Q_Ord, [Item_Price]*[Q_Ord] AS amount
FROM (Customer INNER JOIN OrderMain ON Customer.Cus_ID = OrderMain.Cus_ID) INNER JOIN (Item INNER JOIN orderdet ON Item.Item_Code = orderdet.Item_Code) ON OrderMain.OrderId = orderdet.OrderID
WHERE (((OrderMain.OrderDate) Between [Starting Date] And [Last Date]));

My code in sales report form with errors

Option Explicit
Dim s_date As Date
Dim e_date As Date
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Dim rst As New ADODB.Recordset

Private Sub cmdExit_Click()
Set rst = Nothing
Set cnn = Nothing
Unload Me
End Sub

'this button will send the values from dtpicker controls to
'access parameter query
Private Sub cmdProcessReport_Click()

s_date = FormatDateTime(dtStart.Value, vbShortDate)
e_date = FormatDateTime(dtEnd.Value, vbShortDate)

Print s_date & " " & e_date

'With cmd
' .ActiveConnection = cnn 'predefined connection object
' .CommandType = adCmdText
' .CommandText = "select * from qrySalesReport"
' .Parameters(0) = DateValue(s_date)
' .Parameters(1) = DateValue(e_date)
' Set rst = cmd.Execute
' End With

With cmd
Set .ActiveConnection = cnn
.CreateParameter "[Starting Date]", adDate, , , DateValue(s_date)
.CreateParameter "[Last Date]", adDate, , , DateValue(e_date)
.CommandType = adCmdStoredProc
.CommandText = "Select * from qrySalesReport where [OrderDate] Between" & DateValue(s_date) & "And " & DateValue(e_date)
'.CommandText = "qrySalesReport"
Set rst = cmd.Execute
End With

cmdViewReport.Enabled = True
End Sub

Private Sub cmdViewReport_Click()

With rptSalesReport
.WindowState = vbMaximized
.Caption = "Sales Report"
End With
rptSalesReport.Show
End Sub

Private Sub Form_Load()
cmdViewReport.Enabled = False
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:POSSampleOrders.mdb;"
End Sub

Hoping for any response.........

Dtpicker,access Parameter Query,report Error
Good day to all!

I am new to this forum and i have an ongoing project in vb 6.0 that is simple POS system. I have not completed this project yet.

I am creating a form called sales report with two dtpicker, and three command buttons. One dtpicker is for selecting a starting date and the second one is for selecting an ending date. The values from these dtpicker controls will be sent to a parameter query in MS Access 2000. I have tried several codes and have not been successful. The data report should access the parameter query in the database and will be viewed when the user choose the button cmdViewReport.

My parameter query in ms access has the following SQL Code:
This code will prompt the user to enter start and end dates to generate a sales report within the date range
The query name is qrySalesReport

SELECT OrderMain.OrderId, OrderMain.OrderDate, orderdet.Item_Code, Item.Item_Name, Item.Item_Price, Item.Item_Unit, orderdet.Q_Ord, [Item_Price]*[Q_Ord] AS amount
FROM (Customer INNER JOIN OrderMain ON Customer.Cus_ID = OrderMain.Cus_ID) INNER JOIN (Item INNER JOIN orderdet ON Item.Item_Code = orderdet.Item_Code) ON OrderMain.OrderId = orderdet.OrderID
WHERE (((OrderMain.OrderDate) Between [Starting Date] And [Last Date]));

My code in sales report form with errors
VB Code:
Option ExplicitDim s_date As DateDim e_date As DateDim cnn As New ADODB.ConnectionDim cmd As New ADODB.CommandDim prm1 As New ADODB.ParameterDim prm2 As New ADODB.ParameterDim rst As New ADODB.Recordset Private Sub cmdExit_Click() Set rst = Nothing Set cnn = Nothing Unload MeEnd Sub 'this button will send the values from dtpicker controls to'access parameter query Private Sub cmdProcessReport_Click() s_date = FormatDateTime(dtStart.Value, vbShortDate) e_date = FormatDateTime(dtEnd.Value, vbShortDate) Print s_date & " " & e_date 'With cmd ' .ActiveConnection = cnn 'predefined connection object ' .CommandType = adCmdText ' .CommandText = "select * from qrySalesReport" ' .Parameters(0) = DateValue(s_date) ' .Parameters(1) = DateValue(e_date) ' Set rst = cmd.Execute ' End With With cmd Set .ActiveConnection = cnn .CreateParameter "[Starting Date]", adDate, , , DateValue(s_date) .CreateParameter "[Last Date]", adDate, , , DateValue(e_date) .CommandType = adCmdStoredProc .CommandText = "Select * from qrySalesReport where [OrderDate] Between" & DateValue(s_date) & "And " & DateValue(e_date) '.CommandText = "qrySalesReport" Set rst = cmd.Execute End With cmdViewReport.Enabled = TrueEnd Sub Private Sub cmdViewReport_Click() With rptSalesReport .WindowState = vbMaximized .Caption = "Sales Report" End With rptSalesReport.ShowEnd Sub Private Sub Form_Load() cmdViewReport.Enabled = False cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:POSSampleOrders.mdb;"End Sub

Hoping for any response.........

Edit: Added [vbcode][/vbcode] tags for more clarity. - Hack

Mailmerge Problem Access 2002-Word 2002
This code presents me with a question which table or query to use for the mailmerge. I don't want that. What's wrong with the VBA code? Thanks, Jee

With wordDoc.MailMerge
.OpenDataSource _
Name:=dbNaam, _
ConfirmConversions:=False, _
ReadOnly:=True, _
LinkToSource:=True, _
addtorecentfiles:=False, _
passworddocument:="", _
passwordtemplate:="", _
writepassworddocument:="", _
Writepasswordtemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="QUERY Qry_AdressenSamenvoegen_bestuur", _
sqlstatement:="", _
sqlstatement1:=""
.Destination = wdSendToNewDocument
.Execute
End With

The name of the query isn't wrong. What's wrong with the VBA code (above)?
-------------------------------------------------------
While this complete code with TABLE in the CONNECTION string works fine:

Private Sub Bt_BestuurStappen3_Click()
On Error GoTo Err_Bt_BestuurStappen3_Click

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim dbNaam As String

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open("d:KanwegStandaard bestuurders.doc")
wordApp.Visible = True

dbNaam = Application.CurrentDb.Name

With wordDoc.MailMerge
.OpenDataSource _
Name:=dbNaam, _
ConfirmConversions:=False, _
ReadOnly:=True, _
LinkToSource:=True, _
addtorecentfiles:=False, _
passworddocument:="", _
passwordtemplate:="", _
writepassworddocument:="", _
Writepasswordtemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="TABLE Tbl_AdressenSamenvoegen_bestuur", _
sqlstatement:="SELECT * FROM [Tbl_AdressenSamenvoegen_bestuur] ORDER by [Tbl_Organisatie_VTZ-nummer] ASC,[Unit_Naam] ASC", _
sqlstatement1:=""
.Destination = wdSendToNewDocument
.Execute
End With

wordApp.Quit savechanges:=wdPromptToSaveChanges

Set wordDoc = Nothing
Set wordApp = Nothing

Exit_Bt_BestuurStappen3_Click:
Exit Sub

Err_Bt_BestuurStappen3_Click:
MsgBox Err.Description
Resume Exit_Bt_BestuurStappen3_Click
End Sub

Uploading Excel 2002 Sheet To Access 2002 Db
Hi all,

Currently I've got an excel spreadsheet which I want to put into access 2002 database. The sheet contains just data, no headers or anything fancy thing. It's in the format which I want to put into the database. I've tried without success to do this. My code is listed below.

Code:
Private Sub stgUploadBut_Click()
    Dim csource As String
    Dim ctarget As String
    Dim xlApp As Application
    Dim wbook As Workbook
    Dim wsheet As Worksheet
    Dim xlPath As String
    Dim conn As ADODB.Connection
    Dim rs As Recordset
    Dim sqlStatement As String

    On Error GoTo uploadhandler
    xlPath = App.Path & ""
    Debug.Print xlPath
    csource = xlPath & "smttest.xls"
    ctarget = xlPath & "access_test.mdb"

    'Open Excel file
    Set xlApp = CreateObject("excel.application")
    'xlApp.Visible = True
    Set wbook = xlApp.Workbooks.Open(csource)
    Set wsheet = wbook.Worksheets("stgIssue")

    'Connect to the database
    Set conn = CreateObject("adodb.connection")
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & ctarget & ";" & _
        "Persist Security Info=False"
    conn.Open

    'Unprotect and select worksheet
    wbook.Unprotect
    wsheet.Select

    'Insert excel data into the database
    If conn.State = adStateOpen Then
        sqlStatement = "Select * " & _
                        "INTO [MyTable] " & _
                        "IN '" & ctarget & "' " & _
                        "FROM [$stgIssue]"

        conn.Execute sqlStatement

        conn.Close
    End If
    Set conn = Nothing

    'Close Excel file
    wbook.Close False
    xlApp.Quit
    Set wbook = Nothing
    Set xlApp = Nothing
    'MsgBox "Upload of excel spreadsheets to access database"

uploadhandler:
    MsgBox Err.Description
        wbook.Close False
        xlApp.Quit
        Set wbook = Nothing
        Set xlApp = Nothing
    Exit Sub
End Sub

I am not sure whether my SQL query is correct. I was under the impression that it was possible to do it like that. If it is wrong, or not possible to do the upload like this, please correct me and point me in the right direction.

Thanks.

Create A Report In Access Backend Then Export It To Word Or PDF
i would like to create reports within my access db backend, then when a user wishes to view, or print the report, it is created "silently" so the user does not see the access report, but instead is given a Word.doc report or a PDF report. is this possible and if so how would i achieve it?

Crystal Report Export To ODBC Access Problem
I have Access97 and was doing an export in my Crystal 7.0 report and received an error. My error says:

Failed to export the report.
[Microsoft][ODBC Microsoft Access Driver] Field 'GF1_' already exists in table [CREXPORT].

In my case, all my data is in the Group Footer 1. My Page header contains all the titles, but just as text objects. The Details section is hidden - only the Group Footer 1 is displayed. I have gotten rid of any duplicate field names. It seems the program is exporting all names as 'GF1_' though. Therefore, what change do you think I can make to eliminate this error?

Thanks for any help...

Wendy

Pass Input Parameter From Query To Report In Access 2000
I am using a parameter query to sum information from a table. Part of the parameters is to specify the date range to be summed as shown below:

SELECT TOP 10 [Lost Time Codes].Reason, Sum([Master Lost Time Entries].Minutes) AS SumOfMinutes
FROM [Lost Time Codes] RIGHT JOIN [Master Lost Time Entries] ON [Lost Time Codes].code = [Master Lost Time Entries].[Downtime Code]
WHERE ((([Master Lost Time Entries].Shift)="1st") AND (([Master Lost Time Entries].Date) Between [Enter Start Date] And [Enter End Date]) AND (([Lost Time Codes].Area)="THT"))
GROUP BY [Lost Time Codes].Reason
ORDER BY Sum([Master Lost Time Entries].Minutes) DESC;

I want to pass the the [Enter Start Date] and [Enter End Date] inputs to the report containing a chart. Anyone know how to do this, or of a completely different way to accomplish the same task without using the above query?

Edit by Moderator:
Please post SQL questions, in the Database forum.

Thank you.

Export An Access 2000 Report To Pdf File Using Visual Basic
I use VB 6 and Acrobat 4.
I need to export Access reports to PDF files using VB. I can't use the "Acrobat PDFWriter" because it asks interactively for the file name. There is no way to "export as pdf file" from within Access.
Do you know if an ActiveX exists to do that? Or another idea?

Thanks a lot,
SB.

Leftouter Join And Parameter Doesn't Work In Both Access And Crystal Report Help Please !
hai,

The below is the code of the Access Query. Previously I tried to bring the output in the Crystal report and I am failed, so I am trying to bring it in Ms Access. If I do this without the Parameter the output is coming correct and the leftouter Join is working fine, but when I pass the Date as a parameter, the left Outer Join didn't works, the output is only for the Date Parameter which I pass, the same happens for me in both Ms Access and Crystal Report. I don't know how to bring both. The output should be like below, I plan to display in the Crosstab format. whether Sales is in the Item code or not the details should be displayed, (i.e) details like Stock Quantity like that.

Sales.CustomerName
Sales.Itemcode Sales.Quantity Stock.Quantity Sales.Amount Sales.Gprofit

Code:PARAMETERS [Date From] DateTime, [Date To] DateTime;
SELECT dbo_SC010100.SC01001, dbo_ST030100.ST03017, dbo_SL010100.SL01001, dbo_SC030100.SC03003, dbo_ST030100.ST03020, dbo_ST030100.ST03021, dbo_ST030100.ST03022, dbo_ST030100.ST03023, dbo_ST030100.ST03015
FROM ((dbo_ST030100 RIGHT JOIN dbo_SC010100 ON dbo_ST030100.ST03017 = dbo_SC010100.SC01001) LEFT JOIN dbo_SL010100 ON dbo_ST030100.ST03001 = dbo_SL010100.SL01001) LEFT JOIN dbo_SC030100 ON dbo_SC010100.SC01001 = dbo_SC030100.SC03001
GROUP BY dbo_SC010100.SC01001, dbo_ST030100.ST03017, dbo_SL010100.SL01001, dbo_SC030100.SC03003, dbo_ST030100.ST03020, dbo_ST030100.ST03021, dbo_ST030100.ST03022, dbo_ST030100.ST03023, dbo_ST030100.ST03015
HAVING (((dbo_ST030100.ST03015) Between [Date From] And [Date To]));

My database is Ms Sql server 6.5 and I am using VB as a Front and Crystal Report for Reporting, Some guys suggested to create a procedure in Ms Sql and try, I have less expereience in that.Kind suggest me your idea.

Thank you very much,
Chock.
itchocks@rediffmail.com

<HTML>
HAVE A NICE DAY !
<HTML>

Edited by - chs on 4/7/2003 6:39:36 AM

Failed To Export The Report, Error In MS Excel Export Format DLL
Hi,

I have a little problem with Crystal Reports (CR 8) :

I try to export one of my reports from a VB program. When I try to export it in "Comma Separated Values" format everything is ok (except this is not the format I need
). When I try to export it in excel format it works, on some PCs but not on others ?

VB code sample :

rpt.ExportOptions.FormatType = crEFTExcel80Tabular
rpt.ExportOptions.DestinationType = crEDTDiskFile
rpt.ExportOptions.UseReportDateFormat = True
rpt.ExportOptions.UseReportNumberFormat = True
rpt.ExportOptions.DiskFileName = txtExportDirectory.Text & ExportName
rpt.Export False

I tried all the excel formats (crEFTExcel50, crEFTExcel50Tabular, crEFTExcel70 ...) but on some PC's I always get the "Exporting Records" window for a second and then the error "Failed to export the report, Error in MS Excel Export Format DLL".

Can anyone help me out ?
Thanks !

Problems Setting Export.parameter Values .net Vs SAP
Hi,

i have created a .exe in .net development enviroment. And I have problems to set the export parameter value. "Structure member not found: ". Here my source code.

FunctionCtrl = CreateObject("SAP.Functions")
boolLogon = False
'stropp = CmdArgs(0)
sapConnection = FunctionCtrl.Connection
sapConnection.Client =
sapConnection.User =
sapConnection.Password =
sapConnection.Language =
sapConnection.ApplicationServer =
sapConnection.System =
sapConnection.SystemNumber =
FunctionCtrl.LogLevel = 7
sapConnection.rfcwithdialog = True
FunctionCtrl.LogFileName = "c:saplog.txt"
isconnected = sapConnection.isconnected

If boolLogon = False Then
If sapConnection.Logon(0, False) <> True Then 'logon with dialog
sapConnection = Nothing
boolLogon = False
MsgBox("No connection to R/3 !")
Exit Sub
Else
boolLogon = True
dieFunc = FunctionCtrl.Add("ZCRM_OPPT_IMPORT_FROM_ELCOM")

'(CRM_BUPA_GET_RESP_CLERK"ZCRM_OPPT_IMPORT_FROM_ELCO")
If Not dieFunc Is Nothing Then
exportpar = dieFunc.export(1)
exportpar.value = "123456" 'GUID

At this point the debugger says: Strucutre member not found: "123456"
I have no idea what is wrong.

ret = dieFunc.call

End If
sapConnection.logoff()
End If
Else
sapConnection.Logoff() 'Disconnect from R/3
MsgBox("Disconnect from R/3 !")
boolLogon = False
End If

Any idea what is wrong.....

Thank you

Sigi

What's Logon Parameter To Oracle Server With Vb To Print Report With Crystal Report
Hi all,

How to write a log on parameter to oralce server with using vb to print a report that using crystal report

is it crystalreport1.connect = "Dsn=servername;uid=username;pwd=password"

and how to use the selectionformula

Parameter Query In Access Run In VBA: How To NOT Use A Parameter
Hi there

I am running Parameter Queries in Access from within VBA and I configure them the usual way, e.g:

qdf![NameOfTheParam] = ValueOfTheParam
...

Now one of my Parameters is a Boolean and I sometimes want to use it, but sometimes I just want to leave it unspecified.

Not specifying all Parameters of the query produces errors, I know that much. But what do I have to set the Parameter to to make the query look like there was no parameter???

I hope somebody understands what I am trying to say...

Cheers and thanks for any contributions

Msaccess 2002 Report
Hi all,
I have a access 2002 database - i use a access report from within the database. how do set it up so it only prints odd numbered pages then i want to set it up so it only prints even numbered pages.

The idea is there is 48 pages in the report which is grouped, i want to print on both side of my paper but i dont want to turn the pages over after the page is printed - so i thought if i print all the odds then i can take them, turn them over and then print all the evens..

Thanks

Regards

Tony

Edited by - anthp on 11/23/2006 9:32:40 PM

Prob. In Export A Report In RTF Format Through Crystal Report 8.0
I have a prob. in exporting a report in rtf format through crystal
report 8.0 on some client machine...

i am able to export report on my pc. but not on client machine....

plz suggest

--
Best Regards,
Sachin Jain

Problem Facing After Conversion Of Access 97 Into Access 2002
Dear friends

I was working in Access 97, later i decided to install Access 2002. Concequently vb starts some truble, like db grid is not showing data in tables. While i connected (open) database with following code:

'MyDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=H:UBAIRElite.mdb;Mode=Share Deny None"

Please checkout this

Thanx

Zubair Khan

Urgent! Problem W/ Access 97 Vs Access 2002
Hi

When I connect my DataName with old Access 97 DBA, my VB has no problem, but I have a new Access 2002 DBA, and when I connect to Access 2002, I set RecordSource to one of DBA field, the VB appl. give error
"Unrecognized database format ....mydata.mdb"

Pleae help

Converting Access 2002 To Pocket Access On HPC With Ce 2.11
I've developed a database with Access 2002 on my desktop that I want to put on my NEC 780 Handheld PC (HPC), which is running Windows Ce 2.11.

I've researched EXTENSIVELY. I keep coming back to Syware's Visual Ce and Embedded Visual Basic (eVB). The problem with Visual Ce is that it doesn't support pages (tabs) on forms! The problem with eVB is that I'm not a programmer, but a database hack. Thus, I would have to become a VB hack as well. Actually, I did complete 1 MS certified VB 6 fundamentals course in 2001, and I've had MUCH HELP from newsgroups in getting my database to work as desired.

My database has two main forms. It's not an elaborate database, but it will have some 400,000 records eventually. The controls on the forms are nothing more than 10 text boxes and 2 combo boxes, and I have 1 button on each of the forms for toggling back and forth between them.

Only 4 of the 14 tables of the database have relationships, so it's got a little SQL. In short, it's a simple, yet big database. Again, my need is to get the database onto a Ce 2.11 handheld device (HPC), which is not a pocket device... big difference in the form factor. HPC devices (clamshell devices) are wide and short, like the 92% size keyboard they fold into, whereas palm devices are not as wide or big.

I've taken great pains to get my database just how I want it on the desktop, but I still need to get it onto my HPC. Please advise.

I've got VB 6. Should I plop down $140 for the Windows Ce toolkit ("http://www.vbce.com/vbcestore/software/vbce6_toolkit.htm")?

Can you refer me? Help!

Thanks,
-Dave

Problem In Exporting Datas From Crystal Report 8.0 To Excel 2002
Hi
This is Rajasekaran

I am trying to Export Datas from Crystal Report 8.0 to Excel 2002 Normally using export option in the crystal report. But its saying Error like "Visual Basic Program Encounter Error" and that "Send " " Dont Send " Screen coming

But the same report Export to Text and other word formats except excel

All other reports are working fine..Except this one

The Report have few Supressed Fields with multiple grouped datas (No Sub Report)

The Setup was created using Windows98 OS and Application running in Win 2000 and XP. When we click the source file then Error source coming as mscvrt.dll

I have the screen shot of report and Error message can send u on Request

So Kindly help me to solve this problem very Imp One Please help me

T.Rajasekaran
Removed By Mod

While Passing Date For The Year 2002 Report Is Shown And Not Displayed For 2003 Help Pls!
Hi freinds,

 My form looks like this, it will have two textbox for passing date , a combo box for Selecting the Product Name, and a command button to show the report and some option buttons. name of the Optionbutton is optIwise.
Everything works correct only for 2002 and not for 2003. I don't know what's the problem. when i see in the database the records are there for this year , I temporarily put a MSH Flex Grid and try to display records for this year and last year but in the my original project, the records are not shown in the report only for this year. I also refer the date passing through the break point, everything is going correct, but atlast, the output for the year 2003 is not coming.

I am using Sql version 6 as the Back End

In strSelectionFormula i am using the date format like this, Format(fmd, "YYYY,M,D"), if I change this format, it shows error, Only in this format I am able to run the program, but this format is working for the last year 2002. I think it is the default format to pass date to crystal report, if it is wrong suggest me.

ST030100.ST03015 is the Date field and ST030100.ST03018 is the Product name displayed in combo box

Dim strSelectionFormula As String
Dim fmd As Date
Dim tod As Date
Dim t As Date

Private Sub Form_Activate()
On Error Resume Next

 Text1.Text = "01" & Format(Date,"MM/YY")
 Text2.Text = Format(Date, "DD/MM/YY")

 CrystalReport1.Connect = "DSN=scalaDB;UID=scala;PWD=scala"

 Set cnn = New ADODB.Connection
 Set rs = New ADODB.Recordset
 Set rspt = New ADODB.Recordset

While Not rspt.EOF
 cmbProd.AddItem rspt!ST03018
 rspt.MoveNext
Wend
 cmbProd.AddItem "ALL"
End Sub

Private Sub Command1_Click()

 fmd = Text1.Text
 tod = Text2.Text

If Not optIwise.Value Then Exit Sub
 CrystalReport1.ReportFileName = App.Path & "/product.rpt"

If cmbProd.ListIndex = cmbProd.ListCount - 1 Then
 strSelectionFormula = "{ST030100.ST03015} in Date(" & _
 Format(fmd, "YYYY,M,D") & ") to Date(" & _
 Format(tod, "YYYY,M,D") & ")"
 CrystalReport1.ReplaceSelectionFormula (strSelectionFormula)
 CrystalReport1.Action = 1
Else
 With CrystalReport1
 strSelectionFormula = "{ST030100.ST03015} in Date(" & _
 Format(fmd, "YYYY,M,D") & ") to Date(" & _
 Format(tod, "YYYY,M,D") & ")"
 CrystalReport1.ReplaceSelectionFormula (strSelectionFormula)
 CrystalReport1.GroupSelectionFormula = "{ST030100.ST03018}='" & cmbProd.Text & "'"
 CrystalReport1.Action = 1
 End With
 CrystalReport1.GroupSelectionFormula = " "
End If
End Sub

Kindly reply me and suggest me how to solve it, if its possible correct it.

Thank you very much,
Chock.
itchocks@rediffmail.com

<HTML>
HAVE A NICE DAY !
<HTML>

Edited by - chs on 1/8/2003 1:04:48 AM

Export MS Access 2002 Parameter Report Using VB6

Can some one help me to export MS Access 2002 parameter report using VB6.I have created parameter report in Access now i want to export this report using VB6.if you open report will ask you 2 dates to enter then will print or export.please help me.

Can some one help me to export MS Access 2002 parameter report using VB6.

I have created parameter report in Access now i want to export this report using VB6.
if you open report will ask you 2 dates to enter then will print or export.

please help me.