This one will be singular as its a small tip I just learnt a minute ago.
I am currently converted some Access forms and Reports, and somehow this one record source didn’t work:
SELECT TOP 100 PERCENT pl.PositionID, (SELECT Title FROM atbv_Personnel2_Positions WHERE PositionID = pl.PositionID) AS Title, pl.Percent AS PosPercent, pl.Location, pl.LocationID, p.Name, pl.FromDate, pl.ToDate, ISNULL(pl.MhrsRate, 0) AS MhrsRate, pl.CostGroup, (SELECT Description FROM stbv_System_Codes WHERE CodeType = ‘Personnel2_Location’ AND Code = pl.Location) AS LocationDesc
FROM atbv_Personnel2_PositionsLocations AS pl INNER JOIN atbv_Personnel2_Persons AS p
ON pl.Login=p.Login
ORDER BY pl.PositionID, ISNULL(pl.FromDate, ‘1900-01-01′);
Thats quite a bit of SQL to look at, so just look at the code in red. I need to select the field Percent from the view atbv_Personnel2_PositionsLocations and Percent is a command in SQL, so you will get the following error:
“Incorrect syntax near the keyword ‘Percent’.”
So, my colleague showed me a simple solution just SELECT pl.[Percent] instead, as these brackets represent selecting a column, so this works fine!
Simple basic everyone should learn i reckon.
