Query String not structured correctly

[Synergy]

I am trying to create a string to use as a RecordSource for a form as
follows:


strSQL = "SELECT [Order Entry Header].ordID, [Order Entry Header].ordJob,
[Order Entry ST Products].detItem, [Department Data].DepartmentID,
[Department Data].DepartmentName," & _
"Tasks.[Task Order], [Order Entry ST Tasks].prodTask AS Task,
[Order Entry ST Tasks].prodStartDate AS [Start Date], [Order Entry ST
Products].detShipDate AS [Ship Date], [Order Entry Header].ordJob AS [Order
Number], [Order Entry Header].ordCustName AS [Customer Name]," & _
"[Order Entry Header].ordOnHold, [Order Entry ST Products].detQty
AS Qty, [Order Entry ST Products].detProdCode AS Prod, [detProdCode] & ' ' &
[detProdNotation] AS Product, " & _
"[Order Entry ST Tasks].prodEstTime AS [Est Time], [Order Entry ST
Tasks].prodTaskEmpNo AS Employee, Employees.EmpLastName, [Order Entry ST
Tasks].prodTaskTime AS [Actual Time], Employees.EmpRate, [Order Entry ST
Tasks].prodTaskCost, [Order Entry ST Tasks].prodTaskComplete AS Complete, "
& _
"Format([prodTaskDateComplete],'mm/dd/yy') & ' ' &
Format([prodTaskTimeComplete],'Medium Time') AS [Completed Date], " & _
"Format([prodTaskDateComplete],'mm/dd/yy') as [Date Complete],
DateDiff('d',[prodtaskdatecomplete],Date()) AS Days, [Order Entry ST
Tasks].prodNote AS [Note], [Order Entry Header].ordInvoiced, [Order Entry
Header].ordCustRef, [Order Entry Header].ordCustClient, [Order Entry
Header].ordNumberUp, [Order Entry Header].ordDescription" & vbNewLine & _
"FROM ((Tasks INNER JOIN [Department Data] ON Tasks.DepartmentID =
[Department Data].DepartmentID) INNER JOIN ((([Order Entry Header] INNER
JOIN [Order Entry ST Products] ON [Order Entry Header].ordID = [Order Entry
ST Products].ordID) INNER JOIN [Order Entry ST Tasks] ON [Order Entry ST
Products].ordDetID = [Order Entry ST Tasks].ordDetID) LEFT JOIN Employees ON
[Order Entry ST Tasks].prodTaskEmpNo = Employees.EmpNo) ON Tasks.Task =
[Order Entry ST Tasks].prodTask)" & _
"INNER JOIN [_Task Scheduler Unshipped] ON [Order Entry ST
Products].ordDetID = [_Task Scheduler Unshipped].ordDetID" & vbNewLine & _
"Where " & strWhere & vbNewLine & _
"ORDER BY [Department Data].DepartmentID, Tasks.[Task Order],
[Order Entry ST Products].detShipDate, [Order Entry Header].ordJob,
Format([prodTaskDateComplete],'mm/dd/yy') & ' ' &
Format([prodTaskTimeComplete],'Medium Time');"



The problem I am having is line 13:
"Format([prodTaskDateComplete],'mm/dd/yy') as [Date Complete],


I ran the code, ran the value of strSQL in the Immediate window and pasted
it to a query. The [Date Completed] field is structures wrong so that when
I Zoom the field in query design it displays:

Date Complete: Format([prodTaskD
ateComplete],'mm/dd/yy')

where a line feed/carriage return had been inserted.

I can't understand why this is happening. I am using Access2002, and WinXP
linked to a SQL Server Tables.

Any help is appreciated.

God Bless,

Mark A. Sam

 

[Synergy]

I found the problem to be that the string was too long. I added
& VBNewLine

to the end of each line. This resolved the issue.

 

[Tom Ellison]

Dear NoName:

When you build a string like this, a common pitfall is to forget to
put spaces in it so words don't run together. Watch for that.

