Results 1 to 3 of 3

Thread: Charts

  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: Charts

    I have a chart based on a query that summarizes efficiency % by shift by day. If have a date in the query criteria ( between #04/01/06# and #04/30/06#) the chart works.

    The problem is I need to easily change the date. If I try to have a user interface ( [Start Date] ) I get a Jet dbse error does not recognize field name. And no chart.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It's tricky to get the syntax correct when applying a date to various things. Sounds like you are trying a parameter query.

    Don't apply the date to the query - have the query give you all the data and apply the filter in the report that contains the chart.

    The filter itself is a STRING, but you want Access to know it has to treat the value of the date as a DATE/TIME. To accomplish this, you convert the date to a string and then enclose it in pound signs.

    Example:
    strFilter = "[StartDate] > #" & Format(Me.txtStartDate) & "#"

    If you still want to apply the date to the query, I suggest using a public function in the query, where the function grabs the date from your form (easier to debug and edit than placing an explicit reference directly in the query).

    tc

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Persoanlly I'd disagree, apply the date filter at the query level, get into good SQL habits early. make sure your date column is indexed. if you use SQL on a server based data store then you wnat to send only as much data is required to the client, ok its true that isn't the way JET works but I'd strongly reccommend that you always design as if you are using a server and dump much of the data selection and manipulation into the SQL and leave the presentation layer to do as little as possible with as little as possible data.

    your original error could be because you didn't bracket the date with the hash symbol #, and or format the date in US style eg mm/dd/yyyy

    its perfectly possible to pull dates from a form or request them form a user. Generally I'd suggest pulling them form an intermediate form, esepcially if these dates are used in several reports, or you may have to run thge same report several times. The syntax is =forms!myformname!mycontrolname
    The reasons
    1) you can validate the date(s) is correct in a form, its harder in a parameter query
    2) you users only have to put in the date once, so there is less risk of typos, and or users getting annoyed because they are typing in the same !!!!!! dates
    3) you can offer suggestions based on the current date (eg set as defaults the previous period end dates

    just a thought.....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •