Recent content by oldteddybear

Mm. When I copy and paste without changes I get 1817 incomplete records. When I make the changes as above I still get 1817 records except the [EndDateIfNull] field now returns results with all cells occupied and those over the query date with the correct substitution. Hence I think we on the...

No not yet. Any suggestions would be appreciated. The relevant SQL has now been altered to: SELECT T_Placements.PlacementNumber, T_Placements.ClientID, T_Placements.ServiceStartDate, T_Placements.ServiceEndDate, nz([ServiceEndDate],[End Date]) AS EndDateIfNull, T_Placements.ServiceStartDate FROM...

Hi Paul, I hate to say it but no it did not. It just picked up every period of use entry in the data base with end dates in 2029. However it may be in the right direction. So I changed the #12/31/2029# to [Start Date] and the entry [Forms]![frmTest]![txtStartDate] to [Start Date] and the entry...

Hi Paul, The mdb file is attached as requested. I have removed many additional tables and fields to leave those that are relevant to my query. I hope this helps. I am unable to upload the appn front end as it exceeds the limit allowed by the forum and I dont know how to reduce it. Cheers Don

Thanks Paul and `Sparks'. Unfortunately cant post the data base as its in use with too much confidential information. However working with Sparks answer this creates a reply but I need a way of telling the query to replace any date prior to the query date with the actual query date. I keep...

Hi, Thanks for that however it just seems to identify in a different way the same results I am already getting. I do not seem to be able to gather in null values from the unfinished date ranges or dates that overlap which commenced prior to my query date.

Hi Paul, This is the SQL I am having problems with! I just seem to be going in circles with it. SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate FROM T_Placements GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate HAVING...

Hi Paul, I have tried to use what I think your answer advises but I just dont seem to be able to get it to work. I suspect the fault is mine thru inexperience rather than your answer. I have interchanged field names between yours and mine trying to make it run but no luck yet. Regards Don

I have two entry fields in a Table defining the ServiceStartDate and ServiceEndDate of times clients enter and leave a service. Similar to this example: ServiceStartDate ServiceEndDate 22/12/2011 22/01/2012 22/01/2012 24/01/2012 24/01/2012 09/03/2012 09/03/2012...

I really appreciate the help so as I Hope to clarify my problem. The SQL above is what remained after I dumped the rest when trying to simplify this query in a test database. So what I am after is: Lets say I have 5 entries in the data base and my query asks to show all entries between...

Can anyone assist please? I cannot seem to get this query to work as I would like. I have three tables being ServiceStartDate, ServiceEndDate and Service Type. Clients use the service for periods of time and I need to run a query that identifies what clients have accessed the service and for how...

Hi I'm a senior Kiwi down under. I can see the power of access and as a occasional user I blunder my way thru with access wishing I knew more. Currently trying to cobble together a database to track clientele usage of services and currently getting really confused trying to get a query to pick...