I am working in Access 2003 in Windows XP. I am using Visual Basic for Applications, using DAO to write my modules. What I need to do is write a module that will compare each row to see if they are equal to each other or not. Basically, equal numbers in succession indicate a "music chord" which is why I need to do this. For example, if the table is like so: Notes......... Chord
A.............. .......111
B.............. .......111
C.............. .......111
F.............. .......56
G.............. ......56
A.............. ......56
A.............. ......43
B.............. ......23
...
Then, I would need output like this:
<<A
B
C>>
<<F
G
A>>
A
B
And the module that I wrote which transposes the columns to rows, produces this output:
<<A B C>> <<F G A>> A B
And this is the correct format I am looking for.
The brackets around the note names indicate a music chord. According to the example above, what I need to do is compare the current recordset under "Chord", to the adjacent "Chord" recordset. If equal, put brackets around note A. Then compare the second recordset with the next recordset. If equal, do nothing. Then compare the 3rd recordset with the 4th recordset. Since it is NOT equal, put opposite backets around the note "c". And so on.
What I don't know how to do is fetch a row, analyze it, manipulate it and move to the next row using DAO. For simplicity, here is a piece of code that I would like to get working, in order to crack this: (Code does NOT work, I need to use an array but I do not know how to implement it.) -
Do Until rst.EOF
-
strChord = rst!Chord
-
If strChord = "111" Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('ItWorks' & '" & strNote & "')"
-
db.Execute sSQL
-
Else
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
-
db.Execute sSQL
-
End If
-
rst.MoveNext
-
Loop
-
The logic in words....If Chord = "111" then insert values "ItWorksX", into table Test under column Notes where X is a Note letter, for any Chord value that equals "111". So the output according to the above data set (if written correctly) would be: Notes
ItWorksA
ItWorksB
ItWorksC
F
G
A
A
B
I hope this is clear enough. I think I need to use an array "getrows?" but I am not sure how to use it. I looked all over the web for examples, but I think my situation is unique. I will continue to clarify if need be. Thanks in advance!
6 2206 Denburt 1,356
Recognized Expert Top Contributor -
Cnt=1
-
Do Until rst.EOF
-
strNote =rs!Note
-
strChord = rst!Chord
-
If strChord = strChord1 and Cnt=1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' & '" & strNote & "')"
-
db.Execute sSQL
-
Elseif strChord <> strChord1 and Cnt>1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
-
strChord1 =strChord
-
Cnt=1
-
else
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
-
db.Execute sSQL
-
End If
-
rst.MoveNext
-
Loop
-
It may need a little touching up but it should produce the desired results as I understand them.
Denburt 1,356
Recognized Expert Top Contributor
After I posted my previous I saw that iwas full of holes this one should be a bit closer.I do tend to over think things at times so if any else has a better idea... -
Cnt=1
-
Do Until rst.EOF
-
strChord = rst!Chord
-
strNote =rs!Note
-
if rs.bof then rs.movenext
-
strChord1 = rs!Chord
-
rs.moveprevious
-
end if
-
If strChord = strChord1 and Cnt=1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<'" & strNote & "')"
-
db.Execute sSQL
-
Cnt=Cnt+1
-
Elseif strChord <> strChord1 and Cnt>1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
-
strChord1 =strChord
-
Cnt=1
-
else
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
-
db.Execute sSQL
-
End If
-
rst.MoveNext
-
Loop
-
Thank you for getting back to me so quickly. I tried running the code, and all it did was output the Note letters. Furthermore, I think we need one more "else if" for the notes between the brackets. My addition is in bold: -
If rst.BOF Then
-
rst.MoveNext
-
strChord1 = rst!Chord
-
rst.MovePrevious
-
End If
-
If strChord = strChord1 And cnt = 1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' " & strNote & " ')"
-
db.Execute sSQL
-
cnt = cnt + 1
- ElseIf strChord = strChord1 And cnt > 1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ' )"
-
ElseIf strChord <> strChord1 And cnt > 1 Then
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & ">>')"
-
strChord1 = strChord
-
cnt = 1
-
Else
-
sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ')"
-
db.Execute sSQL
-
End If
-
rst.MoveNext
-
Loop
-
I think the code I added takes into account the notes in the chord between the brackets, like the B and the C in this example:
<<A B C D>>
Let me know if I am wrong. I appreciate your help, and I do understand the logic. This code still does not seem to be working.
Denburt 1,356
Recognized Expert Top Contributor
OK Cleaned it up tried and tested example below I think you will find this satisfactory. Let me know if you find a combination I missed. -
Set db = CurrentDb
-
Set rst = db.OpenRecordset("Table1")
-
cnt = 1
-
If Not rst.EOF Then
-
rst.MoveFirst
-
Do Until rst.EOF
-
strChord = rst!Chord
-
strNote = rst!Notes
-
rst.MoveNext
-
If Not rst.EOF Then
-
strChord1 = rst!Chord
-
Else
-
strChord1 = ""
-
End If
-
rst.MovePrevious
-
If strChord = strChord1 And cnt = 1 Then
-
sSql = "INSERT INTO Test (Notes) VALUES('<<" & strNote & "')"
-
db.Execute sSql
-
cnt = cnt + 1
-
ElseIf strChord <> strChord1 And cnt > 1 Then
-
-
sSql = "INSERT INTO Test (Notes) VALUES(""" & strNote & ">>"")"
-
db.Execute sSql
-
cnt = 1
-
Else
-
sSql = "INSERT INTO Test (Notes) VALUES('" & strNote & "')"
-
Debug.Print sSql
-
db.Execute sSql
-
End If
-
rst.MoveNext
-
Loop
-
End If
-
Well well, thank you very much, problem solved. I am surprised I was able to portray exactly what I needed done, and you solved this...the hardest part of my project.
Anyway, in case you are curious with what I am doing, I am trying to convert musicXML into lilypond format (lilypond is a free music typesetter which requires a special formatting, which then compiles the lilypond code into sheet music that is a PDF). Since I am a composer, converting musicXML into lilypond format would save me a tremendous amount of time rather than to manually do it by hand.
I will bump this thread if I have further questions, as I am working on this project currently, and so far, this was the biggest obstacle.
Again, thank you!
Denburt 1,356
Recognized Expert Top Contributor
My pleasure glad I could help :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Peter |
last post by:
Hi
how can I compare two byte arrays in VB.NET
Thank
Peter
|
by: Iain |
last post by:
Hi
Hopefully I am missing something really simple with this question, but
here goes.
I have two Bitarrays that I would like to compare. At the moment, I am
XORing one with the other and checking to see if the result has any 1s
in it (if so, the arrays are different). This seems to be faster than
comparing each bit of the two original arrays one at a time. But I
still have to iterate over each element of the array, and I'd like to
|
by: agent349 |
last post by:
First off, I know arrays can't be compared directly (ie: if (arrary1
== array2)). However, I've been trying to compare two arrays using
pointers with no success. Basically, I want to take three sets of
character strings from the user. Then I want to run through each
element and compare the two strings. If they match I print they
match... I'm having a bit of trouble with the actual loop through each
array using the pointers and comparing...
|
by: Taxi Driver |
last post by:
Hi Everyone -
Can I get your help with this, it is driving me crazy.
I have 2 arrays listed below:
@req=75
@req=76
@req=77
@req=78
---
@bid=75
@bid=76
|
by: Nathan Gilbert |
last post by:
I have a function that is returning a 2D array (declared using double pointers)
and I want to be able to append a row of data to the beginning and end of this
array without having to create a new 2D array and iterate through copy contents
from arrays into this new 2D array.
Example:
int ** someArray;
int * topRow;
int * bottomRow;
| |
by: Ole Nielsby |
last post by:
How does the GetHashCode() of an array object behave?
Does it combine the GetHashCode() of its elements, or does
it create a sync block for the object?
I want to use readonly arrays as dictionary keys, based on
their content, not their identity. Is this feasible using the
arrays directly, or do I need to wrap them in a struct that
handles GetHashCode and Equal? If so, is such a wrapper
present in the standard class library?
|
by: riauvision |
last post by:
Hello,
I have problem in writing ms Access vba code to compare two array taken from two different recordset. One is from table: Detail Orders (PK:,POID,Product.Code,Qty) and the other is from table (PK:InventoryID, POID, ProductID.Code, Qty).
I retrive the and store its values in an array and also the in another array.
My problem is How to compare the two arrays so that I can update the inventory?
My code :
|
by: Kristaps |
last post by:
Hi everyone!
I have some questions, maybe someone can help me...
I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this process faster and if it is possible in VB.
The idea is to compare two tables and if there is some distinction, then is written in third table. Some tables contains more than 200 000 rows and each row contain approximatelly 30 parameeters (columns)....
|
by: eman.abu.samra |
last post by:
Hi all,
i have encountered the strangest behavior. Check out this simple
program:
#include <stdio.h>
int main()
{
double time = 1;
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |