Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: Need Help on How to join a date query please

    I have a query based on dates
    Can anyone tell me what is wrong with this query
    Bednights: [PlacetEnd]-[PlaceStart] And IIf(IsNull([PlacetEnd]),DateDiff("d",[PlaceStart],Date()),DateDiff("d","PlaceStart]",[PlacetEnd]))

    I can use the Query
    Bednights: [PlacetEnd]-[PlaceStart]

    And I can use the Query
    Bednights: IIf(IsNull([PlacetEnd]),DateDiff("d",[PlaceStart],Date()),DateDiff("d","PlaceStart]",[PlacetEnd]))

    But I cant seem to join them without creating errors
    Can someone assist please?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you try to do by ANDing two dates? If you mean "I want Date1 and Date2, it's :
    Code:
    Date1:[PlacetEnd]-[PlaceStart] , Date2:IIf(IsNull([PlacetEnd]),DateDiff("d",[PlaceStart],Date()),DateDiff("d","PlaceStart]",[PlacetEnd]))
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Hi Sinndho,
    Thanks for trying
    Bednights: Date1:[PlacetEnd]-[PlaceStart] , Date2:IIf(IsNull([PlacetEnd]),DateDiff("d",[PlaceStart],Date()),DateDiff("d","PlaceStart]",[PlacetEnd]))

    I pasted that straight in however I got the reply that this contains an invalid syntax.
    Just for clarity I want the expression to look at the fields PlaceStart and Place tEnd and calculate the number of days between. when there is an empty cell in the PlacetEnd cell then it needs to be treated as the current date so the calculation can continue.
    Does this help. I am very new to access so please forgive me if I am asking a dumb question.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this, then:

    Code:
    Bednights: IIf(IsNull([PlaceEnd]);DateDiff("d";[PlaceStart];Now());DateDiff("d";[PlaceStart];[PlaceEnd]))
    or if you want the full SQL (in my example the table is Table1):
    Code:
    SELECT Table1.PlaceStart, Table1.PlaceEnd, IIf(IsNull([PlaceEnd]),DateDiff("d",[PlaceStart],Now()),DateDiff("d",[PlaceStart],[PlaceEnd])) AS Bednights
    FROM Table1;
    In the SQL language, AND is a link operator that is mainly used to join two conditions; Ex:
    Code:
    [Date1] < #08/15/2009# AND [Date2] IS NOT NULL
    Also, it is not recommended to directly perform arithmetic operations on Date data types. You should always use the date manipulation functions such as: DateAdd, DateDiff etc.

    Have a nice day!

  5. #5
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Thankyou All

    Hello Sinndho,
    This seems to work!
    You are all superhero's in my book so feel free to put on a superhero suit and bask in some well earned glory. Thanks again. I am sure it will not be long before I have run into another problem but until then my sincere thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    Feel free to come back for more.

    Have a nice day!

Posting Permissions

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