query must include date on a form but show others

[BCJourney]

Hi,


I have a query that pulls field1, field2, date field from a table. It will return all records where the date field = the selected date on a form. I also need it to return records up to 8 weeks prior to that date, but only if there is a record for the date displayed on the form.

Right now I have it pulling records for the date on the form. I know the expression to enter to display 8 weeks back but I don't know how to display 8 weeks back and all in between ONLY if there is a record for the date on the form.

thank you!

[Dal Jeanis]

You can try something like this:

SELECT T1.Field1, T1.Field2, T1.MyDate
FROM MyTable AS T1
WHERE ((T1.MyDate <= Forms!MyForm!MyDateField)
AND (T1.MyDate >= DateAdd("w",-8,Forms!MyForm!MyDateField)))
AND EXISTS 
   (SELECT T2.MyDATE from MyTABLE AS T2
    WHERE T2.MyDate = Forms!MyForm!MyDateField);

[BCJourney]

thank you. It works beautifully up until the AND EXISTS part. I don't understand what T2 is.

[Dal Jeanis]

Sorry, that's real SQL rather than Access SQL. T2 was just a different alias for the same table.

Replace the EXISTS clause with something like this:

And Dcount([Mydate],[MyTable],[MyDate] = Forms![MyForm]![MyDateField]) > 0

You'll have to determine where all the quotes go, based on where you're putting the SQL.

[BCJourney]

I got it now. Thank you for your invaluable help.

[BCJourney]

Dal,
I don't know what happened but my code was working, then it didn't. I've been working on it and i can't figure it out. Here it is with actual field names. I hope that doesn't confuse things.

WHERE ((([anti-virus-non_comp_rpt].Report_Date)=DCount([Report_Date],[anti-virus-non_comp_rpt],[anti-virus-non_comp_rpt.[Report_Date]=[Forms]![MyForm]![MyDateField])>0))

[Dal Jeanis]

HABITS:
1) Please get out of the habit of using dashes and underscores in the same field/table/query name. In fact, you should never use dashes/hyphens in a name, because they can be taken for minus signs. Short names in CamelCase is the preferred standard by most Access programmers, but at the very least you should change all the hyphens (-) to underscores (_).

TRANSLATION OF SQL CODE:
2) reviewing that SQL code in English, it isn't very clear what you might be asking the Jet engine to do. Phrase by phrase, here's the English translation:

