I wrote this routine awhile ago when there were only 1 or 2 inputs that were
needed. Now, there are many and I'd like to modify the code to add an
optional parameter to specify the criteria for a where clause to a query.
I'm not sure the best way to do it though. Here is the routine now
Sub CreateBlotter()
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim t As String, t2 As String
Dim sLine As String, sPath As String
Dim sSymbol As String, sBlotter As String
Dim prm As Parameter, qdf As QueryDef
Dim lAns As Long
t = ","
t2 = ",,"
sPath = GetPath(CurrentDb.Name)
sBlotter = sPath & "taxlots.trn"
If bFileExists(sBlotter) Then
lAns = MsgBox("The file " & sBlotter & " already exists. Do you want to
overwrite it?", vbYesNoCancel, "Existing file")
Select Case lAns
Case vbYes: Kill sBlotter
End Select
End If
'Initialise
Set dbD = CurrentDb()
Set qdf = dbD.QueryDefs!DataForExportFile
qdf.Parameters![Enter Portcode] = InputBox("Enter Portcode")
Set rsR = qdf.OpenRecordset(dbOpenSnapshot)
With rsR
If Not .EOF And Not .BOF Then
Do While Not .EOF
If .Fields("sec type") > 4 Then
If Not IsNull(.Fields("cusip")) Then
sSymbol = .Fields("cusip")
Else
sSymbol = ""
End If
Else
If Not IsNull(.Fields("Primary Symbol")) Then
sSymbol = .Fields("Primary Symbol")
Else
sSymbol = ""
End If
End If
sLine = .Fields("portcode") & t & "li" & t2 & .Fields("sectype") & t
& sSymbol & t & _
FixDate(.Fields("settle Date")) & t2 & FixDate(.Fields("Trade
date")) & t & .Fields("Qty") & String(9, t) & .Fields("MktValue") & t & _
.Fields("OrigCost") & String(10, t) & "n" & t & "65533" &
String(12, t) & "1" & t2 & t & "n" & t & "y" & String(13, t) & "y"
WriteFile sBlotter, sLine
'Debug.Print sLine
.MoveNext
Loop
Else
MsgBox "No Records found for account."
End If
.Close
End With
Set rsR = Nothing
Set dbD = Nothing
End Sub
What I want to do is add in an optional string parameter to the routine like
Sub CreateBlotter(optional sPortCodes as string)
and check if Len(sPortcodes) = 0 run as it is now but if it's populated I
want to add whats in it to a where clause for the querydef. I'm not sure of
the best way to achieve it. I'm not sure if I should use a Filter or if I
should dynamically create the SQL string for the querydef. Here is the base
query
sSQLDataForExportFile = "SELECT PPES.Portcode, [Security
Descriptions].CUSIP, SecTypeXref.SecType, [Security Record A].[Primary
Symbol], [Cost]/100 AS OrigCost, " & _
"[Security Descriptions].Price, PPES.[Trade Date], PPES.[Settle
Date], [Quantity]/100000 AS Qty, PPES.NetAccInt, PPES.GrAccrual, " & _
"IIf([Security Descriptions]![Sec
Type]<'5',[Price]*[Qty],[Price]*[Qty]/100) AS MktValue, [Security
Descriptions].[Sec Type] " & _
"FROM [Security Record A] INNER JOIN ((PPES INNER JOIN [Security
Descriptions] ON PPES.CUSIP = [Security Descriptions].CUSIP) " & _
"INNER JOIN SecTypeXref ON ([Security Descriptions].[Sec Mod] =
SecTypeXref.P_Sec_Mod) AND ([Security Descriptions].[Sec Type] =
SecTypeXref.P_Sec_Type)) " & _
"ON [Security Record A].CUSIP = [Security Descriptions].CUSIP WHERE
(((PPES.[Trade Date])<>0) AND ((PPES.[Settle Date])<>0));"
any recommendations?
TIA
Matt
|
Similar Threads
