Access Error Message - Help

[~J~]

Hit a problem today which I can't seem to work out or find an answer anywhere.

In a nutshell, a customer of mine has a program that can use either an Access or SQL database.

For the past 2 years, they've used Access, and today have switched over to SQL.

All has gone good, until they've received the following error message:

The minimum aggregate operation cannot take a bit data type as an argument.

I've look at both databases and the error is connected with a field called "IPPTAG". In Access, the datatype of this field is "Yes/No" in SQL it's a BIT.

Now I know they are technically the same thing, but the following SQL command is throwing a wobbler when run.

In Access, it runs perfect, in SQL it crashes with the error, removing the MIN(d.IPPTag) reference causes it to work.

Any ideas if there's a way to get it working?

Query is:

SELECT d.BasicPartNo, d.LatestPartNo, d.PartColour, MAX(d.Description) AS FirstDesc, MIN(d.IPPTag) AS MinIppTag, SUM(W1QtyRequired) AS Required FROM DeliverySchedule d WHERE d.SupplierID = 1 AND d.WarehouseID = 1 AND d.OrderType = 1 AND d.PlanCode = 'U4AF01' AND d.ShipToCode = 'SDC1' AND d.ProdPeriod = 42 AND d.DateTimeDelivery = {ts'2006-10-16 11:00:00'} AND d.Creator = 1 AND d.W1QtyRequired IS NOT NULL GROUP BY d.BasicPartNo, d.LatestPartNo, d.PartColour ORDER BY d.BasicPartNo, d.LatestPartNo, d.PartColour

 

[~J~]

Well the program is a bespoke piece of kit wrote be a rather large car company in Swindon, so I can't change the code as it's hard-coded into the program.

I agree, the error makes total sense, but didn't know if there was some advanced SQL server action that could convert a 1 to a Y or a 0 to a N. Likewise didn't know if there was a datatype (what I've no idea!!) in SQL that would act 'similar' to the Yes/No state in Access which I believe is a tetinary state and not binary like SQL.

 

[~J~]

Well, just converted the field type from BIT to INT and no errors reporting (can't do the CASE statement as the query is hardcoded into the program!).

No errors is a start, will have to wait now to see if the results the program returns is the same as the query.

Thanks for that, was starting to get worried!!