Thread: Access 2007 - Help building a query

Hi

I was wondering if anyone could help me create a query in access which would do the following as i cant seem to work it out.

Currently i have two tables table 1 and 2. I want to create a query which selects ID, Description, Chase date, Receievd date from table 1 & Closedate and startdate form table 2.(These tables already have a inner join by the ID column)

The criteria i need is that when the query runs a inputbox appears where you enter a date and then the query takes that date and adds 21 (e.g. if the entered date was 17/11/2011, the query would add 21 to it so it would be 08/12/2011). It would then use that date and bring back any Chase date which are equal to or less than that date and that are blank in Receieved date, closedate and startdate.

However i am not sure how to do this? I am currently using the Query Design tool in design view, and i am having trouble entering the criteria. Can anyone help me?

Thank you

Jeskit

Hi

Thank youf ro replying.

Sure not a problem the SQL code is below:

SELECT Table1.ID, Table1.Description,Table1.Chasedate, Table1.Receievddate, Table2.Closedate, Table2.startdate
FROM Table2 INNER JOIN Table1 ON Table2.AppRefID = Table1.AppRefID;

Thanks,

Jeskit

Hi,

I have found out that what I actually need the query to do is different from what i posted first off!

What it needs to is take the same columns from the same tables, except for the criteria it needs to first create a new invisible column which takes the chase date and add 21 to it, return only blank rows in Receieved date, closedate and startdate.

It should also, when the query runs a inputbox appears where you enter a date. It should then take that date and compare it agianst the chase date + 21 column.

I hope this makes sense!

Hi,

Thanks for replying!

The code with the criteria of Recieved date, close date, and start date as null from below,

SELECT Table1.AppRefID, Table1.Item,Table1.DateChased, Table1.DateRcd, Table2.StartDate, Table2.AppClosed

FROM Table2 INNER JOIN Table1 ON Table2.AppRefID = Table1.AppRefID

WHERE (((IsNull([Table2]![StartDate]))=True) AND ((IsNull([Table2]![AppClosed]))=True) AND ((IsNull([Table1]![DateRcd]))=True));

However now i need a criteria which add an invisible column which takes the Chase date and adds 21 to it, then add code which says when the query is run have the user provide a date and then return all the rows where the date in the new invisible column is equal to or less than the date provided.

Any help would be appreciated!

Thank you

HI,

Ok i have tried that however i keep getting a message of "The expression you entered has an invalid date value" when i enter "#&[Enter a Chase Date]&#" into the criteria box.

Hi,

OK i have got it running but its not bringing back any dates.

From what i know about the dates in the tables if i put in 26/11/2011 it should return any rows which are equal too or less than the chase date + 21, so it should return 1 row.

Hi,

Thank you for all your help, your query has worked.

However do you know if it is possible to have a criteria which says <= [table3].[date] where table1].[id] = 1?

As the way i was trying to build the query was not working due to other constraints in the database so i have had re-design, so the query now does the same but instead of using a message box to get the chase date it takes the date in a certain table (table3).

However i need to add an extra on constraint on to <= [table3].[date] criteria so it only looks at the date in table 3 which has 1 in the id column of table 3.

Is this possible?

Thanks for your help