Replies

GOT IT!!!! I finally cut and pasted your code in and it ran. I can’t thank all of you enough.

Have a grand day!

Leesha

Hi Hans,

I tried something similar but noticed the following. First, the original form was unbound as were the two input boxes. The code ran as follows:

Dim ReportName
ReportName = “rptClientInitial-CopyInvoice”
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=acViewPreview, _
WhereCondition:=”[Date] BETWEEN #” & _
Me.STARTDATE & “# AND #” & _
Me.ENDDATE & “#”

When I tried to restrict the report by name and bound the form to the table with the client names, and bound the list to Client Name, I found that entering the code the way and you and Charlotte suggested no longer gives me the ability to find the names startdate or enddate when I type me. There are no start and end dates in the actual tables that are in the query the is bound to the report. The only date is the date entered as the billing date. When running the report I am trying to run the report for a date range that varies from report to report, etc.

Alicia

YAHOO!!!!!!!!!! That did it. I’m so thrilled.

Now, to get the restrictions on dates to work. As I mentioned earlier, I have two unbound list boxes, one labeled startdate and and one labeled enddate to restrict the time frame for the report. I tried the AND statement that Charlotte mentioned earlier but got errors. Any suggestions?

Alicia

Ugh, I’ll admit with all the changes I didn’t declare strwhere, but added it and still receive the syntax error as written above. I don’t understand it. When I yank the code that refers to the docmd to open the report, I don’t get a blow up and the immediate box shows that the correct name is found. If I take out the ,,strwhere at the end of the code used to open the report, the report opens, but all clients are in the report.

Alicia

I tried adding the column piece and now at least in the debug screen it shows the name vs the ID number so we’re making progress! (Thank you for your patience). I am however still getting a syntax error and when I go to debug the portion hightlighted is as follows:

DoCmd.OpenReport “rptClientInitial-CopyInvoice”, acViewPreview, , strWhere

The entire code is as follows:

strWhere = “[Client Name] = ‘” & Me.List2.Column(1) & “”
Debug.Print strWhere
DoCmd.OpenReport “rptClientInitial-CopyInvoice”, acViewPreview, , strWhere

Thanks,
Alicia

I am trying to restrict the report by client name not ID. The SQL behind the row source reads as follows:

SELECT tblClientDemographics.ID, tblClientDemographics.[Client Name]
FROM tblClientDemographics
ORDER BY tblClientDemographics.[Client Name];

If I take out “ID” nothing shows in the list box (even after adjusting column settings etc.) If I replace “ID” for “client name” I get a similar error message. RE placing backets around Client Name when I did this prior to posting, the report opens however if it blank without ANY names let alone the name I’m trying to restrict it to.

I’ve removed the date restiriction piece at this point till I get the name piece straightened out but will try Charlottes suggestion after I ge this name restriction down.

Thanks,
Alicia

Hi Hans,

Thanks for the help. Did what you suggested and this is the error message that comes up before I get to the “immediate window”

“Syntax error (missing operator) in query expression ‘(client name =’15’)'”

This is the code its referringto

strWhere = “Client Name= ‘” & Me.List2 & “‘”

This is what is in the immediate window in the visual basic editor:

Client Name= ‘7’

Each time I select a different name from the list box the ID number changes so it is recognizing the change in name. Also, I tried replacing “client name” with “ID” but still get a similar error.

Thanks,
Alicia

Hi Charlotte! Thanks for the post. I tried the code you suggested but am still running into an issue. First, yes it was a list box on a form that I am referring to. The form is bound to tblClientDemographics and the list is bound to control source “ClientName”. I entered the code as follows:
strWhere = “ClientName = ‘” & Me.List2 & “‘”
DoCmd.OpenReport “rptClientInitial-CopyInvoice”, acPreview, , strWhere
My problem is that the report still opens showing all of the clients in the table.

In addition to needing the report to open by client name, I need the report to open by a From and To date range. I have two input boxes on the form, one labeled “startdate” and one labeled “enddate”. I tried taking the code out for the dates to be sure that the report first opened by cient name but that didn’t help. The code I’m using for the dates is as follows:

Dim ReportName
ReportName = “rptClientInitial-CopyInvoice”
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=acViewPreview, _
WhereCondition:=”[Date] BETWEEN #” & _
Me.STARTDATE & “# AND #” & _
Me.ENDDATE & “#”

This code works fine.

Any suggestions would be greatly appreciated!!

Thanks again,
Leesah