concatenate hookom with two figures.

  • Thread starter Thread starter ibo
  • Start date Start date
I

ibo

Hi all

I have to concatenate serial number according to part number and Coc No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;
 
Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Duane Hookom said:
Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


ibo said:
Hi all

I have to concatenate serial number according to part number and Coc No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
I don't understand what you mean by "For1:" and "For2:" or "It shows
H-32-0512-50 totally??". You state a fact and finish with question marks.

--
Duane Hookom
Microsoft Access MVP


ibo said:
Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Duane Hookom said:
Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


ibo said:
Hi all

I have to concatenate serial number according to part number and Coc No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Duane
I mean , need
for cocno1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1
for cocno2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

But your figures shows :
For cocno1:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For cocno2:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

If you look at the sample H-32-0512-50 shows totaly serial number....

Thanks


Duane Hookom said:
I don't understand what you mean by "For1:" and "For2:" or "It shows
H-32-0512-50 totally??". You state a fact and finish with question marks.

--
Duane Hookom
Microsoft Access MVP


ibo said:
Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Duane Hookom said:
Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


Hi all

I have to concatenate serial number according to part number and
Coc
No
Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Hi again Duane

Your figures is not chose parameter cocno.It is subsitute all serial numbers
in cocno's .
I am really confused how your formula doesn't choose parameter or am I
wrong?

Thanks for guidance.

ibo said:
Duane
I mean , need
for cocno1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1
for cocno2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

But your figures shows :
For cocno1:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For cocno2:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

If you look at the sample H-32-0512-50 shows totaly serial number....

Thanks


Duane Hookom said:
I don't understand what you mean by "For1:" and "For2:" or "It shows
H-32-0512-50 totally??". You state a fact and finish with question marks.

--
Duane Hookom
Microsoft Access MVP


ibo said:
Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


Hi all

I have to concatenate serial number according to part number and Coc
No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Why did you define a parameter that has the same name as a field? It will
never prompt you for a value since the table contains the value.
TRY:
PARAMETERS [Enter CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[Enter CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """ AND CocNo = " & [CocNo]) AS SerNo,
cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;

--
Duane Hookom
MS Access MVP


ibo said:
Hi again Duane

Your figures is not chose parameter cocno.It is subsitute all serial numbers
in cocno's .
I am really confused how your formula doesn't choose parameter or am I
wrong?

Thanks for guidance.

ibo said:
Duane
I mean , need
for cocno1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1
for cocno2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

But your figures shows :
For cocno1:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For cocno2:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

If you look at the sample H-32-0512-50 shows totaly serial number....

Thanks


Duane Hookom said:
I don't understand what you mean by "For1:" and "For2:" or "It shows
H-32-0512-50 totally??". You state a fact and finish with question marks.

--
Duane Hookom
Microsoft Access MVP


Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


Hi all

I have to concatenate serial number according to part number
and
Coc
No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty, Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use
parameter
of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Dear Duane,

Thanks a million.......

I really appreciate your patience and all your help.

At last it does what I want it to do!!

Once again thank you so much.

All the best,


Duane Hookom said:
Why did you define a parameter that has the same name as a field? It will
never prompt you for a value since the table contains the value.
TRY:
PARAMETERS [Enter CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[Enter CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste WHERE
PartNumber =""" & [PartNumber] & """ AND CocNo = " & [CocNo]) AS SerNo,
cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;

--
Duane Hookom
MS Access MVP


ibo said:
Hi again Duane

Your figures is not chose parameter cocno.It is subsitute all serial numbers
in cocno's .
I am really confused how your formula doesn't choose parameter or am I
wrong?

Thanks for guidance.

ibo said:
Duane
I mean , need
for cocno1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1
for cocno2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

But your figures shows :
For cocno1:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For cocno2:
H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

If you look at the sample H-32-0512-50 shows totaly serial number....

Thanks


I don't understand what you mean by "For1:" and "For2:" or "It shows
H-32-0512-50 totally??". You state a fact and finish with question marks.

--
Duane Hookom
Microsoft Access MVP


Thanks for response Duane. your sql result show:

For1:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-39-0001-12 ser no:1

For2:

H-32-0512-50 ser no:1,2,3,5,7,8,9
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

It shows H-32-0512-50 totally?? ..Is there any other trick?.

Thanks for help


Try:
PARAMETERS [CocNo] Text ( 255 );
SELECT PartNumber, Description, Qty, CocNo
FROM Liste
GROUP BY PartNumber, Description, Qty, CocNo
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM Liste
WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
ORDER BY cl.PartNumber, cl.CocNo;


--
Duane Hookom
MS Access MVP


Hi all

I have to concatenate serial number according to part number and
Coc
No

Part Number Description Qty CoC No Serial Number
H-32-0512-50 PIN 1 1 1
H-32-0512-50 PIN 1 1 2
H-32-0512-50 PIN 1 1 3
H-32-0512-50 PIN 1 2 5
H-32-0512-50 PIN 1 1 7
H-32-0512-50 PIN 1 1 8
H-32-0512-50 PIN 1 1 9
H-37-4122-01 INDUCTOR ASSEMBLY 1 2 6
H-39-0001-06 RECEIVER/EXCITER GREEN 1 2 1
H-39-0001-12 RECEIVER/EXCITER(GREEN) 1 1 1


my cl query is below:
PARAMETERS [CocNo] Text ( 255 );
SELECT DISTINCTROW Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo, Liste.SerialNumber
FROM Liste
GROUP BY Liste.PartNumber, Liste.Description, Liste.Qty,
Liste.CocNo,
Liste.SerialNumber
HAVING (((Liste.CocNo)=[CocNo]));


I tried to figure out below sql.
SELECT cl.PartNumber, Concatenate("Select SerialNumber FROM cl WHERE
PartNumber =""" & [PartNumber] & """") AS SerNo, cl.CocNo
FROM cl
GROUP BY cl.PartNumber, cl.CocNo;


But it is not working shows run time error.I need to use parameter
of
CocNo:1 and 2.
If I coud not use parameter ,I figured out hookom concatenate

for coc1:
H-32-0512-50 ser no:1,2,3,7,8,9
H-39-0001-12 ser no:1

for coc2:
H-32-0512-50 ser no:5
H-37-4122-01 ser no:6
H-39-0001-06 ser no:1

I would appreciate if I could get a quick response

ibo
 
Back
Top