Report based on a Query

[usvista]

I just a built a form with 2 set of combo boxes. Both are going to be parameter cbo. ie. cbo1From, cbo1To, cbo2From, and cbo2To.

It's a simple form where user selects 1 set or 2 sets of cbo box and when they click a command button, it will pop up a report based on their selection.

One problem. The query won't work.

here's the query i have. (i have tried so many differet ways too..)

SELECT tbl_MasterPM.Pmname, tbl_Rperiod.BqtrName, Sum(Tbl_PmTranx.Pmamount) AS SumOfPmamount, tbl_Rperiod.Rperiod, tbl_MasterPM.Pmid
FROM tbl_Rperiod INNER JOIN (tbl_MasterPM INNER JOIN Tbl_PmTranx ON tbl_MasterPM.Pmid = Tbl_PmTranx.Pmid) ON tbl_Rperiod.Rperiod = Tbl_PmTranx.Rperiod
WHERE (((tbl_Rperiod.Rperiod) Is Null) AND ((tbl_MasterPM.Pmid) Between [forms]![frm_PMPP]![cboPMfrom] And [forms]![frm_PMPP]![cboPMto])) OR (((tbl_Rperiod.Rperiod) Between [forms]![frm_PMPP]![cboQfrom] And [forms]![frm_PMPP]![cboQto]) AND ((tbl_MasterPM.Pmid) Is Null))
GROUP BY tbl_MasterPM.Pmname, tbl_Rperiod.BqtrName, tbl_Rperiod.Rperiod, tbl_MasterPM.Pmid;


where cause is where i have a problem (atleast i think).
If i only do one of the parameter, Between [forms]![frm_PMPP]![cboQfrom] And [forms]![frm_PMPP]![cboQto], it works fine. but I need to set it so that the user can either select 1 set of combo boxes or both.

I know it's a long sql code but can anyone help me out?

so pretty much it goes down to making a query ask for few inputs...
thanks inadvance

 

[dhookom]

Since your report's record source contains both fields, I would remove all dynamic criteria from the query and use the WHERE CONDITION in the DoCmd.OpenReport method. Assuming both fields are numeric, try:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboPMFrom) Then
    strWhere = strWhere & " AND Pmid >=" & Me.cboPMFrom
End If
If Not IsNull(Me.cboPMTo) Then
    strWhere = strWhere & " AND Pmid <=" & Me.cboPMTo
End If
If Not IsNull(Me.cboQFrom) Then
    strWhere = strWhere & " AND Rperiod >=" & Me.cboQFrom
End If
If Not IsNull(Me.cboQTo) Then
    strWhere = strWhere & " AND Rperiod <=" & Me.cboQTo
End If
DoCmd.Openreport "rptYourReport", acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[usvista]

they're actually text field.. do i just need to add '?

 

[usvista]

I just changed it to number field but im still getting a run time error '3615' Type mismatch in expression.

when i click debug, doCmd.OpenReport "rpt_PmbyPerfIDperiod", acPreview, , strWhere

is highlited in yellow.

what does this mean?

 

[dhookom]

What are your field types for Pmid and Rperiod and what is your exact code? Does either field contain embedded quotes?

Why would you change a field type? It is much easier to change the code.

Duane
Hook'D on Access
MS Access MVP