Macro Help - Openreport Method passing multiple promp val syntax error

[odrocs]

Hi All,

I'm at a loss... Below is my macro which for the most part works great.

Sub Main()
Dim objImpApp As Object
Dim objImpRep As Object
Dim objImpCat as Object
Dim strPredicate as string
Dim strTextFileName As String
Dim intIndex As Integer
Dim intFileNumber As Integer
Dim intCounter as Integer
Dim strEntry As String
Dim StartDate
Dim EndDate
Dim ListValues$()
Dim intNumber as Integer

StartDate = "2007-01-15"
EndDate = "2007-12-30"

Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.OpenCatalog "C:\Documents and Settings\mscordo\Desktop\Loan_Servicing_v7.3.cat","BNK 280","password","userid","password"
Set objImpRep = objImpApp.OpenReport("C:\Documents and Settings\myname\Desktop\Cognos Reports\Dealer Name.imr")
strTextFileName = "DealerNames"
objImpRep.ExportText strTextFileName & ".txt"
objImpRep.CloseReport

'initialize variables
intFileNumber = FreeFile
intIndex = 0
intCounter = 0
Redim ListValues$(intIndex)

Open "C:\Program Files\Cognos\cer4\samples\Impromptu\reports\DealerNames.txt" For Input As #intFileNumber
'read in every line until end of file
Do Until EOF(intFileNumber)
Line input #intFileNumber, strEntry
If intCounter < 2 Then 'first two values not needed
'put nothing in the array
Else
strEntry = mid$(strEntry, 1, 35)
strEntry = RTrim(strEntry)
intNumber = Len(StrEntry)
strEntry = mid$(strEntry, 1, intnumber)

'place entry in the array
ListValues$(intIndex) = strEntry
intIndex = intIndex + 1
Redim Preserve ListValues$(intIndex)
End If
intCounter = intCounter + 1
Loop
Close #intFileNumber
Redim Preserve ListValues$(intIndex - 1)
For intIndex = 1 to 6
strEntry = ListValues$(intIndex)
Set objImpRep = objImpApp.OpenReport("C:\Documents and Settings\myname\Desktop\Cognos Reports\Score Card.imr",StartDate)
objImpRep.Visible True
'objImpRep.Print 1,1,1
objImpRep.CloseReport
next
ObjImpApp.Quit
Set objImpRep = Nothing
Set objImpCat = Nothing
Set objImpApp = Nothing
End Sub


So the above code works perfect but I want to pass three promptvalues to the report. 1. StartDate 2. EndDate 3. StrEntry.
This line of Code is the issue:
Set objImpRep = objImpApp.OpenReport("C:\Documents and Settings\myname\Desktop\Cognos Reports\Score Card.imr",StartDate,???,????)

I have tried every combination and keep getting a syntax error.

Please help,

Marc

 

[odrocs]

Couple more things:

I am using Cognos Impromptu Version 7.3.648.0

The syntax I can't seem to figure out is below:

Syntax
Application.OpenReport(FileName ,["PromptValueA1[, PromptValueA2, …][|PromptValueB1[,
PromptValueB2, …]]..."])

 

[odrocs]

I am getting this error message a lot.

Error number -239:

DMS-E-GENERAL, A general exception has occurred during operation 'prepare request with options'.
DMS-E-SS_SYNTAX, A syntax error was detected near 'TIMESTAMP'.

 

[drlex]

odrocs,
To pass multiple parameters to an Impromptu report by way of a macro, use the pipe ('|') as a delimiter.
In your case, StartDate + "|" + EndDate + "|" + StrEntry.

I notice that you've put

Dim StartDate
Dim EndDate

Perhaps

Dim StartDate, EndDate as String

would be better, especially in light of your syntax error relating to timestamp. It may be that your report is expecting a date-time entry at the prompt and you're only supplying a date.

soi la, soi carré