where argument of OpenReport

[Janet Thomas]

I am opening a report from a command button on my form. (I use this same report from several different forms so I don't want to refer to the form in the query in the report.) My report is only based on one table - called "ICT Status". I thought this was simple - just put the where clause in the OpenReport for the event procedure for the command button. So I have this:

stDocName = "ICT Status Print Page"
DoCmd.OpenReport stDocName, acPreview, , [ICT Status.Dfee Number] = Forms![ICT Status Page 1]![Dfes]

However, I can't get this to work. My report returns every record in the ICT Status table. No errors or anything - just seems to ignore it. Can anyone tell me what I'm missing?
Thanks..

 

[cogent1]

What is [ICTStatus.DFee number] referring to?

 

[Janet Thomas]

Sorry for not saying...
It refers to a field in the table which the report it using. (ICT Status is the table, Dfee Number is the primary key for that table). I don't use a query in the Report - just the table ICT Status.
Also, in my attempts to figure out what is going on, I've gone into design view when my report previews with all the records. The filter field on the report properties has (-1) in it. I put in a message box and have confirmed that my form field still have "2323" in it so does this (-1) in the filter mean anything? I haven't used a filter in the report.

 

[cogent1]

Put a breakpoint on the DoCmd.Open Report line of your code.

When the code breaks, inspect the values of the criteria on both sides of the WHERE clause by hovering the cursor over them.

Report exactly what they are and we'll take it from there.

PS- you shouldn't need the full syntax of the field name if only one table is involved. Just [DFee Number] should suffice.

 

[Janet Thomas]

I put a breakpoint on the DoCmd line. If I hover over parts of it like AcPreview, it shows me "AcPreview=2". But hovering over the where part doesn't do anything. So I set a watch on [ICT Status.Dfee Number]. It has the value I expected in it (in this case 5202). But the report still returns all records rather than just 5202. Just for fun, I tried a different field on the table (not a primary key field) but it didn't work either. It appears that the where clause is being ignored.

 

[cogent1]

Can you email me the database as a zip file please? Providing the form referred to is open when the code is run, I can see no immediate reason for its failure.

 

[cogent1]

Can you email me the database as a zip file please? Providing the form referred to is open when the code is run, I can see no immediate reason for its failure. (I assume you are using the intrinsic constant acViewPreview and not just acPreview as you have written, by the way). And I also assume that the fields referred to in the WHERE clause are both number types?

 

[Marilor]

Try...

My Access experience is not much, so I don't know if this has anything to do with it, but shouldn't

[ICT Status.Dfee Number] be


[ICT Status].[Dfee Number]

Good luck,
Maria

 

[jadeIT]

Did you figure it out? I am also having a similar problem.

I have a report and want to open it via a button on a form like this:

DoCmd.OpenReport stDocName, acPreview, , "[created] >= # " & [txtStart] & " #"

[created] is a date field in the report
[txtStart] is a text box

In my case the report is ALWAYS blank, regardless of what date you put in.

Any ideas please

 

[Rich]

DoCmd.OpenReport "MyReport", acPreview, "", "[Table/QueryName]![Created]>=[Forms]![MyFormName]![MyDateName]"

 

[jadeIT]

It worked.

I hade to change

DoCmd.OpenReport "MyReport", acPreview, "", "[Table/QueryName]![Created]>=[Forms]![MyFormName]![MyDateName]"

to

DoCmd.OpenReport "MyReport", acPreview, "", "[Created]>=[Forms]![MyFormName]![MyDateName]"

As it didnt like it for some reason. But that doesnt matter, it works! it works! that was a very fustrating problem. thanks.

 

[cogent1]

in reply to the original post:

Dim stDocName As String
Dim stSQL As String

stSQL = "[DFee Number]=Forms![school information update]![Dfee Number]"

stDocName = "ICT Status Print Page 3"
DoCmd.OpenReport stDocName, acPreview, , stSQL


the above code opens the report correctly.

 

[Janet Thomas]

Strange but true - it works! Thanks so much for your help... would not have tried that!

 

[cogent1]

The reason it works is that a string expression is specified in the syntax, so you have to include quotes in the expression.