By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

subquery

P: 12
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
Apr 12 '08 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Apr 12 '08 #2

P: 12
thanks for the hint
but i was wondering why the query didn't work
Apr 12 '08 #3

FishVal
Expert 2.5K+
P: 2,653
What do you mean saying "the query didn't work"?
Apr 12 '08 #4

P: 12
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
Apr 12 '08 #5

P: 12
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
Apr 12 '08 #6

FishVal
Expert 2.5K+
P: 2,653
the original query that i sent gives no data . i didn't know what's wronge with it
Me too. ))

Expand|Select|Wrap|Line Numbers
  1. select gro_code,grp_name from groups 
  2. where exists 
  3. (select * from groups where len(gro_code)<5) 
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1. select gro_code,grp_name from groups as groups_alias
  2. where exists 
  3. (select * from groups where len(groups_alias.gro_code)<5) 
  4.  
But in this case a simple query .
Expand|Select|Wrap|Line Numbers
  1. select gro_code,grp_name from groups
  2. where len(gro_code)<5
  3.  
will do the same for less money.

Regards,
Fish
Apr 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.