Modifying Query Criteria Question

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

TotallyConfused

I have a report that has records that must be ignored for a certain time
period from when project starts. Meanwhile, from this report records need to
be printed when projects starts. I have in my query's criteria a field for
the records to be ignored set to Is Null.

How can I do I write the query so that when I need to start printing these
records can I then make the records accessible. I do not want to go and
change queries. I want to write it to the query or have a command. Can this
be done, if so can you please provide sample? Thank you in advance for any
help you can provide.
 
So after a certain date you want those records to become available for the
report right? Can you tell us the name of your project or task date field
and when you want them to become available. I am envisioning a datedif
function that will compare today's date with the task date and only show
those records that are withing a certain range.
 
Thank you for responding. There is no definite date so that may not help. I
was thinking instead to write something for the Field (name of grp). Only a
one digit number will be entered to identify the grp 1 or 2. When the time
comes that we want to print these records, we can enter a -1 or -2. I tried
putting in the criteria
Not like 1 OR Not like 2. This did not return anything. Can you help with
this? Thank you.
 
Thank you for responding. There is no definite date so that may not help. I
was thinking instead to write something for the Field (name of grp). Only a
one digit number will be entered to identify the grp 1 or 2. When the time
comes that we want to print these records, we can enter a -1 or -2. I tried
putting in the criteria
Not like 1 OR Not like 2.
Click to expand...

You're misunderstanding the meaning of LIKE.

LIKE is used for *ONLY* one purpose: it lets you search Text data using
wildcards such as * (match any string) or ? (match any single character) or #
(match any numeric digit).

If you want to retrieve all records except those with values 1 and 2, use a
criterion

NOT IN (1, 2)

John W. Vinson [MVP]
 
Thank you the explanation. Yes I do want all records retrieved except those
with values 1 and 2. I have entered the criterion you provided and it is
returning nothing. There should be records returned when I run my criteria
that do not have the values 1 and 2. What am I not doing right? Thank you
for your help.
 
Thank you the explanation. Yes I do want all records retrieved except those
with values 1 and 2. I have entered the criterion you provided and it is
returning nothing. There should be records returned when I run my criteria
that do not have the values 1 and 2. What am I not doing right? Thank you
for your help.
Click to expand...

Please open the query in SQL view and post the SQL text here, along with an
example of a record that you should be seeing and aren't.

John W. Vinson [MVP]
 
Okay - here is the SQL - when I run the query I want the query to retrieve
rows with "hlthcarPrvdrs" measure, fullname, that are not complete, id, name,
Type not 1 or 2. Thank you

SELECT DISTINCTROW [HlthcarePrvdrs].Measure, tblMeasures.FullName,
[HlthcarePrvdrs].Complete, [HlthcarePrvdrs].[ID],
[HlthcarePrvdrs].[HLTHCAREID], [HlthcarePrvdrs].[NAME],
[HlthcarePrvdrs].GROUPS, [HlthcarePrvdrs].Type
FROM [HlthcarePrvdrs] INNER JOIN tblMeasures ON [HlthcarePrvdrs].Measure =
tblMeasures.Measure
GROUP BY [HlthcarePrvdrs].Measure, tblMeasures.FullName,
[HlthcarePrvdrs].Complete, [HlthcarePrvdrs].[ID],
[HlthcarePrvdrs].[HLTHCAREID], [HlthcarePrvdrs].[NAME],
[HlthcarePrvdrs].GROUPS, [HlthcarePrvdrs].[PRVDR PH], [HlthcarePrvdrs],Type
HAVING ((([HlthcarePrvdrs].Complete=False) AND (([HlthcarePrvdrs].GROUPS) Is
Null) AND (([HlthcarePrvdrs].Type) Not In (1,2)));
 
Okay - here is the SQL - when I run the query I want the query to retrieve
rows with "hlthcarPrvdrs" measure, fullname, that are not complete, id, name,
Type not 1 or 2. Thank you
Click to expand...

First off... this is a Totals query with a Group By, but you're not summing or
counting or doing anything else that makes it need to be a Totals query (not
that I can see anyway). Am I missing something? Why the Totals?

And I did suggest that you post some sample data, showing a record that it
should be selecting and isn't. I can't see your data and I don't know the
values of these fields, so I don't know what is wrong and what is right. For
instance, what is the value of GROUPS? Your query is selecting only records
with nothing in that field; is that one of your requirements? If not, take out
the line

AND [HlthcarePrvdrs].GROUPS Is Null

I would try simplifying the query, by getting rid of the Group By and changing
the HAVING to WHERE:

SELECT DISTINCTROW [HlthcarePrvdrs].Measure, tblMeasures.FullName,
[HlthcarePrvdrs].Complete, [HlthcarePrvdrs].[ID],
[HlthcarePrvdrs].[HLTHCAREID], [HlthcarePrvdrs].[NAME],
[HlthcarePrvdrs].GROUPS, [HlthcarePrvdrs].Type
FROM [HlthcarePrvdrs] INNER JOIN tblMeasures ON [HlthcarePrvdrs].Measure =
tblMeasures.Measure
WHERE [HlthcarePrvdrs].Complete=False
AND [HlthcarePrvdrs].GROUPS Is Null
AND [HlthcarePrvdrs].Type Not In (1,2);

John W. Vinson [MVP]
 
Back
Top