Cannot "Group By" Instr function in SQL 3

[Poduska]

Have been given a form with a combo box which no longer works.

The combo box is to show one instance of each string found in a field for ever record in a table.

For example:

For records
"ABC-12345"
"ABC-67890"
"DEF-12345"
"DEF-67890"

should return 

"ABC" 
"DEF"

one instance of everything to the left of the Dash.

This would appear to be simple.

This works but shows duplicates.

SELECT Left$([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1) AS [Left of Dash]
FROM tblNTIs;

So I get
"ABC"
"ABC"
"DEF"
"DEF"

However when I attempt to use Group By this creates the error

[red]"Invalid Procedure Call"[/red]

The Group By code was created by the QBE grid.

SELECT Left$([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1) 
FROM tblNTIs
GROUP BY Left$([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1);

It does not matter if everything is in one query or I query the query which creates the duplicates (no Group By).

This would seem to be something you should be able to accomplish, any suggestions?

 

[SkipVought]

SELECT DISTINCT Left$([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1) 
FROM tblNTIs

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

This works for me as expected:

SELECT Left([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1) AS [Left of Dash]
FROM tblNTIs
GROUP BY Left([tblNTIs]![NTINumber],InStr(1,[NTINumber],"-",1)-1);

Do you have any records with no "-" in the value?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

The simplest way:

SELECT DISTINCT Left(NTINumber,InStr(NTINumber,"-")-1) AS [Left of Dash] FROM tblNTIs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

As SkipVought is always right and as I was STILL receiving my error I thought "What about garbage data?"

Apparently out of 6,000 records TWO do not have a DASH which is the character I am searching for using the InStr function.

Therefore I am am receiving a Zero from InStr and passing this on to the Left function. Assuming a NULL is returned from the Left Function when using Zero then this is causing an error with the Distinct.

Well that is my best guess.


SO, I had to either eliminate the "Did not find a dash" records (InStr = ZERO)

SELECT DISTINCT Left$([tbltest]![NTINumber],InStr(1,[tbltest]![NTINumber],"-",1)-1) AS [Left of Dash]
FROM tbltest
WHERE (((InStr(1,[tblTest]![NTINumber],"-",1))<>0));

OR if no dash is found then change the InStr value to the entire length of the field and return everything

Left of Dash: 
Left$([tbltest]![NTINumber],
IIf(InStr(1,[tbltest]![NTINumber],"-",1)=0,
      Len([tbltest]![NTINumber])+1,
      InStr(1,[tbltest]![NTINumber],"-",1))-1)

Either way eliminates the errors

Thanks SKIP

 

[PHV]

Why not simply this ?

SELECT DISTINCT Left(NTINumber,InStr(NTINumber & "-","-")-1) AS [Left of Dash] FROM tbltest

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

Dhookom,
I sure wish I would have read you post sooner as you pointed out EXACTLY what my problem was.

My wife would say I have more than just ONE problem but that's another story......


Thanks all.

 

[SkipVought]

As SkipVought is always right ...

Bad categorical assumption!

Right of center most often, would probably be much closer to reality.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}