Anyway, the main thing is to put a breakpoint in the code and use the
immediate window to see just what is in the SQL string after it has
been built. You may want to paste this string into a query and fix it
up till it works, then fix up the code till it is correct.

I am trying to create a string to use as a RecordSource for a form as
follows:


strSQL = "SELECT [Order Entry Header].ordID, [Order Entry Header].ordJob,
[Order Entry ST Products].detItem, [Department Data].DepartmentID,
[Department Data].DepartmentName," & _
"Tasks.[Task Order], [Order Entry ST Tasks].prodTask AS Task,
[Order Entry ST Tasks].prodStartDate AS [Start Date], [Order Entry ST
Products].detShipDate AS [Ship Date], [Order Entry Header].ordJob AS [Order
Number], [Order Entry Header].ordCustName AS [Customer Name]," & _
"[Order Entry Header].ordOnHold, [Order Entry ST Products].detQty
AS Qty, [Order Entry ST Products].detProdCode AS Prod, [detProdCode] & ' ' &
[detProdNotation] AS Product, " & _
"[Order Entry ST Tasks].prodEstTime AS [Est Time], [Order Entry ST
Tasks].prodTaskEmpNo AS Employee, Employees.EmpLastName, [Order Entry ST
Tasks].prodTaskTime AS [Actual Time], Employees.EmpRate, [Order Entry ST
Tasks].prodTaskCost, [Order Entry ST Tasks].prodTaskComplete AS Complete, "
& _
"Format([prodTaskDateComplete],'mm/dd/yy') & ' ' &
Format([prodTaskTimeComplete],'Medium Time') AS [Completed Date], " & _
"Format([prodTaskDateComplete],'mm/dd/yy') as [Date Complete],
DateDiff('d',[prodtaskdatecomplete],Date()) AS Days, [Order Entry ST
Tasks].prodNote AS [Note], [Order Entry Header].ordInvoiced, [Order Entry
Header].ordCustRef, [Order Entry Header].ordCustClient, [Order Entry
Header].ordNumberUp, [Order Entry Header].ordDescription" & vbNewLine & _
"FROM ((Tasks INNER JOIN [Department Data] ON Tasks.DepartmentID =
[Department Data].DepartmentID) INNER JOIN ((([Order Entry Header] INNER
JOIN [Order Entry ST Products] ON [Order Entry Header].ordID = [Order Entry
ST Products].ordID) INNER JOIN [Order Entry ST Tasks] ON [Order Entry ST
Products].ordDetID = [Order Entry ST Tasks].ordDetID) LEFT JOIN Employees ON
[Order Entry ST Tasks].prodTaskEmpNo = Employees.EmpNo) ON Tasks.Task =
[Order Entry ST Tasks].prodTask)" & _
"INNER JOIN [_Task Scheduler Unshipped] ON [Order Entry ST
Products].ordDetID = [_Task Scheduler Unshipped].ordDetID" & vbNewLine & _
"Where " & strWhere & vbNewLine & _
"ORDER BY [Department Data].DepartmentID, Tasks.[Task Order],
[Order Entry ST Products].detShipDate, [Order Entry Header].ordJob,
Format([prodTaskDateComplete],'mm/dd/yy') & ' ' &
Format([prodTaskTimeComplete],'Medium Time');"



The problem I am having is line 13:
"Format([prodTaskDateComplete],'mm/dd/yy') as [Date Complete],


I ran the code, ran the value of strSQL in the Immediate window and pasted
it to a query. The [Date Completed] field is structures wrong so that when
I Zoom the field in query design it displays:

Date Complete: Format([prodTaskD
ateComplete],'mm/dd/yy')

where a line feed/carriage return had been inserted.

I can't understand why this is happening. I am using Access2002, and WinXP
linked to a SQL Server Tables.

Any help is appreciated.

God Bless,

Mark A. Sam

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Mark A. Sam]

Tom,

Thanks for the reply. I understand that. The problem was that the SQL
statement was too long to assign to the RecordSource property, so I saved it
a s QueryDef.

No Name?...lol

God Bless,

Mark A. Sam