filter outside given range 1

[trustsun]

I am filtering a report by date range. At times there’s no data for a particular field when I type in a range, then I get the error, “Jet database engine does not recognize “field” as valid name or expression”. Is there a way could I by pass this even if I filter outside the field range with out the data? This way it will still show the field but without any data.

 

[dhookom]

Is your record source a crosstab query? That is the only way that I have seen that message. If a record source doesn't return any records then you might see #Error in text boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[trustsun]

Yes,it is a text box. Thanks for responding. Any ideas or revision to by pass the error?

 

[dhookom]

If the record source is a crosstab, please provide some information such as the SQL view. If it's not a crosstab, provide more details about your error and where it shows up.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[trustsun]

Yes I meant to say crosstab, here's the SQL:

PIVOT Switch([ACTIONS] Like "[ADD-LAD]*","NEW",[ACTIONS] Like "[CHG-LCH]*","CHANGE",[ACTIONS] Like "[PDL]*","DELETE",True,"Other");

On "other" is where the error occurs. Just like you said, there's no records. The only time it will filter or show data if I select the date with the "other" field. Even when I change True to False the errors still shows when executed.

 

[dhookom]

Try use the Colulmn Headings property with sql like:

PIVOT Switch([ACTIONS] Like "[ADD-LAD]*","NEW",[ACTIONS] Like "[CHG-LCH]*","CHANGE",[ACTIONS] Like "[PDL]*","DELETE",True,"Other") IN ("NEW","CHANGE","DELETE","OTHER");

Are you actually storing the []s in the field values. The Switch works fine but requires maintaining of expressions if you ever modify the ACTIONS. I would probably add these values to data.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[trustsun]

Thanks, it works now. Could you explain what you did by adding the *. This helps me from asking the same question if this issue comes up again, that way I understand.

 

[dhookom]

I didn't add "the *". I only copied your pivot and added values that would go into the Column Headings property of the crosstab query. This will create columns whether there is data or not available. If you pivot statement was:

PIVOT Switch([ACTIONS] Like "[ADD-LAD]*","NEW",[ACTIONS] Like "[CHG-LCH]*","CHANGE",[ACTIONS] Like "[PDL]*","DELETE",True,"Other") IN ("NEW","CHANGE","DELETE","OTHER","ANOTHER");

you would see a new column "ANOTHER".


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]