Unique Values from Access - Very difficult!

Dear all,

I'm trying to create a validation list with data from access. The code
below already do it, but I have a big problem: in the field of access
database I can have duplicate data...

table: period

fields
year_month         year_quarter         year
200701                  200701               2007
200702                  200701                2007
200703                  200701                2007
200704                  200702                2007
..
..
..
And the code below brings duplicate data. How can I solve this
problem?
I wouldn't like to bring all information to excel and after transform
it to unique values only... Is there other way to do it?

Thanks a lot!!!

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
 ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As
Boolean) As ADODB.Recordset

Dim strConnection As String
Dim filenm As String

On Error GoTo ErrorHandler

filenm =3D ThisWorkbook.Path & "\controle_despesas.mdb"

strConnection =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & "Data Source=3D" &
filenm & ";"

Set RunQuery =3D New ADODB.Recordset
    With RunQuery
        .CursorLocation =3D adUseClient
        .CursorType =3D adOpenStatic
        .LockType =3D adLockBatchOptimistic
    End With


RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText

If blnConnected =3D False Then Set RunQuery.ActiveConnection =3D Nothing
Exit Function

ErrorHandler:
    MsgBox Err.Description
End Function

Sub expenses_period()

Dim rst As ADODB.Recordset
Dim strValidationList As String
Dim strValidationList2 As String
Dim strValidationList3 As String


Set rst =3D RunQuery("Select *", "From period", "", ";", False)
rst.MoveFirst
strValidationList =3D rst.Fields("year_month").UnderlyingValue
strValidationList2 =3D rst.Fields("year_quater").UnderlyingValue
strValidationList3 =3D rst.Fields("year").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
    strValidationList =3D strValidationList & ", " &
rst.Fields("year_month ").UnderlyingValue
    strValidationList2 =3D strValidationList2 & "," &
rst.Fields("year_quater ").UnderlyingValue
    strValidationList3 =3D strValidationList3 & "," & rst.Fields("year
").UnderlyingValue
    rst.MoveNext
Loop

MsgBox strValidationList, vbInformation
MsgBox strValidationList2, vbInformation
MsgBox strValidationList3, vbInformation

'Range("D6:IV6").Validation.Delete
'Range("D6:IV6").Validation.Add xlValidateList, , , strValidationList

End Sub

Thanks a lot!!!

Andr=E9.

0
gatarossi (81)
8/17/2007 11:41:15 AM
excel 39879 articles. 2 followers. Follow

2 Replies
757 Views

Similar Articles

[PageSpeed] 12

Do you include the DISTINCT statement in your SELECT clause? It will return 
unique values.

Example:
SELECT DISTINCT field1 FROM database;


Does that help?
***********
Regards,
Ron

XL2003, WinXP


"gatarossi@ig.com.br" wrote:

> Dear all,
> 
> I'm trying to create a validation list with data from access. The code
> below already do it, but I have a big problem: in the field of access
> database I can have duplicate data...
> 
> table: period
> 
> fields
> year_month         year_quarter         year
> 200701                  200701               2007
> 200702                  200701                2007
> 200703                  200701                2007
> 200704                  200702                2007
> ..
> ..
> ..
> And the code below brings duplicate data. How can I solve this
> problem?
> I wouldn't like to bring all information to excel and after transform
> it to unique values only... Is there other way to do it?
> 
> Thanks a lot!!!
> 
> Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
> String, _
>  ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As
> Boolean) As ADODB.Recordset
> 
> Dim strConnection As String
> Dim filenm As String
> 
> On Error GoTo ErrorHandler
> 
> filenm = ThisWorkbook.Path & "\controle_despesas.mdb"
> 
> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
> filenm & ";"
> 
> Set RunQuery = New ADODB.Recordset
>     With RunQuery
>         .CursorLocation = adUseClient
>         .CursorType = adOpenStatic
>         .LockType = adLockBatchOptimistic
>     End With
> 
> 
> RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
> strOrderBy, strConnection, , , adCmdText
> 
> If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
> Exit Function
> 
> ErrorHandler:
>     MsgBox Err.Description
> End Function
> 
> Sub expenses_period()
> 
> Dim rst As ADODB.Recordset
> Dim strValidationList As String
> Dim strValidationList2 As String
> Dim strValidationList3 As String
> 
> 
> Set rst = RunQuery("Select *", "From period", "", ";", False)
> rst.MoveFirst
> strValidationList = rst.Fields("year_month").UnderlyingValue
> strValidationList2 = rst.Fields("year_quater").UnderlyingValue
> strValidationList3 = rst.Fields("year").UnderlyingValue
> rst.MoveNext
> Do While Not rst.EOF
>     strValidationList = strValidationList & ", " &
> rst.Fields("year_month ").UnderlyingValue
>     strValidationList2 = strValidationList2 & "," &
> rst.Fields("year_quater ").UnderlyingValue
>     strValidationList3 = strValidationList3 & "," & rst.Fields("year
> ").UnderlyingValue
>     rst.MoveNext
> Loop
> 
> MsgBox strValidationList, vbInformation
> MsgBox strValidationList2, vbInformation
> MsgBox strValidationList3, vbInformation
> 
> 'Range("D6:IV6").Validation.Delete
> 'Range("D6:IV6").Validation.Add xlValidateList, , , strValidationList
> 
> End Sub
> 
> Thanks a lot!!!
> 
> André.
> 
> 
0
RonCoderre (135)
8/17/2007 12:16:00 PM
Dear Ron,

Thanks a lot, It looks easy but nobody knowns, I have tried to obtain
this answer for a long time...

Thanks a lot!!!

Andr=E9.


0
gatarossi (81)
8/17/2007 2:34:41 PM
Reply:

Similar Artilces:

Splitting Values
I have a long column with town state and zip code appearing like this Farmington, CT 06032 can a macro be made, or is there a command that will take the last five characters from each of these cells and put them somewhere else? If you have Farmington, CT 06032 in cell A1 Put this formula in B1 to get the rightmost 5 characters =RIGHT(A1,5) Vaya con Dios, Chuck, CABGx3 "Zip Codes" wrote: > I have a long column with town state and zip code appearing like this > > Farmington, CT 06032 > > can a macro be made, or is there a command that will take the last five ...

Access Help 06-29-07
I am a completly new user to Access and I have been asked to create a database for a big project we are working on. I am creating a form where a user would have to select a catagory and after they select a drop down box, below the radio button, would show a list associated with the selection. I have no idea how to do this. can some one help me please?? Ex. Categories A. B. C. D. When you click on A, a list shows up with job names. I appreciate any help. To be sure your tables are correct, you need: TblJobCategory JobCategoryID JobCategory TblJob JobID JobCategoryID JobName <Other j...

defining unique range of cells for different sheets as the same n.
I want to use the same name, month9, to refer the same range of cells, but on different sheets, how do i turn off the global define? When you define the name (Insert|name|Define), include the sheet name in the "names in workbook" box: sheet1!month9 or 'sheet 1'!month9 You can select your range and type this in the name box (to the left of the formula bar), too. KSAPP wrote: > > I want to use the same name, month9, to refer the same range of cells, but on > different sheets, how do i turn off the global define? -- Dave Peterson ...

Beginner: Is HWND unique?
Hi! When I start an application twice and ask each application for the handle of its main window (calling an inbuild COM function), are the returned dwords guaranteed to be different? Thanks a lot for your help! Volker -- For email replies, please substitute the obvious. It is not guaranteed to be the same or different. You might get the same HWND towice in a row, or might get different ones between two instances of your program. AliR. "Volker Hetzer" <firstname.lastname@ieee.org> wrote in message news:ed9gei$di9$1@nntp.fujitsu-siemens.com... > Hi! > When I start an ...

Importing Visio into Access #2
Is it possible to import Visio 2007 into Access 2007? Thanks Joy You've asked the equivalent of "can I make an apple pie from oranges?". Check out Database Wizard in Visio help. Al <Joy.Huggins@gmail.com> wrote in message news:7a7da118-f30e-4b7b-8018-243523c78a24@k41g2000yqn.googlegroups.com... > Is it possible to import Visio 2007 into Access 2007? > > Thanks > > Joy "AlEdlund" <aledlund@comcast.net> wrote in message news:19594892-7535-44E6-8C8B-014A8043D7B1@microsoft.com... > You've asked the equivalent of "can I make ...

pivottable does not sort date values?
Anyone know why excel 2000 does not sort date values? it lists in the following way: 01-2001 01-2002 01-2003 02-2001.... or=20 2001-01 2001-10 2001-11 ..=B7.. Yes. Your dates have been input as text. It is simply=20 sorting them by alphabetical order. Go back to your original data and find the column with the=20 dates. Create a new column and use =3Ddatevalue(a1) where=20 a1 is the reference to the cells with the misinterpreted=20 dates. Display the cells in the new column as dates. Now recreate your pivot table including this new column=20 and you will find you will be able to sort i...

OWA cannot refresh group member PF access in 15 min
When I change permissions on PF for user by adding user to security group (which has author permission), user cannot access this PF immediately. But he can access this PF in 15 min. Q: where is this parameter? The 15 min delay could be to do with the DS2MB process. How are you changing permissions and how are your clients accessing public folders? Nue "Engineer" <dsadsa@yandex.ru> wrote in message news:OEACmX%23KGHA.3984@TK2MSFTNGP14.phx.gbl... > When I change permissions on PF for user by adding user to security group > (which has author permission), user cann...

How can I define unique font per each Outlook profile?
We have several Outlook profiles on one computer and each user wants their own font. I noticed changing to one type of font in one profile affects all the other user profiles in Outlook. Is there a way to assign unique fonts to each Outlook profile? Many thanks experts... Each user would need their own Windows login.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

unique record numbers
I am trying to create a unique PIN for members in a table (not the primary key) that will be 4 digits long, and would like each new entry to be incremental. I am very new, and would appreciate a detailed instruction (though I can get around access fair enough). Any help would be appreciated. On Fri, 5 Mar 2010 19:19:44 -0800, Rev David Bissas <Rev David Bissas@discussions.microsoft.com> wrote: The "DMax + 1" technique should work for you. Google or Bing for it. -Tom. Microsoft Access MVP >I am trying to create a unique PIN for members in a table (not t...

Network Connections no access
trying to view network connections settings, bindings.. (little bit new to subject), but do "not" seem to have access to: control panel, network, (don't even see network link, where might find tabs: id, services, protocols, adapters, bindings) not sure if any true of some trojan or setting keeping from view, or not part of xp home (sp3), maybe some security application? or internet provider block item from view? (if relevant, have: verizon fast dsl, sbsd: spybot s&d, malware bytes anti-malware, spyware blaster..) am trying to check network/ internet se...

CString value
I want to know how to assign the CString value into VARIANT type. How to convert? VARIANT vt; CString st; vcteam <anonymous@discussions.microsoft.com> wrote in message news:05d501c3c0cc$7e644df0$a001280a@phx.gbl... > I want to know how to assign the CString value into > VARIANT type. > How to convert? > > VARIANT vt; > CString st; > CString st; .... VARIANT vt; vt.vt=VT_BSTR; vt.bstrVal=st.AllocSysString(); Thanks a lot. >-----Original Message----- >vcteam <anonymous@discussions.microsoft.com> wrote in message >news:05d501c3c0cc$7e644df0$a001...

printing zero values
I have a number of reports within a single spread sheet. Of these reports only those relating to fields with poistive values need to be printed. How can I avoid printing those fileds relating to zero vlaues? Dave - Try - Tools, Options, View, and uncheck zero values in the Windows Options section. HTH, Carole O "dave glynn" wrote: > I have a number of reports within a single spread sheet. Of these reports > only those relating to fields with poistive values need to be printed. How > can I avoid printing those fileds relating to zero vlaues? ...

how to lookup a value within a range and return a label
Wrecking my head this one: For example if the Label A is from 1 to 200 B from 201 to 1000 C from 1001 and above How can I lookup and return the correct label for a value. Is there a function for this, or does it require programming Put these values in cells A1:B3 1 A 201 B 1001 C put the value to look up in C1, and use this formula =VLOOKUP(C1,A1:B3,2) "jocmccoy" wrote: > Wrecking my head this one: > > For example if the Label > A is from 1 to 200 > B from 201 to 1000 > C from 1001 and above > > How can I lookup and return the correct label for a...

Cell Values in Headers & Footers
Is it possible to insert a cell value in a header or footer? Thanks, Micah Right_Click on a Tab, choose View Code, then double-click ThisWorkbook of your project.. Click on the Left-Box down-arrow select Workbook in the right_box down-arrow select Before Print Type in line 2 below: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = Range("A1").Value 'LeftFooter for example with Cell A1 Value End Sub HTH "Micah J" <anonymous@discussions.microsoft.com> wrote in message news:821ADBF8-B2CA-4115-8164-EF1460FFB1BC@microsoft...

Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList? I know there is an algorithm function "unique" performing on containers in Standard C++ library. Is there a similar function in MFC? What am I doing now to avoid adding same strings to container is: CStringList strList; ..... if (strList.Find("somestring") == NULL) strList.AddTail("somestring"); But I realized it may be an inefficient way. Any good sugestion? I would have used a CMap for this. Then, when all insertions were done, I might consider converting it to a CArray...

NetBank Security Update Killed Money Access?
NetBank recently forced users to change passwords and enter responses to some personalized security questions. Since than signup consist of entering userid/pw followed by a question like the first musical instrument you learned, favorite movie, book ...etc. It's really annoying. Worst of all, Money 2007 can't log in, even though I changed the password in Money. Could this be the stupid extra question? Has anyone else experienced it? Thanks. Almost certainly that is what is the problem. Until the Banks work out how to deliver to Money you could try downloading manually and inpu...

How do I format a row depending on the value of a cell in the row
I have a table set up and would like any row of the table where a particular cell in the same row is blank to be highlighted. In other words, for any cell in column J that is blank, I want the corresponding row to be highlighted. I'm trying the conditional formating 'Formula Is' but so far it's not working for me. Any ideas? Select the whole table, assume the table is A1:D10, so formula is should be =COUNTBLANK($A1:$D1)>0 replace A1 with the left uppermost cell in your table that you want to test for and D1 with the right uppermost cell, notice the mixed relative a...

Help with SUMPRODUCT & converting date to value
I have the following Excel calculation, shown below. It is searching cells A6:A1000 on various sheets for data which fall between a certain date/period range, and is then adding the values in cells L6:L1000 together to display on the summary. The problem I have is that the data in ranges L6:L1000 are supposed to be Dates, but the only way i can return a value without an error is to use a number (e.g. of 1) instead. How can I use Dates within cells L6:L1000, and still return the same totals? Many thanks in advance for any tips you can offer! =SUMPRODUCT((Office!L6:L1000)*(O...

how do i list values within a given range?
i am trying to list the values that my original data does not provide. E.g I have a range beginning with "ABC0001" and ends at "ABC0043". How do I write a query or to list the values not shown i.e "ABC0002" all the way to "ABC0042" as individual values in a seperate table? I would use VBA to loop through a recordset, and record the 'missing' values. Use Left$(), Mid$(), Right$() and/or InStr() functions to help seperate the numbers from the text. -- Steve Clark, Former Access MVP FMS, Inc http://www.fmsinc.com/consulting "Ify O.&...

Investment re-valuing
My 401(k) company uses fractional shares and fractional values. The accounts they use are not directly referenced on any particular exchange. I had to manually create the accounts, and I have to manually update the prices. recently, two of the investments were "re-valued". They reference it as a "Transfer" but all that really happens is all my shares for a given investment were listed as negative transactions with the current unit value (so that the dollar value of the transaction was negative). Then they immediately followed that with a positive transaction for the ...

How to search and add a value from sheet1 to sheet2
Hi, Let's say you have one sheet with a list of products. Column1 = product name Column2 = product number I would like to have a cell on Sheet2, where I can start typing something, and it would let me see a list based on what I typed so far. E.g when I type "Volks" it should show a list of all Volkswagen Cars. If I then choose the value, it should use this value and add the product number in the cell next to it. What function should I use, or how can I do this? Thanks, Roel Roel You could use Data Validation drop-down list for the selecting the product name and VLOOKUP ...

Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell multiple items, but I only need to show the vendor once so I can pull other information. There are also may be blanks in the rows if the vendor is inactive. Thanks! Let me add one more twist to this problem: The unique values also need to filter out where it may be 'VendorA - West' and 'VendorA - East' and combine them into one record. I only need it to show VendorA and not both records East and West (same company, just different drop ship location). Thanks! "Karl Burrows" <kfb1@spa...

Using Access Reports in other EXE file
Hello I'd like to use access reports from any access version XP, 2000 or 97; because of the easy way to build such reports. Have you ever found or built any solutions permitting to reuse such access reports objects in other exe files, developped in Visual Basic, Visual Studio or even Delphi.... Thanks a lot for your suggestions Hi. Most programming languages support launching an executable, so the Snapshot Viewer executable would be a good choice for viewing reports exported from Access as Snapshot (*.SNP) files. Access would not need to be installed on the computer in order to view ...

m_pDocument Doc/View unique id for each view
I'm using the standard mfc mainform with a doc/view. While I'm in the view portion I need to track the actions taken individually and separately for each view. So when when I click on view one I track that in a separate place than view two. I there a unique id or index for each view? Something like m_pDocument->ID? Thank you, Joshua <jtfaulk@eudoramail.com> wrote in message news:1111085780.370621.117620@f14g2000cwb.googlegroups.com... > I'm using the standard mfc mainform with a doc/view. While I'm in the > view portion I need to track the actions taken...

Access 2002 front end works fine in full version
Folks, originally posted 2 days ago. Since then, I tried running in the runtime environment on my computer, again performs flawlessly, but fails on machines imaged with runtime version..... I work for a government department with strict controls on the computer and network. I have built an Access 2003 (2000 file format) DB that has a front and back end. The front end has a switch and user toolbar, and the majority of the app works fine. The front end performs flawlessly on my machine with a full version. The major data entry screen has a series of fields and a subform wi...