Chart and Graph in Access 2000 1

[482570]

Anyone has worked on chart and graph in Access?
I'm looking for sample or artical in detail and I have not located yet. error message "MS jet database engine doesn't reconize "[Name]" as a vaild field name or expression"
This only occur when I used date range in the form or direct input. Is anyone have any good reference where to look for?

 

[fneily]

Can you supply your code. Are you using a parameter prompt? A date from a textbox? Etc. What is the source of your chart? - be very specific. Since it's not clear, remember when you use dates, they must have #'s around them for VBA to recognize it has a date. So if you have any SQL statement, the date would look like #1/1/08#. Just 1/1/08 would give an error.

 

[482570]

I try to generate chart base on user input such as date range and others.

I created query and Date range statement in query as “Between Date()-120 and Date()” or “Between #11/1/2007# and Date()” then working just fine.
If I use Parameter prompt, query itself working fine but either report or form for chart is given and error message.
Cross Tab also same problem chart form or report.
Look like error from user selection and I don’t know where to look for. I been working on for last few weeks I have not located any reference for this situation.

I don’t use any vb codes, I only use chart wizard and expression like stated above.

 

[PHV]

What is the EXACT wording of the error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

First of all, IMHO, you should never use parameter prompts as per faq701-6763. Then, you must declare the data types of all parameters for crosstab queries. Charts quite often use crosstabs as their Row Source.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[fneily]

dhookom is correct. In design view of your query, click the menu item Query - Parameters. Enter your parameter prompt EXACTLY as you have it in the query (with the [ ]'s). Then on the right select Date/time. Now when you open your form, it'll ask for a date and translate correctly.

 

[482570]

I still can’t understand why is not working.
Here’s SQL statement I use and when I use form, error message as

“Microsoft database engine does not recognize “[Forms]![frmReporting]![txtDate1] And (tblrun.Date)<=[Forms]![frmReporting]![txtDate2]” as valid field name or expression.”

TRANSFORM Sum(tblrun.int) AS SumOfint
SELECT tblrun.intWeekNumber
FROM tblrun
WHERE (((tblrun.Date)>=[Forms]![frmReporting]![txtDate1] And (tblrun.Date)<=[Forms]![frmReporting]![txtDate2]))
GROUP BY tblrun.Date, tblrun.intWeekNumber, tblrun.GeneralCode
PIVOT tblrun.Shift;

But if use following method, working just fine.

TRANSFORM Sum(tblrun.int) AS SumOfint
SELECT tblrun.intWeekNumber
FROM tblrun
WHERE (((tblrun.Date) Between #11/1/2007# And #12/31/2007#))
GROUP BY tblrun.Date, tblrun.intWeekNumber, tblrun.GeneralCode
PIVOT tblrun.Shift;

Any idea?

 

[dhookom]

482570,
[red]Did you read the last two replies to you?[/red]

If you didn't understand the suggestions then tell us what isn't clear to you.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

PARAMETERS [Forms]![frmReporting]![txtDate1] DateTime, [Forms]![frmReporting]![txtDate2] DateTime;
TRANSFORM Sum(tblrun.int) AS SumOfint
SELECT tblrun.intWeekNumber
FROM tblrun
WHERE tblrun.Date Between [Forms]![frmReporting]![txtDate1] And [Forms]![frmReporting]![txtDate2]
GROUP BY tblrun.Date, tblrun.intWeekNumber, tblrun.GeneralCode
PIVOT tblrun.Shift;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[482570]

Thanks a lot PHV. Work like charm. Thanks milion's for your sample and direction.