query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!
"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!
|||<snip>[vbcol=seagreen]
<snip>
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)
|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!

> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>
sql

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!|||<snip>
<snip>[vbcol=seagreen]
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integ
er arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>,
<, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LE
FT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>