Results 1 to 4 of 4

Thread: help with sql

  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: help with sql

    I am trying to write sql query that I will base my report on but I can't get my head around for what I need to do so I was hoping somebody could help me, I will try and explain.

    SELECT [Location of Builders Damage Table].[Report ID], [Location of Builders Damage Table].MaintenanceOfficer, [Location of Builders Damage Table].[Date Building Works Completed], [Street Number] & ' ' & [STREET] & ' , ' & [SUBURB] & ' , ' & [POSTCODE] AS Address
    FROM STREETS INNER JOIN [Location of Builders Damage Table] ON STREETS.[Street ID] = [Location of Builders Damage Table].[street id]
    WHERE ((([Location of Builders Damage Table].MaintenanceOfficer)=[Enter Maintenance Officer Name?])

    so this bit is fine, now i am trying to read values from two fields

    date1=Date Building Works Completed
    date2=Date of entry

    This is what I need to do:
    1. need to be able to run this report limiting it by date range
    2.date range is made out of either of these two dates
    3.If date1 is not NULL then "Between[start date] and [End date]" is running off date1
    4.If date1 is NULL then "Between[start date] and [End date]" is running off date2

    I have been trying to write it, use IIF function but got nowhere i was hoping somebody could look at it and help me.

    Thanks a lot

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi zobernjik,

    If you don't get any help soon from your posting, try posting it in the SQL section of the Forum. Might come out better that way.

    have a nice one,
    BUD

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    WHERE ((([Location of Builders Damage Table].MaintenanceOfficer)=[Enter Maintenance Officer Name?]) AND ((DATE1 NOT NULL AND DATE1 Between[start date] and [End date] ) OR (DATE1 IS NULL AND DATE2 Between[start date] and [End date]))

    you will need to tidy up the puctuation and brackets but this shgould get you close

  4. #4
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks guys,

    Healdem, I did something similar and I got where I wanted to go, so thanks for you help.

    cheers

Posting Permissions

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