WHERE ((

MEANS:Accept any fields and records which meet the following condition(s):

([anti-virus-non_comp_rpt].Report_Date)=

MEANS:the ReportDate on the AVNCRpt must be equal to

DCount([Report_Date],

MEANS:the count of the number of ReportDates

[anti-virus-non_comp_rpt],

MEANS:found on records on the AVNCRpt

[anti-virus-non_comp_rpt.[Report_Date]=[Forms]![MyForm]![MyDateField])

MEANS:where the AVNCRpt ReportDates are equal to the value of the date found in MyDateField on MyForm.

>0)

MEANS:and then compare that something-or-other and see if it is greater than zero.

Yep, sounds confusing to me.

SYNTAX OF DCOUNT:

DCount(FieldName,TableorQueryName,condition expression)

Dcount returns a number - from all the records in TableOrQueryName, find the ones that match the conditional expression, and count the number of non-NULL values in FieldName on those records. Oh, and for all functions like DCount, I make a habit of putting braces [] around the field and table names, since Jet sometimes gets picky that way.

CONCLUSION:
A record count is just never going to equal a valid date.

CORRECTED SQL STATEMENT:
Since you are Dcounting the same Date field, you probably just want (leaving out the details)

where Dcount(,,)>0.

or including the details

WHERE ((DCount([Report_Date],[anti-virus-non_comp_rpt],[anti-virus-non_comp_rpt].[Report_Date]=[Forms]![MyForm]![MyDateField]))>0)

[BCJourney]

thank you for the help. I changed all hyphens in field names to underscore as you suggested. I used your code:

WHERE (((DCount([Report_Date],[anti_virus_non_comp_rpt],[anti_virus_non_comp_rpt].[Report_Date]=[Forms]![Navigationfrm]![NavigationSubform].[Form]![TxtDate]))>0))

and it asks for a parameter value for anti_virus_non_comp_rpt. considering that is a table, i am perplexed.

[Dal Jeanis]

Yep, that's an odd one. Please post the entire SQL string.

[BCJourney]

SELECT anti_virus_non_comp_REPORT_count.[CountOfSystem Name], anti_virus_non_comp_rpt.[System Name], anti_virus_non_comp_rpt.[Master Application Name], anti_virus_non_comp_rpt.[IP Address], anti_virus_non_comp_rpt.[Domain Name], anti_virus_non_comp_rpt.[OS Platform], anti_virus_non_comp_rpt.[Product Version (Agent)], anti_virus_non_comp_rpt.[Engine Version (VirusScan Enterprise)], anti_virus_non_comp_rpt.[DAT Version (VirusScan Enterprise)], anti_virus_non_comp_rpt.[Free System Drive Space], anti_virus_non_comp_rpt.Report_Date
FROM anti_virus_non_comp_rpt LEFT JOIN anti_virus_non_comp_REPORT_count ON anti_virus_non_comp_rpt.[System Name] = anti_virus_non_comp_REPORT_count.[System Name]
WHERE (((DCount([Report_Date],[anti_virus_non_comp_rpt],[anti_virus_non_comp_rpt].[Report_Date]=[Forms]![Navigationfrm]![NavigationSubform].[Form]![TxtDate])>0)>0))
GROUP BY anti_virus_non_comp_REPORT_count.[CountOfSystem Name], anti_virus_non_comp_rpt.[System Name], anti_virus_non_comp_rpt.[Master Application Name], anti_virus_non_comp_rpt.[IP Address], anti_virus_non_comp_rpt.[Domain Name], anti_virus_non_comp_rpt.[OS Platform], anti_virus_non_comp_rpt.[Product Version (Agent)], anti_virus_non_comp_rpt.[Engine Version (VirusScan Enterprise)], anti_virus_non_comp_rpt.[DAT Version (VirusScan Enterprise)], anti_virus_non_comp_rpt.[Free System Drive Space], anti_virus_non_comp_rpt.Report_Date
HAVING (((anti_virus_non_comp_REPORT_count.[CountOfSystem Name])>1) AND ((anti_virus_non_comp_rpt.Report_Date) Between [Forms]![Navigationfrm]![NavigationSubform].[Form]![TxtDate] And [Forms]![Navigationfrm]![NavigationSubform].[Form]![txt8wksago]))
ORDER BY anti_virus_non_comp_rpt.[System Name];

[Dal Jeanis]

try this:

SELECT 
   T2.[CountOfSystem Name], 
   T1.[System Name], 
   T1.[Master Application Name], 
   T1.[IP Address], 
   T1.[Domain Name], 
   T1.[OS Platform], 
   T1.[Product Version (Agent)], 
   T1.[Engine Version (VirusScan Enterprise)], 
   T1.[DAT Version (VirusScan Enterprise)], 
   T1.[Free System Drive Space], 
   T1.Report_Date
FROM 
   anti_virus_non_comp_rpt AS T1 
   LEFT JOIN 
   anti_virus_non_comp_REPORT_count As T2
   ON T1.[System Name] = T2.[System Name]
WHERE EXISTS 
   (SELECT T3.[Report_Date] 
    FROM anti_virus_non_comp_rpt AS T3
    WHERE T3.[Report_Date] = [Forms]![Navigationfrm]![NavigationSubform].[Form]![TxtDate])
GROUP BY 
   T2.[CountOfSystem Name], 
   T1.[System Name], 
   T1.[Master Application Name], 
   T1.[IP Address], 
   T1.[Domain Name], 
   T1.[OS Platform], 
   T1.[Product Version (Agent)], 
   T1.[Engine Version (VirusScan Enterprise)], 
   T1.[DAT Version (VirusScan Enterprise)], 
   T1.[Free System Drive Space], 
   T1.Report_Date
HAVING 
   (((T2.[CountOfSystem Name])>1) 
   AND ((T1.Report_Date) Between 
        [Forms]![Navigationfrm]![NavigationSubform].[Form]![TxtDate] AND
        [Forms]![Navigationfrm]![NavigationSubform].[Form]![txt8wksago]))
ORDER BY 
   T1.[System Name];

[BCJourney]

There are only 2 tables in the query. What is T3? thanks!

[Dal Jeanis]

FROM anti_virus_non_comp_rpt AS T3

T3 is a virtual copy of anti_virus_non_comp_rpt. It is only being used to determine whether the requested report date exists on the table.