November 24, 2015 at 9:30 am
I recently moved a database from a SQL 7 server (yes I know) to a SQL 2005 server, now however I having issue with 1 of the queries, can someone help me to see what is wrong with this query? the user UI is access if that makes a difference
thanks in advance
SELECT DISTINCTROW Task.Status, Task.[Contract Number],
IIf(IsNull([Task].[Task Number]),0,[Task].[Task Number] & "." & [Task].[SubTask Number])
AS Task, Task.[Task Number], Task.[SubTask Number], Task.[Task Title], Task.[Task Reserve Amount]
AS ResrveAmt, Task.[Task Upset Limit]
AS UpsetLimit, qryAppTaskSupps.SuppSum
AS AprvdSupp, [Task].[Task Upset Limit]+nz([SuppSum],0)
AS ApprvUL, Task.[A/E Task Manager]
AS TaskMgr, Task.[Consultant Task Manager]
AS ConsltTaskMgr, Task.Consultant_Mgr_Email
AS ConsltEmail, Task.[Task Section], Task.[Engineers Estimate], Task.Airport, Task.[NTP Date], Task.[Anticipated Design Fee], Task.[Task Construction date], Task.Consultant, Task.Funding_Type, Task.[CTP Budget], Construction.Contract_Amount
AS CO_Contract_Amount, Construction.Contractor, Task.Design_Contingency, Task.Construction_Contingency, Task.Inflation_Escalator, Task.Total_CMI, Task.Completion_Dt, [Summary Link].PIN, Task.[Task Description], Task.Comments
FROM ((Task LEFT JOIN [Summary Link]
ON (Task.[SubTask Number] = [Summary Link].[SubTask Number]) AND (Task.[Task Number] = [Summary Link].[Task Number])) LEFT JOIN qryAppTaskSupps
ON (Task.[SubTask Number] = qryAppTaskSupps.[SubTask Number]) AND (Task.[Task Number] = qryAppTaskSupps.[Task Number])) LEFT JOIN Construction
ON [Summary Link].[PC Contract Number] = Construction.Contract_Number
ORDER BY [Task].[Task Number], [Task].[SubTask Number]
November 24, 2015 at 9:38 am
SQL Server 2005 doesn't have IIF(), you need to replace it with a CASE.
SQL Server uses DISTINCT instead of DISTINCTROW.
Access and SQL Server syntax are not the same, so you shouldn't write SQL Server queries in Access or the other way around.
November 24, 2015 at 11:04 am
Not going to lie, I am not 100% how it works it appears the form is using this query as a row source
here is an example of one that is working
SELECT DISTINCTROW Procurement.Status AS Status, Procurement.Contract_Number, Procurement.Title, Procurement.WageRatesDue, Procurement.Advertise, Procurement.PreBid, Procurement.NORA, Procurement.BidsDue, Procurement.MDE, Procurement.BPW_AgendaDue, Procurement.BPW, Procurement.Prov_Sch, Procurement.NTP, Procurement.MD_Register, Procurement.Duration, Procurement.Completion_Dt, Procurement.Consultant, Procurement.Contractor, Procurement.MBE_W_pct, Procurement.MBE_AA_pct, Procurement.MBE_pct, Procurement.Base_Bid_Amount, Procurement.Plans_Spec_Cost, Procurement.Escrow, Procurement.Comments, qryProAlts.Add AS Add_Alt, qryProAlts.Deduct AS Deduct_Alt, Procurement.Proj_Mgr, Procurement.Engineers_Est, IIf(IsNull([Summary Link].[Task Number]),'',[Summary Link].[Task Number] & "." & [Summary Link].[SubTask Number]) AS Task, [Summary Link].PIN, Mid([Procurement].[Contract_Number],5,2) AS Type FROM (Procurement LEFT JOIN [Summary Link] ON Procurement.Contract_Number = [Summary Link].[PC Contract Number]) LEFT JOIN qryProAlts ON Procurement.Contract_Number = qryProAlts.Construct_Contract_Number
November 24, 2015 at 12:12 pm
If you don't know, throwing queries at us won't work. We have no idea on how does your environment looks like. You mentioned that you're having an issue with your first query, but you never said what was the issue.
I suggest that you read the following article: http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
November 24, 2015 at 12:19 pm
Try replacing & with +
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 24, 2015 at 1:22 pm
Alvin Ramard (11/24/2015)
Try replacing & with +
Query is correct for Access.
November 25, 2015 at 5:40 am
I complete get it is hard to troubleshoot even when the op (me) does not know what the query does.
Little more background when the user opens up access, access is not using a access database it is pulling directly from the sql database, when going to the menu via the UI and the user pulls a master list for Design nothing appears the window comes back blank, when the pull the Capital Master list it comes back as it should, the Capital Master list is using the 2nd query I posted and the Design is using the query that I posted first and like I said does not work. When I click around in the in the UI I do get errors such as "can't find the object 'qryDesignML'" and "can't find the object 'qrDesignML'.','frmDesign',
Like I said earlier I backed up the database from a sql 7 server and restored it to a sql 2005 server, the users are now accessing the sql 2005 server there was no issue when the sql 7 server was housing the database.
I greatly thank you for the help with this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply