Hey all - I want to generate a report programmatically based on an existing query, or alternatively on an SQL statement. The query is a little complex (this is copied from the Query's SQL view):
SELECT TESTS.facilityID, TESTS.testDate, TESTS.testTypeID, DateAdd("m",[testPeriod],[testDate]) AS DateDue
FROM FACILITIES INNER JOIN (TESTTYPES INNER JOIN TESTS ON TESTTYPES.testTypeID = TESTS.testTypeID) ON FACILITIES.facilityID = TESTS.facilityID
WHERE (((DateAdd("m",[testPeriod],[testDate])) Between Date() And DateAdd("m",3,Date())));

which is why I thought it would be easier to use the existing query.
So, how do I get the record source of the report to be the records returned by this query? ALternatively, how can I code this complex SQL statement into VBA?
Thank you

SQL in VBA is string format. Assign a variable and just copy it into "" and then aply it to a control property RecordSource.
A few tricks you need to know.
If the line is too long:
..." _
& "...;"
You must identify the value string for instance in the WHERE statmnt:
"...WHERE ((table.field)= '" & yrStrValue & "');"
"...WHERE ((table.field)= #" & yrDateValue & "#);"
"...WHERE ((table.field)= " & yrValue & ");"
*note the number of ().
In your line would be Between #" & yrDate & "# And ...
Please ask if you need more

Thank you josemariagomes - I think it is working now. Another trick I needed to know was to use "" two double quotes inside the SQL string in VBA where I had " one double quote before as in:
SELECT TESTS.facilityID, TESTS.testDate, TESTS.testTypeID, DateAdd
WHERE (((DateAdd(""m"",[testPeriod],[testDate])) Between Date() And DateAdd(""m"",3,Date())));
Thanks.