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