Delete Chart

[sq75222]

I have the data base as below :

123 BOT 3
123 5
123 Top 2
abcde T1 4
abcde T2 4
abcde 5

Result in query to display 123= 10 and abcde = 13

 

[John W. Vinson]

I have the data base as below :

123 BOT 3
123 5
123 Top 2
abcde T1 4
abcde T2 4
abcde 5

Result in query to display 123= 10 and abcde = 13

Assuming that Field1 is "123 BOT" and Field2 is 3 in the first record:

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2)
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

 

[sq75222]

When i try to run it, there is Invalid procedure call

I have the data base as below :

123 BOT 3
123 5
123 Top 2
abcde T1 4
abcde T2 4
abcde 5

Result in query to display 123= 10 and abcde = 13

Assuming that Field1 is "123 BOT" and Field2 is 3 in the first record:

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2)
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

 

[John W. Vinson]

When i try to run it, there is Invalid procedure call

Sorry: missing a square bracket. Should be

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

If your table is named something other than "yourtable" or your fields named
something other than "field1" and "field2", adjust appropriately.

 

[sq75222]

same problem show out, and i just try to run the

SELECT Left([field1], InStr([field1], " ")-1),[field2],
FROM yourtable

the results show as below:
123 3
#Error 5
123 2
abcde 4
abcde 4
#Error 5

When i try to run it, there is Invalid procedure call

Sorry: missing a square bracket. Should be

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

If your table is named something other than "yourtable" or your fields named
something other than "field1" and "field2", adjust appropriately.

 

[John Spencer]

Try a simple modification to John Vinson's suggested query.

Add a space to the end of Field1 in the Instr. Left does not like negative
numbers - that is it will generate an error message if the second argument
is less than zero. Instr(1,[Field1]," ") will return zero if there is no
space in field1. If you subtract 1 from that you get a negative number.

Try the following. Also, I removed the extra comma in the Select clause
that you posted

SELECT Left([field1], InStr(1,[field1] & " ", " ")-1)
, [field2]
FROM yourtable

So eventually your query would read something like the following
SELECT Left([field1], InStr(1,[field1] & " ", " ")-1)
, Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr(1,[field1] & " ", " ")-1)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

same problem show out, and i just try to run the

SELECT Left([field1], InStr([field1], " ")-1),[field2],
FROM yourtable

the results show as below:
123 3
#Error 5
123 2
abcde 4
abcde 4
#Error 5

When i try to run it, there is Invalid procedure call

Sorry: missing a square bracket. Should be

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

If your table is named something other than "yourtable" or your fields
named
something other than "field1" and "field2", adjust appropriately.

 

[sq75222]

Many thank, it work

Try a simple modification to John Vinson's suggested query.

Add a space to the end of Field1 in the Instr. Left does not like negative
numbers - that is it will generate an error message if the second argument
is less than zero. Instr(1,[Field1]," ") will return zero if there is no
space in field1. If you subtract 1 from that you get a negative number.

Try the following. Also, I removed the extra comma in the Select clause
that you posted

SELECT Left([field1], InStr(1,[field1] & " ", " ")-1)
, [field2]
FROM yourtable

So eventually your query would read something like the following
SELECT Left([field1], InStr(1,[field1] & " ", " ")-1)
, Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr(1,[field1] & " ", " ")-1)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

same problem show out, and i just try to run the

SELECT Left([field1], InStr([field1], " ")-1),[field2],
FROM yourtable

the results show as below:
123 3
#Error 5
123 2
abcde 4
abcde 4
#Error 5

On Sun, 16 Mar 2008 18:28:00 -0700, sq75222

When i try to run it, there is Invalid procedure call

Sorry: missing a square bracket. Should be

SELECT Left([field1], InStr([field1], " ")-1), Sum([Field2])
FROM yourtable
GROUP BY Left([field1], InStr([field1], " ")-1);

If your table is named something other than "yourtable" or your fields
named
something other than "field1" and "field2", adjust appropriately.

 

[John W. Vinson]

Try a simple modification to John Vinson's suggested query.

Add a space to the end of Field1 in the Instr

Thanks, John... and <DUH!!!> for missing that; the examples did indeed have
values with no blank, and I've been bitten by that before.