Report only showing half the records

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a report that is called from a form. On the open
event of the report I set the report's data source as
follows:

Dim stSQL As String

stSQL = Forms![frmMyForm].[txtSQL]
Me.RecordSource = stSQL

The SQL string looks like:

SELECT * FROM vOOWList WHERE GroupID='S100' AND
(('8/6/2004' BETWEEN StartDt and EndDt)
OR ('8/6/2004' > StartDt AND EndDt IS NULL))


The wierd thing is, my report displays records as if
the "OR ('8/6/2004' > StartDt AND EndDt IS NULL))" in the
SQL string wasn't there.

To make matters even more strange, if I were to switch the
report to design view and then back to print preview all
the data would be displayed.

Any ideas as to what the problem might be would be greatly
appreciated.
 
In a SQL statement, delimit dates with #.
When you use the quote mark, Access treats it like a string.

If you live in a country that does not use the mm/dd/yyyy date format, you
need to explicitly format the date that way as well. More info:
http://members.iinet.net.au/~allenbrowne/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


news:[email protected]...
 
Thanks for the reply but that is not the problem. My
application is an .ADP, the string is running in SQL
Server 2000 and in SQL dates are surrounded by single
quotes.

Any other ideas???

-----Original Message-----
In a SQL statement, delimit dates with #.
When you use the quote mark, Access treats it like a string.

If you live in a country that does not use the mm/dd/yyyy date format, you
need to explicitly format the date that way as well. More info:
http://members.iinet.net.au/~allenbrowne/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that is called from a form. On the open
event of the report I set the report's data source as
follows:

Dim stSQL As String

stSQL = Forms![frmMyForm].[txtSQL]
Me.RecordSource = stSQL

The SQL string looks like:

SELECT * FROM vOOWList WHERE GroupID='S100' AND
(('8/6/2004' BETWEEN StartDt and EndDt)
OR ('8/6/2004' > StartDt AND EndDt IS NULL))


The wierd thing is, my report displays records as if
the "OR ('8/6/2004' > StartDt AND EndDt IS NULL))" in the
SQL string wasn't there.

To make matters even more strange, if I were to switch the
report to design view and then back to print preview all
the data would be displayed.

Any ideas as to what the problem might be would be greatly
appreciated.
Click to expand...


.
Click to expand...
 
Back
Top