subquery

[dmcp]

hi
i have the below query that is not working .

select gro_code,grp_name from groups
where exists
(select * from groups where len(gro_code)<5)
i want to get all data in groups where no# of digits in gro_code<5

[FishVal]

hi
i have the below query that is not working .

select gro_code,grp_name from groups
where exists
(select * from groups where len(gro_code)<5)
i want to get all data in groups where no# of digits in gro_code<5

Hi, dmcp.

Won't a simple SELECT query with criteria for gro_code "length" do the job?
BTW, a more effective criteria (<5 digits) for positive integers interpreted as decimals looks like:
gro_code < 100000.

Kind regards,
Fish

[dmcp]

thanks for the hint
but i was wondering why the query didn't work

[FishVal]

What do you mean saying "the query didn't work"?

[dmcp]

i have an unbound form with 2 multiselect list boxes. grp & io.
the IO listbox is unbound and depends on the grp listbox but after finishing from the grp listbox i'm getting a null IO listbox.
the below code is tested and ok.

Dim V As Variant
Dim GRPS As String

If Me.grp.ItemsSelected.Count > 0 Then
For Each V In Me.grp.ItemsSelected
GRPS = GRPS & Me.grp.ItemData(V) & ","
Next V
GRPS = Left$(GRPS, Len(GRPS) - 1)
End If

If Len(GRPS) = 0 Then
GRPS = "like '*'"
Else
GRPS = "IN(" & GRPS & ")"
End If

SQLSTR = "SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.io r_date, "
SQLSTR = SQLSTR & " internal_orders.io_grp FROM internal_orders "
SQLSTR = SQLSTR & " WHERE internal_orders.io_grp " & GRPS

Me.io.RowSource = SQLSTR

[dmcp]

What do you mean saying "the query didn't work"?

the original query that i sent gives no data . i didn't know what's wronge with it

[FishVal]

the original query that i sent gives no data . i didn't know what's wronge with it

Me too. ))

select gro_code,grp_name from groups 
where exists 
(select * from groups where len(gro_code)<5) 
 

The query is expected to return all or no records depending on whether [gro_code] value in ANY record of [groups] table satisfies given criteria or it doesn't. This happens because ambiguous field name - gro_code, could be related to main query and subquery source table. In this particular case Access by default associates [gro_code] with subquery source table.

To disambiguate this you need to give one of the table instance an alias name and use it to explicitly refer to the field.

select gro_code,grp_name from groups as groups_alias
where exists 
(select * from groups where len(groups_alias.gro_code)<5) 
 

But in this case a simple query .

select gro_code,grp_name from groups
where len(gro_code)<5
 

will do the same for less money.

Regards,
Fish