Add where clause to querydef?

  • Thread starter Thread starter Matt Williamson
  • Start date Start date
M

Matt Williamson

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
 
Matt Williamson said:
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
Click to expand...

I ended up just doing this. Not sure if it's the best way but it works


sSQL = "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) "


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()

If Len(sPortcodes) = 0 Then
sSQLFilter = "AND (((PPES.Portcode)=[Enter Portcode]));"
dbD.QueryDefs("DataForExportFile").SQL = sSQL & sSQLFilter
Set qdf = dbD.QueryDefs("DataForExportFile")
qdf.Parameters![Enter Portcode] = InputBox("Enter Portcode")
Else
sSQLFilter = "AND ((PPES.Portcode) in (" & addsingleq(sPortcodes) &
"));"
dbD.QueryDefs("DataForExportFile").SQL = sSQL & sSQLFilter
Set qdf = dbD.QueryDefs("DataForExportFile")
End If

Set rsR = qdf.OpenRecordset(dbOpenSnapshot)
 
Back
Top