Report and Query Parameters

[JohnnyProd]

I'm trying to create a report which accepts a paremeter which narrows the scope of my data. More specifically, when a user runs this report it prompts them for 'mm/dd/yyyy' and uses this info to only display the day he/she is interested in. I have done this in the past by placing a query between the report and table which looks like the following:

SELECT ...
FROM ...
WHERE ... (Format([Field_With_Date],"mm/dd/yyyy&quot)=[mm/dd/yyyy]);

The problem I am having is that when I run the report I am getting the error:

"The Microsoft Jet database engine does not recognize '[mm/dd/yyyy]' as a valid field name or expression"

However, when I run the query iself, I am prompted for 'mm/dd/yyyy' as expected and everything works fine. Any ideas? I'm still new to VBA and I'm not sure if there is a way for me to place parameters in a report, so up till now I have been placing them in a query. Any help is appreciated, thanks.

 

[macgood31]

Try this..

SELECT ...
FROM ...
WHERE ... (Format([Field_With_Date],"mm/dd/yyyy&quot)=[The_date]);

 

[JohnnyProd]

I appreciate the help, but [The_date] is treated no differently than [mm/dd/yyyy]. Is there a simpler way to change the SQL statement inthe report than giving it parameters? I am currently looking into setting the RowSource of the report as I can easily create an SQL String that will work before genereting the report.

 

[macgood31]

hi,If u have enought time, try this
This is the way I do now. But I have 200 report need difference para.
Create a form
place a button.
Insert Button_click()
'If check_valid() = False Then Exit Sub
'Dim MainSQL As String
Dim TEMPSQL As String
Dim a_tmsql As String
Dim Criteria As String, mydb As Database, myset As Recordset, TableHeader As Recordset, FilteredSet As Recordset
Dim A As Integer
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myset = mydb.OpenRecordset("Reports", DB_OPEN_DYNASET) ' Create dynaset.

myset.Filter = "[ReportID] = " & Me![reportList].Column(2)

Set myset = myset.OpenRecordset() ' Create filtered dynaset.

If myset.EOF Then Exit Sub

TEMPSQL = ""
If Not IsNull(myset![selector1]) Then
If selector1 = 1 Then
'
'
Else
TEMPSQL = ("[" & myset![selector1] & "] = " & Chr(39) & Me!selector1Item & Chr(39))
End If
End If
If Not IsNull(myset![selector2]) Then
If selector2 = 1 Then
Else
sql1 = ""
If IsNull(FromDate) Then
sql1 = (&quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot '&&980730
Else
'where [CASHDATE] BETWEEN '&quot; & a_from_date & &quot;' AND '&quot; & a_to_date & &quot;'&quot;
'SQL1 = (&quot;[&quot; & myset![selector2] & &quot;] >= &quot; & &quot;#&quot; & FromDate & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot '&&y2k1005

sql1 = (&quot;[&quot; & myset![selector2] & &quot;] >= '&quot; & FromDate & &quot;' AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<= '&quot; & ToDate & &quot;'&quot '&&y2k1005
End If
'SQL1 = (&quot;[&quot; & myset![selector2] & &quot;] >= &quot; & &quot;#&quot; & FromDate & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot '&&980730

If (TEMPSQL <> &quot;&quot Then
'TempSQL = TempSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector2] & &quot;] >= &quot; & &quot;#&quot; & FromDate & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot ''&&980730
TEMPSQL = TEMPSQL & &quot; AND &quot; & sql1
Else
'TempSQL = (&quot;[&quot; & myset![selector2] & &quot;] >= &quot; & &quot;#&quot; & FromDate & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot''&&980730
TEMPSQL = sql1 ''&&980730
'SQL1
End If
End If
End If
If Not IsNull(myset![selector3]) Then
If selector3 = 1 Then
Else
If (TEMPSQL <> &quot;&quot Then
' If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector3] & &quot;] = &quot; & Chr(39) & Me!selector3Item & Chr(39))
Else
TEMPSQL = (&quot;[&quot; & myset![selector3] & &quot;] = &quot; & Chr(39) & Me!selector3Item & Chr(39))
End If
End If
End If
If Not IsNull(myset![selector4]) Then
If selector4 = 1 Then
Else
If (TEMPSQL <> &quot;&quot Then
'If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector4] & &quot;] = &quot; & Chr(39) & Me!selector4Item & Chr(39))
Else
TEMPSQL = (&quot;[&quot; & myset![selector4] & &quot;] = &quot; & Chr(39) & Me!selector4Item & Chr(39))
End If
End If
End If
If Not IsNull(myset![selector5]) Then
If selector5 = 1 Then
Else
If (TEMPSQL <> &quot;&quot Then
' If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector5] & &quot;] = &quot; & Chr(39) & Me!selector5Item & Chr(39))
Else
TEMPSQL = (&quot;[&quot; & myset![selector5] & &quot;] = &quot; & Chr(39) & Me!selector5Item & Chr(39))
End If
End If
End If
If Not IsNull(myset![selector6]) Then
If selector6 = 1 Then
Else
If (TEMPSQL <> &quot;&quot Then
'If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector6] & &quot;] = &quot; & Chr(39) & Me!selector6Item & Chr(39))
Else
TEMPSQL = (&quot;[&quot; & myset![selector6] & &quot;] = &quot; & Chr(39) & Me!selector6Item & Chr(39))
End If
End If
End If
If Not IsNull(myset![selector7]) Then
If selector7 = 1 Then
Else
If (TEMPSQL <> &quot;&quot Then
'If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & (&quot;[&quot; & myset![selector7] & &quot;] = &quot; & Chr(39) & Me!selector7Item & Chr(39))
Else
TEMPSQL = (&quot;[&quot; & myset![selector7] & &quot;] = &quot; & Chr(39) & Me!selector7Item & Chr(39))
End If
End If
End If
If Not IsNull(myset![selector8]) Then
If selector8 = 1 Then
Else
sql1 = &quot;&quot;
If IsNull(selector8Frm) Then
'SQL1 = (&quot;[&quot; & myset![selector8] & &quot;]&quot; & &quot;<='&quot; & selector8To & &quot;'&quot '&&991214
sql1 = (&quot;[&quot; & myset![selector8] & &quot;]&quot; & &quot;<=#&quot; & selector8To & &quot;#&quot '
Else
'SQL1 = (&quot;[&quot; & myset![selector8] & &quot;] >= &quot; & &quot;'&quot; & selector8Frm & &quot;'&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector8] & &quot;]&quot; & &quot;<='&quot; & selector8To & &quot;'&quot '&&980730
sql1 = (&quot;[&quot; & myset![selector8] & &quot;] >= &quot; & &quot;#&quot; & selector8Frm & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector8] & &quot;]&quot; & &quot; <=#&quot; & selector8To & &quot;#&quot '&&980730
'SQL1 = (&quot;[&quot; & myset![selector2] & &quot;] >= &quot; & &quot;#&quot; & FromDate & &quot;#&quot; & &quot; AND &quot; & &quot;[&quot; & myset![selector2] & &quot;]&quot; & &quot;<=#&quot; & ToDate & &quot;#&quot '&&y2k1005
End If
If (TEMPSQL <> &quot;&quot Then
'If Not IsNull(TempSQL) Then
TEMPSQL = TEMPSQL & &quot; AND &quot; & sql1
Else
TEMPSQL = sql1 ''&&980730
End If
End If
End If
a_tmsql = TEMPSQL
If (Me![reportList].Column(2) = 225 Or Me![reportList].Column(2) = 229 Or Me![reportList].Column(2) = 231) Then
A = Pre_dailyinout(a_tmsql)
B = Pre_dailyinoutHeader(a_tmsql)
's_SQL = &quot;SELECT DISTINCTROW ConfirmShipdate, Buyer, Bene, [SC price], [SC QTY], [SC unit], [SC dely-remark], [SC currency], StyleID, [Buyer PO number], [SC buyingTerm], [SC payment applicant], [SC payment period], [SC payment Ref], [PO number], Fty, [SC dely date], [SC payment Beneficiary], BuyerShortName, SCpaymentTerm, DescrptionShort, [SC number] INTO pendingLC ( ConfirmShipdate, Buyer, Bene, [SC price], [SC QTY], [SC unit], [SC dely-remark], [SC currency], StyleID, [Buyer PO number], [SC buyingTerm], [SC payment applicant], [SC payment period], [SC payment Ref], [PO number], Fty, [SC dely date], [SC payment Beneficiary], BuyerShortName, SCpaymentTerm, DescrptionShort, [SC number] ) FROM ordersPendingLCopendingLinkWithCondate9&quot;
temp_reportName = Me![reportList]
DocName = temp_reportName
DoCmd.OpenReport DocName, A_PREVIEW

End If

temp_reportName = Me![reportList]
DocName = temp_reportName
DoCmd.OpenReport DocName, A_PREVIEW, , TEMPSQL

exit_sub:
Exit Sub

click_error:
Resume Next



End Sub

Private Function check_valid() As Integer

a_check_result = True

If (Me!selector1.Visible = True) And (select1 = 2) And (IsNull(Me!selector1Item)) Then
a_check_result = False: GoTo end_check_valid
ElseIf (Me!selector2.Visible = True) And (Me!select2 = 2) And (IsNull(Me!FromDate) Or IsNull(Me!ToDate)) Then
a_check_result = False: GoTo end_check_valid
ElseIf (Me!selector3.Visible = True) And (Me!select3 = 2) And (IsNull(Me!selector3Item)) Then
a_check_result = False: GoTo end_check_valid
ElseIf (Me!selector4.Visible = True) And (Me!select4 = 2) And (IsNull(Me!selector4Item)) Then
a_check_result = False: GoTo end_check_valid
End If

*******

This is the idea, But It is so generic that allow u add any para (As u can see, there is 5 now in my report selection form.)

Let me know if u need help

Mac

 

[JennyPeters]

I assign the date I want to a string and do this:

Dim SQLText, pstrDate As String

pstrDate = &quot;01/01/01&quot;
SQLText = &quot;SELECT * FROM tblYourTable _
WHERE fldDate = #&quot; & pstrDate & &quot;#;&quot;

DoCmd.RunSQL (SQLText)

Very important that you have the #'s.

Let me know how it works for you.

Jenny