05-05-06, 15:39 #1Registered User
- Join Date
- May 2006
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.
05-05-06, 16:09 #2Registered User
- Join Date
- Apr 2004
- outside the rim
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.
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).
05-06-06, 03:12 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
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