Using Duane Hookom's Concatenate Module

[BBBryan]

Hi,
Can Someone Please help.

I am using Duane Hookom's Concatenate Module.
I am using this right now in my query

CL-No:Concatenate("SELECT DISTINCT[ClosureWeldNo] FROM [Welds tbl] WHERE WorkOrderID =" & [Welds tbl].[WorkOrderID])
It Concatenates by the WorkOrder But I want It to Concatenate by DrawingNo AND WorkOrder. (I may have up to 5 drawings in each WorkOrder).

I am not sure how to add this extra statement in by DrawingNo into this code.
What I was looking for was to add onto the WHERE statement to this
CL-No:Concatenate("SELECT DISTINCT[ClosureWeldNo] FROM [Welds tbl] WHERE WorkOrderID =" & [Welds tbl].[WorkOrderID])
After the WHERE WorkOrderID =" & [Welds tbl].[WorkOrderID])

I tried these and the syntax is not right
CL-No:Concatenate("SELECT DISTINCT[ClosureWeldNo] FROM [Welds tbl]DrawingNo =" & [Welds tbl].[DrawingNo])
CL-No:Concatenate("SELECT DISTINCT[ClosureWeldNo] FROM [Welds tbl]WorkOrderID & DrawingNo =" & [Welds tbl].[WorkOrderID] &[Welds tbl].[WorkOrderID])

The ID Is a Number and the DrawingNo Is Text

There is some way to write this and I am not sure how to.

Thanks BBryan

 

[Beetle]

Can you post some info about the structure of the relevant tables as well as what other fields exist in the query in which you are using the Concatenate function?

Also, maybe an example of what results you are hoping to get.

 

[BBBryan]

Hi Beetle,
I have a Form - WorkOrder fm in that I have WorkOrderID & WorkOrders
I have another SubForm Welds Fm in that I have WorkOrderID,WeldsID, DrawingNo, WeldNo, TestPackNo and ClosureWeldNo. Related is the 2 WorkOrderID,s

I want to Concatenate ClosureWeldNo and get this result
WorkOrderID / WorkOrderNo / DrawingNo / ClosureWeldNo
1 8100 8167-44
1 8100 8167-44 CL-01
1 8100 3455-55
1 8100 3455-55 CL-01, C-L02
1 8100 8167-44 CL-01, CL-02 CL-03
2 3477 1336-98
2 3477 1336-98 CL-01


WHERE WorkOrderID =" & [Welds tbl].[WorkOrderID])
I'm stuck on After the WHERE
I want the ClosureWeldNo to = WorkOrderID(Number) & DrawingNo(Text)

 

[Beetle]

Assuming that the [Welds tbl].[WorkOrderID] and [DrawingNo] fields are in the query, you can try;

Concatenate("SELECT DISTINCT [ClosureWeldNo] FROM [Welds tbl] Where [WorkOrderID]=" & [Wels tbl].[WorkOrderID] & " And [DrawingNo] =""" & [DrawingNo]) & """")

 

[BBBryan]

Hi Beetle,Thanks for your help
I tried that and I get a
syntax error in string in query expression WorkOrderID = 24 And DrawingNo = 8167-44
Not sure what that means

 

[BBBryan]

Hi Beetle,
Thanks for your help
I did get the code to work
I have expression and not by Group by


Thanks again
Bryyan