Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2013
    Posts
    5

    Unanswered: New Query hates old date formula & new yr-to-date

    Here's a good one for you!

    I've added several fields to a straight forward Access dB with a single data table. Once the new fields are added, Access decided it doesn't like a formula in an unrelated date field and I get an error message that says "the expression you entered has an invalid date value" when I go to save it.

    Query runs fine without the new fields, there is no validation rules on the field, and the original formula was not touched. The only changes made to the query were a few new fields (true/false value, default settings). In all cases there is no relationship between the problem date field where the formula lives or the fields that were added.

    Thought I had a fix from another forum where two new formula's were suggested. Problem is, the suggestions worked with some queries and not others.

    Here's the query setup: I have sales status reports per principles (product line) that list each customer for that principle in ascending order, they are grouped by month. Amongst other fields displayed are sales and 2 commission amounts. The sales, commissions amounts are listed & totaled by each month per customer and grand totaled at the end of the report.

    A status is either booked (b), forecast (f), shipped (s) or closed (c). The status isn't displayed because its the name of the report. The original query criteria is as follows, (runs fine displaying running totals):

    customer status sales_comm(company) rep_comm(sales rep) book_date (substitute bill_date/revenue_date in those reports)
    "b" >=#Sun Jan 01 2010# And <Date()+30

    My [report] output is attached, I posted it here but it garbled.

    For my new reports I added the following fields to my query: model/product description, revised quote number. My updated report with the and the model/product description, revised quote number fields should look the same as the original report but begin with Booking Cycle: month for a year earlier (in this case, June 2012). For the Forecast sales/billing report I should have results from Forecast Cycle June 2012 thru Dec 2014.

    Instead, as soon as I add those fields, I got: "the expression you entered has an invalid date value", and could not save the query. In using alternate formulas, I still get nothing in some of my reports.

    Some of my correction attempts and recommendations include the following:

    >="Sun Jan 01 2012" And <Date()+30
    which returns this error message
    "this expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

    or
    >= DateSerial(Year(Date()), 1, 1)
    Between DateSerial(Year(Date()),Month(Date()),1) And Date()-1
    or
    Between CDate("1/1/2012") And Date() + 30
    or
    Between CDate("1/1/2012") And DateAdd("d", Date(), 30)
    instead of >="Sun Jan 01 2012" And <Date()+30
    or
    Between DateAdd("d", -730, Date()) And DateAdd("d", 730, Date())
    instead of >(date()-730) and <date()+730
    or
    >= CDate("1/1/2010")
    instead of >="Fri Jan 01, 2010"


    Here's my problem:
    I have 16 reports/queries, about 5-6 of them return no results with the new formulas when I know I should have at least 10 records populate each report.

    Nothing in the query has been modified except adding the new fields, the status value was unchanged; its the book/bill/or revenue date field that determines if records are displayed or not because the status field is constant and is never modified.

    What I need:
    (a) the report displays a running total, the new formulas don't error out but don't return any data, I need it to return data
    (b) I need the report to display year-to-date totals, but in csae someone needs a running total (for historical purposes), I need a furnish them with a formula they can use to do a custom query becasue their original formula no longer works.

    Report output attached.

    Thanks in advance for the help and the fresh eyes!
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dates should be specified as either ISO format YYYY/MM/DD or US format MM/DD/YYYY
    and be enclosed in hash symbols
    eg WHERE SaleDate BETWEEN #2013/01/01# AND #2013/03/31#

    you are mixing datatypes
    cdate returns a datetime value so whoudl work with dateadd
    date()+30 is a nasty workaround, you should use the datetime functions to protect yourself against changes of implementation of dates. this may be a problem in the future in JET(although the way of stroing dates hasn't changed since V1 of Access/JET), it may be a problem if you switch to a SQL Server backend, it WILL be a problem if you switch to some other backend data storage mechanism that doesnt' do things the Access/JET way
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Posts
    5
    hey, thnx a mil, i really appreciate the help. i've been away from this for ages, wasn't even supposed to be dong this, was recommend by a colleague to convert a legacy access db to v2010 then publish to sharepoint. the conversion was fine, all their reports ran ok no issues, hiccups, anything.

    of course after the conversion was completed they decided they needed all these add-in's -- new fields, totally redesigned main menu, new reports -- not what i signed up for but i'm already in and they want this before publishing to sharepoint. so we have what was supposed to be a 1 -2 week project still going 3.5 months later and now they are putting the breaks on due to the added time and costs.

    so i'm doubly frustrated because i haven't done db programming since grass was dirt, web/html/css programming's much easier. i did db only to backup my staff when needed and make repairs. these query formulas are my only stumbling block with this project an unforeseen obstacle to something i'm not supposed to be doing in the first place. helping sometimes is more trouble than itw worth, this is one of those times.

    their queries were all running totals, NOW they want their totals to be either year to date or current month forward. but i also need the running total formula to work because now that its gone from the padded reports, someone will invariably want to do a customer report with running totals, and now they will have to run a custom query to produce it. these folks are scattered around the world (hence sharepoint solutions), so i'd like to give them what they want even though i'm not getting paid for all the hours i've put in.

    so, the $64,000 question, what formula would you use in a date field to get "year-to-date" and "current month forward"?

    TIA

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    "Year to date" depends on the start date for your reporting year. Similarly, "current month forward" depends on whether you want calendar months, financial periods or a rolling number of weeks. (Yes, I've worked for places with weird reporting/accounting requirements. Crashing on.)
    Assuming that YTD is for the current calendar year:
    Code:
    [DateField] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
    Please remember to replace [DateField] with the name of your actual date field! If your YTD runs from an accounting start date, I might be able to help - I've modified some VBA code that generates the ISO year start date to work for my current employer's financial year (with exceptions).
    If you want the current month forward, the following air code makes these assumptions:
    • You want the whole of the current month
    • You want x months into the future
    Code:
    [DateField] BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND DateSerial(Year(DateAdd("m", X, Date())), Month(DateAdd("m", X, Date())), Day(DateAdd("d", -1, DateSerial(Year(DateAdd("m", X + 1, Date())), Month(DateAdd("m", X + 1, Date())),1))))
    Note that if you end up upsizing this database to SQL, you'll need to revisit these queries as SQL uses different date functions.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what I'd do is:-
    for your reports set up a parameter selection form
    then pull those parameters from the form as required (in any form or report)
    that means you have consistent settings if you run a series of reports.

    If you are getting involved in financial year stuff then you almost certainly need a table that identifies financial dates (whether thats weeks/months/periods/years or all of)

    don't be tempted to use magic numbers (eg hardcoded values) as at some stage some allegedly bright spark will change the financial year start date, or want compartives based on 4 week periods or whatever. by setting up a table which maps the financial year/period to an actual date (start or end is irrelevant) you convert the maintenance problem into an opportunity for the aforementioned bright spark to set up their calendar the way they want it. New financial year, well its down to the owners of the data to setup their financial calendar not the developer
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2013
    Posts
    5
    the start date is whatever the report run date is, so when they run a report for year to date (using 6/19/2013 as an example), the yr-2-date report needs to cover from 6/19/2012-6/19/2013.

    the same applies for the current date forward, assuming a rep in amsterdam runs a forecast report today, it pull records from 6/19/2013 forward.

    if his counterpart in colorado ran both reports the day before, his booking report should be from 6/18/2012-2013 and his forecast report should have been from 6/18/2013 forward.

    this database will eventually be published to sharepoint, with the history of this client stretching a 2 week project 3+ months, rest assured i will NOT be doing that project.

    so i need structured formula's consistent with their old formats. they don't have a "go to" and i'm interested in being "IT", they've already consumed several months of unpaid time and they also cost me other work.

    thanks in advance

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Okay, so these are rolling report windows. That makes things easier.
    Rolling year - 1 to now:
    Code:
    [DateField] BETWEEN DateAdd("yyyy", -1, Date()) AND Date()
    Rolling now to month + 1:
    Code:
    [DateField] BETWEEN Date() AND DateAdd("m", 1, Date())
    ...they've already consumed several months of unpaid time and they also cost me other work.
    And you're still there? O.o
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jun 2013
    Posts
    5
    thanks a mil, and i don't work for them, its a consulting contract. they were sent to me by a former client-turned employer from 20 years ago with whom i still keep in contact. she sends me a lot of referral work which has been helpful since layoff #5. unfortunately, i've had to rely on consulting and contract work for the last 6+ years, just trying to get $850 i can afford to miss so i can start a non profit training center.

    i took this conversion/migration project because it was supposed to be straight forward. convert an old access 2000/03 db to 2010, then publish to sharepoint2010. they also asked to do a maintenance contract 20 hours a month just to monitor to make sure they hadn't "brok anything", and post an occasional standardized document. they have no network and have staff that work globally and their db has been sitting on a local desktop since 95. it was updated in 2000 fory2k, then again in 2004 but each time remained local to a single desktop because they have no network. of course they didn't consult with other staff just decided what everyone wanted based on how they were doing things. then the death of a staff member bought everyone together at their htqrs and vioala, they good feedback after announcing what they were doing. hence a 2 wk project turning into 3+ months just on the db.

    this has been too problematic for me, so just to assure i get paid "something" i will be completing the [now] phase 1 part of the project which is the db; they've also since decided to let folks come in town and test drive it for a while before moving on the SP . however, i'll be suggesting someone else continue with the reamining new report, a spreadsheet they want linked to the db but thru SP, and the [now] phase 2 which is the SharePoint implementation. i'm done. out of 108 additional hours, they are only willing to pay for 17 and i'd already spent 50+ this is it. i'm finishing because I gave my word and don't want to walk away with nothing, AND because we belong to the same networkign gorup of 4k+ and I don't want to get blacklisted for pulling out on a project when they are the ones who created the situation we're in.

    what we do to earn a buck!

    thanks again babe, i "REALLY" appreciate it.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Why do I feel like I'm reading something on Clients from Hell?

    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jun 2013
    Posts
    5
    cuz it is...
    back in tha day when i ran a hair salon (college,75-82) we nicked-named ours CFHs. of course they had no idea what we met, and some of them "BEAMED" when we announced, hey "XXXX", your CFH is here!

    lmao!!!!!!!

    now all i need is a regular paying job so i can finance my 501c3!

    have a good one and thanks again SOOOOOOOOOOOOOO very much!

Posting Permissions

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