Microsoft Access: Vulnerabilities Database - Query Past 6 Months

[aus_son]

I need to make a query that will show the past 6 months for each month. So now for an example, if it was February I need to create a SLA report for February, but I need to generate trend graph for the past 6 months to include in the report. But it just continues to show months that are not for the past 6 months.

SQL VIEW

<code>
SELECT Vulnerabilities.[Host IP], Vulnerabilities.CVSS, Vulnerabilities.[Risk Level], Vulnerabilities.Vulnerability, Vulnerabilities.[Scan Date]FROM VulnerabilitiesWHERE (((Vulnerabilities.[Risk Level])="Serious" Or (Vulnerabilities.[Risk Level])="High" Or (Vulnerabilities.[Risk Level])="Medium") AND ((Vulnerabilities.[Scan Date]) Between #8/1/2020# And #1/31/2020#));
</code>

DESIGN VIEW

[ranman256]

on a form put in 3 text boxes: txtToday, txtStartDate, txtEndDate
set the default txtToday = Date()
when the form opens, set the other 2 boxes:

sub Form_Load()
vStart = month(txtToday ) & "/1/" & year(txtToday )

'get end of the month
txtEndDate = dateAdd("d",-1,dateAdd("m",1,vStart))
txtStartDate = DateAdd("m",-6,vStart)

end sub

the query then uses the textboxes for the criteria:
select * from table where [dateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

[ssanfu]

The first thing I would do is remove ALL spaces in object names.

In the where clause, you have

Vulnerabilities.[Scan Date] Between #8/1/2020# And #1/31/2020#

Shouldn't it be

Vulnerabilities.[Scan Date] Between #8/1/2019# And #1/31/2020#

And instead of hard coding the dates in the query, I would have a form with two text controls for the Start Date and the End Date.......much easier to change the dates for the query.

[CJ_London]

cross posted here with answers

https://www.access-programmers.co.uk...months.311312/

[Micron]

removed cross posting message