Correct format for DoCmd.BrowseTo acReport

[Michael Barkemeyer]

Hello,
I have paired down an existing db to be able to attach it here. My issue is that I don't know what the proper syntax is when using DoCmd.BrowseTo acReport that is pulling data from a query containing an Expression. The code that is producing an error is tied to the first DropDown called cboOwner within a subform as part of the Main form.
Thank you for your help.

The SQL for the query that is the record source for the form is:

---

SELECT tblInvoices.InvoiceID, Format([ServiceDate],"mmm yyyy") AS Expr1, Sum(tblInvoices.TotalCharge) AS SumOfTotalCharge, Sum(tblInvoices.TimeMin) AS SumOfTimeMin, tblInvoices.[Project Owner Name], tblInvoices.InvoiceTitle, Format([ServiceDate],"yyyy") AS Expr2, tblProjectOwners.[Project Owner Name], tblProjectOwners.ID
FROM tblInvoices LEFT JOIN tblProjectOwners ON tblInvoices.[Project Owner Name] = tblProjectOwners.ID
GROUP BY tblInvoices.InvoiceID, Format([ServiceDate],"mmm yyyy"), tblInvoices.[Project Owner Name], tblInvoices.InvoiceTitle, Format([ServiceDate],"yyyy"), tblProjectOwners.[Project Owner Name], tblProjectOwners.ID
HAVING (((Format([ServiceDate],"yyyy"))=2020));

The Expression that I am trying to reference in the DoCmd.BrowseTo acReport line is:

---

Expr2: Format([ServiceDate],"yyyy")

Line in code containing syntax error is:

---

Case Not IsNull(cboOwner) And Not IsNull(cboYear)
        DoCmd.BrowseTo acReport, "Year End Invoice Grouping", "Main.NavigationSubform>ReportCenter.NavigationSubform>YEInvoicing.DS", "[tblProjectOwners].[ID]=cboOwner.Value and (((Format([ServiceDate],"yyyy"))=cboYear.Value", "", 1
End Select

 

[CJ_London]

try

DoCmd.BrowseTo acBrowseToReport, "Year End Invoice Grouping", Me.DS, "[tblProjectOwners].[ID]=" & cboOwner '& " and year([ServiceDate]=" & cboYear

Note the year is commented out since it is not populated

also better to use year([ServiceDate]) rather than Format([ServiceDate],"yyyy") AS Expr2 since it is a numeric value - format returns a text value

 

[Michael Barkemeyer]

try

DoCmd.BrowseTo acBrowseToReport, "Year End Invoice Grouping", Me.DS, "[tblProjectOwners].[ID]=" & cboOwner '& " and year([ServiceDate]=" & cboYear

Note the year is commented out since it is not populated

also better to use year([ServiceDate]) rather than Format([ServiceDate],"yyyy") AS Expr2 since it is a numeric value - format returns a text value

Thank you CJ.
I have made the suggested changes to include year([ServiceDate]). But am getting a data type error when attempting to filter by year.

 

[CJ_London]

as I said, you are using the format function in your query which is referenced in your year combo.

also, if you include the year the code can run before you have selected a value - so you need to cater for that eventuality. perhaps it's to show all years, perhaps it doesn't run if a year has not been entered. you need to think it through better

 

[arnelgp]

without using BrowseTo.