Having trouble with an imported Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Having trouble with an imported Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-04 : 07:38:36

Dan writes "I have migrated from an Access Database to SQL 2005. Almost everything is working as it should, except for the following query. I made the sytax change form " to ' which I thought was the only problem, but I was obviously wrong. Any ideas would be appreciated.

--Dan

SELECT [NCF - LA].[Partner #], [NCF - LA].Salutation, [NCF - LA].FirstName, [NCF - LA].[Middle Initial], [NCF - LA].LastName, [NCF - LA].[Address 1], [NCF - LA].[Address 2], [NCF - LA].City, [NCF - LA].StateOrProvince, [NCF - LA].PostalCode, [NCF - LA].Country, [Money & Gifts].[Appeal Code], [Money & Gifts].Amount, [Money & Gifts].[Date of Contribution], [Money & Gifts].[Number of Gifts]
FROM [NCF - LA] INNER JOIN [Money & Gifts] ON [NCF - LA].[Partner #] = [Money & Gifts].[Partner #]
GROUP BY [NCF - LA].[Partner #], [NCF - LA].Salutation, [NCF - LA].FirstName, [NCF - LA].[Middle Initial], [NCF - LA].LastName, [NCF - LA].[Address 1], [NCF - LA].[Address 2], [NCF - LA].City, [NCF - LA].StateOrProvince, [NCF - LA].PostalCode, [NCF - LA].Country, [Money & Gifts].[Appeal Code], [Money & Gifts].Amount, [Money & Gifts].[Date of Contribution], [Money & Gifts].[Number of Gifts], [NCF - LA].[Delete from Active List]
HAVING ((([NCF - LA].[Address 1]) Not Like "none") AND (([NCF - LA].City) Not Like "none") AND (([NCF - LA].StateOrProvince) Not Like "ZZ") AND (([NCF - LA].PostalCode) Not Like "00000") AND (([Money & Gifts].[Appeal Code]) Not Like "AG*" And ([Money & Gifts].[Appeal Code]) Not Like "CS*" And ([Money & Gifts].[Appeal Code]) Not Like "DC*" And ([Money & Gifts].[Appeal Code]) Not Like "EG*" And ([Money & Gifts].[Appeal Code]) Not Like "MG*" And ([Money & Gifts].[Appeal Code]) Not Like "PG*" And ([Money & Gifts].[Appeal Code]) Not Like "TG*" And ([Money & Gifts].[Appeal Code]) Not Like "WG*" And ([Money & Gifts].[Appeal Code]) Not Like "EP*" And ([Money & Gifts].[Appeal Code]) Not Like "MR*" And ([Money & Gifts].[Appeal Code]) Not Like "PR*" And ([Money & Gifts].[Appeal Code]) Not Like "TR*" And ([Money & Gifts].[Appeal Code]) Not Like "WR*") AND (([Money & Gifts].Amount)>0) AND (([Money & Gifts].[Date of Contribution])=[Enter date:]) AND (([Money & Gifts].[Number of Gifts])=0) AND (([NCF - LA].[Delete from Active List]) Is Null))
ORDER BY [NCF - LA].[Partner #];"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 07:48:00

[code]DECLARE @SomeDate DATETIME

SELECT @SomeDate = '20060819'

SELECT DISTINCT [NCF - LA].[Partner #],
[NCF - LA].Salutation,
[NCF - LA].FirstName,
[NCF - LA].[Middle Initial],
[NCF - LA].LastName,
[NCF - LA].[Address 1],
[NCF - LA].[Address 2],
[NCF - LA].City,
[NCF - LA].StateOrProvince,
[NCF - LA].PostalCode,
[NCF - LA].Country,
[Money & Gifts].[Appeal Code],
[Money & Gifts].Amount,
[Money & Gifts].[Date of Contribution],
[Money & Gifts].[Number of Gifts]
FROM [NCF - LA]
INNER JOIN [Money & Gifts] ON [Money & Gifts].[Partner #] = [NCF - LA].[Partner #]
WHERE [NCF - LA].[Address 1] <> 'none'
AND [NCF - LA].City <> 'none'
AND [NCF - LA].StateOrProvince <> 'ZZ'
AND [NCF - LA].PostalCode <> '00000'
AND [NCF - LA].[Delete from Active List] IS NULL
AND [Money & Gifts].[Appeal Code] NOT LIKE 'AG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'CS%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'DC%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'EG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'MG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'PG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'TG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'WG%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'EP%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'MR%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'PR%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'TR%'
AND [Money & Gifts].[Appeal Code] NOT LIKE 'WR%'
AND [Money & Gifts].Amount > 0
AND [Money & Gifts].[Date of Contribution] BETWEEN @SomeDate AND DATEADD(day, 1, @SomeDate)
AND [Money & Gifts].[Number of Gifts] = 0
ORDER BY [NCF - LA].[Partner #][/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources