Eliminating Duplicate values in a combobox (2002)

Topic

New Reply

I have a combo box that gets it’s values from entries from the Date field of tblCustomers. I made a little popup form that you select cboDateBeg and cboDateEnd and these values are used as a parameter in a query. this selects those dates and then that resultant query is used in a listbox lstCustomers.
The problem I have is if there are several dates the same i.e 1/26/02; 1/26/02 etc. these are listed in the combo box. I only want one instance of 1/15/02. I used default = unique values and tried unique records-no joy.
Frank

Reply | Quote

Replies

Open the list in design view.
Go to the rowsource and click on the symbol to the right (…)
This will show the query design for the list.
At the top of the screen, press the symbol that looks like an E only backwards, this will add an extra row in the query.
Make sure it says “Group By” and preview it.
If you’re still getting duplicates, it’s because of the other field in the query.

That should get you started in solving your prob

Dave

Reply | Quote

That did it.
I never knew you could use this function to eliminate duplicates very interesting. Don’t know why it works but very nice solution.
Frank

Reply | Quote

If the dates in the field are general dates (i.e., they’re entered by using the Now() function, which includes time) then using Unique Values won’t work the way you want it to without a bit more work. You’ll need to wrap the [Date] field in a Format(tblCustomers.[Date],”Short Date”), which will give you a short date (no time portion) and the unique values should work on that. You’ll need to make sure your query criteria using the date parameters from the form is something like this:

>= CDate(forms!frmPopup!cboDateBeg) And < DateAdd("d",1, forms!frmPopup!cboDateEnd)

If you add a day to End Date and use “less than”, you’ll get all the records for the actual End Date , right up to midnight when the next day begins.

Reply | Quote

Thanks Charlotte

Reply | Quote