Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Angry Unanswered: Display records that match the current month

    I'm trying to build a query that will display all records for the current month based on the calculated field [Duedate]

    I have one calculation a date field that calculates a 5 month period between service dates. i have tried month(now()) in the calculated [DueDate] field but I keep getting a type mismatch and not understanding why they are both date/time fields formatted to short date. When I remove the criteria it display all records regardless of the [duedate]. I"m not sure where i'm missing a step in producing the correct results.

    the complete SQL
    SELECT Customer.CustomerNumber, Customer.Company, Customer.Address, Customer.City, Customer.Zip, Customer.Cross1, Customer.Cross2, Customer.Phone, Customer.Other, Customer.Contact, Workorders.qty, Workorders.Price, Workorders.Terms, Workorders.Completed, Workorders.duedate
    FROM Customer INNER JOIN Workorders ON Customer.CustomerID = Workorders.CustomerID;

    Thanks to all in advance.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    month(now()) is a correct (numeric ) value. But you have to match it to the month([Duedate])

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    surely match month AND year


    or use one of the inbuilt date time functions, such as datediff or possibly dateadd/datesub
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    ...surely match month AND year...
    In addition to this, is Duedate, a calculated Field, actually stored in the Table? Storing calculated values is generally a very bad idea.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2014
    Posts
    2
    Yes due date is part of the base table calculated as dateserial(year([completed]),Month([completed])+5,day([completed])) an for clarification sake why is it bad to store this being that it's calculated?

Posting Permissions

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