use variable in Docmd.openreport

[pdtit]

Hey,

I've been searching already quiet some time to do the following :

I want to open a somehow same report for 3 different locations. The location is chosen from a selection list on a form.

I have the following code behind my "show report"-button

Docmd.openreport "total sales & me!sellocation",...
where me!sellocation is the selection list on my form.
The issue I have is that everything between "..." is interpreted as text, and the variable is not read.
I also tried something like ""total sales " & "me!sellocation"", but then only "total sales " is read.

I was wondering if anyone knows if I can use variables at all using the docmd.openreport.

Regards,

Peter

 

[PaulBricker]

Assuming that "Total Sales" is the name of the Report then it would look something like this

Docmd.openreport "total sales",acPreview,, "[SellLocation]= '" & Me!sellocation & "'"


Paul

 

[VicM]

pdtit:

Your command string should be:

Docmd.openreport "total sales" & me!sellocation.value,...

me!sellocation.value is a variable, by placing it inside the quotes (along with the concatenation operator &) you have created the string that you are seeing. Since you don't have a report named [total sales & me!sellocation], the command fails.

I always like to identify to the system that I want the value being held by the control and not the control itself. Code often works without the .value, but I've found it safer to use it.

Also, the concatenation operator, &, should never (well almost never) be placed inside of quotations marks.

You can test if the created report name is identical to the real report name by doing the following:

strName = "total sales" & me!sellocation
msgbox strName

If your location is Location101, then this contruct will produce: total salesLocation101.

Remember that the string you create must be identical in spelling, spacing and case, to the real name of the report.

Hope this helps,

Vic

 

[pdtit]

Vic,

I tested it out and it works great.

Damn that stupid .value word costs me one hour of time.

Many thx for the info, and the speed in replying.

Regards,

Peter