You wrote
Can anyone tell me what is wrong with the following? Whatever I do
the report brings back all records in the table, rather than just
those between the dates specified.
Dim strDocName As String
Dim strIncidentDate1 As Date
Dim strIncidentDate2 As Date
strIncidentDate1 = Me!txtReportStartDate
strIncidentDate2 = Me!txtReportEndDate
strDocName = "rptRedYellow"
DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>=" &
strIncidentDate1 & "And" & "[IncidentDate]<=" & strIncidentDate2
This is crucial to my whole database project and I am tearing out what
little I have left of my hair.
Help!!!
If you are in an ADP I assume that the data is held in SQL Server.
I've never worked with ADPs but I believe that the syntax of SQL
generally in ADPs has to match T-SQL. In that case I would expect the
concatenation operator to be + rather than &. It may be that your filter
string is being completely ignored.
Before getting involved in the report and the code to manage it make
sure that you can write a query that returns the data you require.
What you should try is using a MsgBox to display the filter string that
you are generating.
Also, the IncidentDate strings need to be identified as such in the
filter string. If you were hard coding it would look like this:
"[IncidentDate]>= '1-MAR-2005'"
Try putting single quotes in to surround the string variables.
DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>= '" &
strIncidentDate1 & "' And " & "[IncidentDate]<= '" & strIncidentDate2
& "'"
Finally, making date queries work in SQL Server depends on getting the
format of the criteria right. You haven't told us the format of
strIncidentDate1 and strIncidentDate2 -- are they hand entered by the
user or derived from tables?
--
Albert Marshall
Marshall Le Botmel Ltd
01242 222017