If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parameter Prompt Annoyingly Appears (sometimes)
I have a form that I set the SQL query to either
if len(sParam) = 0 then sSQL = "Year=2007" Works fine I get all records for that year If len(sParam) 0 then sSQL = "Year=2007" & " and " & sParam (value= "Year=2007 and salesrep = 'RosieS'") Now I'm prompted to enter a parameter for salesrep - where does that come from? My report has a query that returns all records - no parameters or other criteria. Query... only criteria is that the job ID's exist... SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt], [Main Job Log].[SalesRep], [T_Year].[Year] FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job #]=[T_Year].[IDEnd]) AND ([Main Job Log].[Job #]=[T_Year].[IDStart]) ORDER BY [Main Job Log].[Job #]; ....end query ...frmReport snipit.... sReportName = sRptName sWhereFieldName = Me.lbl_WhereFieldName.Caption sWhereFieldValue = Me.cbo_RptOptionList.Value If Me.cbo_Year.Visible = True Then sYear = "Year=" & Me.cbo_Year.Value Else: sYear = "" End If If Me.cbo_RptOptionList.Value = "_All" Then sWhere = "" Else: sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'" End If Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2)) Case 3 sQry = sYear & " and " & sWhere Case 2 sQry = sWhere Case 1 sQry = sYear Case 0 sQry = sWhere End Select DoCmd.OpenReport sReportName, acViewPreview, , sQry ....end snipit.... |
Thread Tools | |
Display Modes | |
|
|