Search by Distinct Name

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a form which allows you to enter in data, hit
submit, and it shows the result in another form. Each
option has a drop down menu for you to select and it
queries based on the selection. Everything works correct
for the options I enter in (meaning a drop down will have
the option of yes, no, na..you select one).

What I want to be able to do is a search by a field that
draws data from table...but only shows the name once on
the drop down menu. Meaning, on my table, I will have
Assigned To field, which would display the names John Doe,
Jane Doe, Jim Smith, etc etc. Each name may have 2 or 3
entries in the table. I would like to be able to have a
drop down menu that draws the names, but only shows hte
name once, and able to query that name by what is selected.

Here is a portion of the code I currently have:

Dim sfilter As String

If Not IsNull(cmbCenter) Then
sfilter = "(
='" & cmbCenter & "') and "
End If

If Not IsNull(cmbCustType) Then
sfilter = sfilter & "([Customer Type] ='" &
cmbCustType & "') and "
End If


If Not IsNull(cmbCurrentStatus) Then
sfilter = sfilter & sfilter & "([Current Status] ='" &
cmbCurrentStatus & "') and "
End If

If Len(sfilter) Then sfilter = Left(sfilter, Len(sfilter) -
5)

DoCmd.OpenForm "frmResults", wherecondition:=sfilter

What would I need to do to complete my task? Thanks!!!​
 
Hi Dave

Add the keyword "DISTINCT" to your combo's RowSource query. For example:
Select DISTINCT [Customer Type] from YourTable;

This will ensure that each value appears only once.
 
-----Original Message-----
Hi Dave

Add the keyword "DISTINCT" to your combo's RowSource query. For example:
Select DISTINCT [Customer Type] from YourTable;

This will ensure that each value appears only once.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form which allows you to enter in data, hit
submit, and it shows the result in another form. Each
option has a drop down menu for you to select and it
queries based on the selection. Everything works correct
for the options I enter in (meaning a drop down will have
the option of yes, no, na..you select one).

What I want to be able to do is a search by a field that
draws data from table...but only shows the name once on
the drop down menu. Meaning, on my table, I will have
Assigned To field, which would display the names John Doe,
Jane Doe, Jim Smith, etc etc. Each name may have 2 or 3
entries in the table. I would like to be able to have a
drop down menu that draws the names, but only shows hte
name once, and able to query that name by what is selected.

Here is a portion of the code I currently have:

Dim sfilter As String

If Not IsNull(cmbCenter) Then
sfilter = "(
='" & cmbCenter & "') and "
End If

If Not IsNull(cmbCustType) Then
sfilter = sfilter & "([Customer Type] ='" &
cmbCustType & "') and "
End If


If Not IsNull(cmbCurrentStatus) Then
sfilter = sfilter & sfilter & "([Current Status] ='" &
cmbCurrentStatus & "') and "
End If

If Len(sfilter) Then sfilter = Left(sfilter, Len (sfilter) -
5)

DoCmd.OpenForm "frmResults", wherecondition:=sfilter

What would I need to do to complete my task? Thanks!!!​
Click to expand...



.
Oh wow...I had this exact same thing at one point I​
Click to expand...

think, but I mispelled my field or something, cause it
didn't work for me...however, it works great
now...thanks!!!!!​
 
Back
Top