help with sql

[croiva25]

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

 

[PHV]

Something like this ?
WHERE MaintenanceOfficer=[Enter Maintenance Officer Name?] AND
([Date Building Works Completed] Between [start date] And [End date] OR
([Date Building Works Completed] Is Null And [Date of entry] Between [start date] And [End date]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[croiva25]

thanks a lot that seems to be what I needed

cheers