criteria in query

[Guest]

I am currently running a query which selects different
records according to criteria set. Three example fields
in this query are report due, report initiated and start
date. I would like the query to show all fields where it
is 30 days after the report initated date or all fields
where the start date is null or all fields where its been
30 days after the report due date. However having this
criteria entered my query only shows records that fulfill
all the criteria, how do should i resolve the problem? I
would rather not have a seperate query for each criteria
but will if it is the only way.

 

[tina]

try this

SELECT Table5.ReportDue, Table5.ReportInitiated, Table5.StartDate
FROM Table5
WHERE (((Table5.StartDate) Is Null)) OR
(((DateAdd("d",30,[ReportInitiated]))<=Date())) OR
(((DateAdd("d",30,[ReportDue]))<=Date()));

substitute your correct table and field names, of course. if you then look
at the query in Design view (rather than SQL view), you'll see how the
criteria is placed on a separate line in the QBE grid for each value -
rather than all on one line, as you probably did initially.
also note: " <= " in the formula means you get dates 30 days or older. if
you want to only pull dates exactly 30 days old, use " = " instead.

hth

 

[Guest]

Tina, thank you for the tip however since I am new to
database design I am confused. where should I intert this
select criteria?

-----Original Message-----
try this

SELECT Table5.ReportDue, Table5.ReportInitiated, Table5.StartDate
FROM Table5
WHERE (((Table5.StartDate) Is Null)) OR
(((DateAdd("d",30,[ReportInitiated]))<=Date())) OR
(((DateAdd("d",30,[ReportDue]))<=Date()));

substitute your correct table and field names, of course. if you then look
at the query in Design view (rather than SQL view), you'll see how the
criteria is placed on a separate line in the QBE grid for each value -
rather than all on one line, as you probably did initially.
also note: " <= " in the formula means you get dates 30 days or older. if
you want to only pull dates exactly 30 days old, use " = " instead.

hth

I am currently running a query which selects different
records according to criteria set. Three example fields
in this query are report due, report initiated and start
date. I would like the query to show all fields where it
is 30 days after the report initated date or all fields
where the start date is null or all fields where its been
30 days after the report due date. However having this
criteria entered my query only shows records that fulfill
all the criteria, how do should i resolve the problem? I
would rather not have a seperate query for each criteria
but will if it is the only way.

.

 

[tina]

in your database window, go to Queries. click New, the select Design View in
the dialog box and click OK. close the Show Table dialog box without adding
any tables. click the SQL button on the toolbar (it should be at the far
left on the default toolbar). paste the complete SQL statement into the
window; the word "SELECT;" is already there - make sure you overwrite it.
and make sure you remove those >> characters from the beginning of each
line.
after pasting, substitute your correct table and field names in the entire
statement (don't miss any). then click the down arrow beside the View button
on the toolbar (again, far left) and select Design View. this will show you
the QBE grid that you're probably familiar with in building queries.
if you have trouble following my directions (so much easier to show than
tell), you can email me at ttacc_kill_all_spam_ess1 at yahoo dot
com (remove the underscores and the words between them) and i'll send you
screen prints to walk you thru it.

Tina, thank you for the tip however since I am new to
database design I am confused. where should I intert this
select criteria?

-----Original Message-----
try this

SELECT Table5.ReportDue, Table5.ReportInitiated, Table5.StartDate
FROM Table5
WHERE (((Table5.StartDate) Is Null)) OR
(((DateAdd("d",30,[ReportInitiated]))<=Date())) OR
(((DateAdd("d",30,[ReportDue]))<=Date()));

substitute your correct table and field names, of course. if you then look
at the query in Design view (rather than SQL view), you'll see how the
criteria is placed on a separate line in the QBE grid for each value -
rather than all on one line, as you probably did initially.
also note: " <= " in the formula means you get dates 30 days or older. if
you want to only pull dates exactly 30 days old, use " = " instead.

hth

I am currently running a query which selects different
records according to criteria set. Three example fields
in this query are report due, report initiated and start
date. I would like the query to show all fields where it
is 30 days after the report initated date or all fields
where the start date is null or all fields where its been
30 days after the report due date. However having this
criteria entered my query only shows records that fulfill
all the criteria, how do should i resolve the problem? I
would rather not have a seperate query for each criteria
but will if it is the only way.

.