Someone would know

[Mansoor Ahmad]

Here is the statement. Please can you see where I am doing wrong.

DoCmd.OpenReport "R_Fuel parts summary monthwise", acViewPreview, , "[ANALYSIS DATE] = > #12/31/1999# " Or "[ANALYSIS DATE]Is Null" And [Parts type] = " #Fuel# "
OrderBy ([ANALYSIS DATE])

[ANALYSIS DATE] is a date field
[Parts type] is a text field
Want to filter the data by '>12/31/1999' on [ANALYSIS DATE] and
by text 'Fuel' in [Parts type] field

When I click on command button, access prompts as

'Microsoft cannot find the field 'i' referred to in your expression.'


I am not good in VB codes. I have tried it myself but as I do not have clear concept of using '," ,& and other notations so I could not fix it myself.

Thanks

 

[Mile-O]

Double Post

 

[Mansoor Ahmad]

you are right.

Mistakenly I posted it in the wrong forum earlier on. May be that is why I did not get any response.

 

[Mile-O]

I've responded.

 

[The_Doc_Man]

OK, at first glance it looks like you have a fixed query because everything looks like a constant. If that is a side-effect of the way you asked the question, then I've misunderstood.

The EASIEST way to do this is to base the report on a stored query that includes your WHERE clauses with your filtering constraints as constants.

If that is not practical, then your problem has to do with the simple rule that a quote inside quotes has to be doubled to stay as a quote. Also, IsNull is a function. Null is not a constant that you want to use in this context. Finally, I'm guessing that you were playing with syntax in that bit about Fuel. I will assume that you want the literal word 'Fuel' as opposed to a floating point value that is somehow derived from a variable called Fuel.

Having said that, I think the fragment for your filter should look SOMETHING like this:

"[ANALYSIS DATE] => #12/31/1999# Or ( IsNull([ANALYSIS DATE]) And ( [Parts type] = ""Fuel"" ) )"

 

[Mile-O]

[ANALYSIS DATE] => #12/31/1999#

[ANALYSIS DATE] >= #12/31/1999#

 

[IgorB]

"[ANALYSIS DATE] >= #12/31/1999# Or ( IsNull([ANALYSIS DATE]) And ( [Parts type] = 'Fuel' ) )"

 

[Mansoor Ahmad]

Its Monday again and I am back.

Thank you very much for your replies. Just a little problme (I think) is left. I have now put following code but still it prompts to enter parameter values for ANALYSIS DATE and Parts type.

DoCmd.OpenReport "R_Fuel parts summary monthwise", acViewPreview, , "[ANALYSIS DATE]>=#12/31/1999# Or (IsNull([ANALYSIS DATE]) And ([Parts type] = ""Fuel""))" 'OrderBy [ANALYSIS DATE])"

I believe another thing is worth mentioning here. The report which opens has got graph on it based on the filtered data. As I read somewhere that if you have a query that produces chart or graph then you must put the parameter values in Query parameter window. Is that right? If that is the case then I think I will have to create a seperate query for this purpose.

Looking forward to your reply.

 

[Mile-O]

I have now put following code but still it prompts to enter parameter values for ANALYSIS DATE and Parts type.

Let's not forget that it was you who said [ANALYSIS DATE].

Check that it is a field in the RecordSource of the report you are opening.

 

[Mansoor Ahmad]

I had already checked it. The field is there in record source of report.

The full set up of report is as follows

Report is based on a crosstab query.

Cross tab query is based on a select query.

In corsstab query ANALYSIS DATE field is set as column heading in 'mmm-yyyy' format.

 

[The_Doc_Man]

Invariably, when you get that "Enter parameter" dialog box, you have either

1. Spelled the name wrong. Check punctuation like underscores, check spaces, compare spelling, etc.

2. Named an object not in the recordset in the first place. For instance, if you reference a table but the item in question is a calculated field in a query, don't reference the table.

3. Failed to use proper syntax to specify the (data) pathway to the object in question. That space in the field name stands out like a sore thumb. You MUST use brackets on such a field name (which I can see that you do), but look carefully at the item requested in the parameter dialog box. Verify that it is asking for the item with the same spelling.

4. Specified the item in a context where the item's parent collection isn't open. Remember, Access is VERY dynamic. It looks for things WHEN YOU ASK FOR THEM, not earlier.