Access 2007 - Help building a query

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
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

JessicaSeymour
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,299
Office Version
  1. 365
Based on your description, I think the following will work.
Create a new query in the query wizard, go to SQL View and paste the following:

PARAMETERS PleaseEnterADate Date;

SELECT
ID
, Description
, [Chase date]
, [Received date]
,[Closedate]
,[startdate]
FROM [Table 1] INNER JOIN [Table 2] ON
[Table 1].ID = [Table 2].ID
WHERE
[Chase date] <= PleaseEnterADate + 21 AND
[Received date] IS NULL AND
[Closedate] IS NULL AND
[startdate] IS NULL


Good luck
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top