Oracle Function Lexical Match

Finds a match if every word in one string is in the other string:

CREATE OR REPLACE FUNCTION LEXICAL_MATCH (STR1   IN VARCHAR2,
                                                 STR2   IN VARCHAR2)
   RETURN NUMBER
IS
   THE_RESULT    NUMBER := 0;
   POS_SPACE     NUMBER;
   POS_WORD      NUMBER;
   SINGLE_WORD   VARCHAR2 (250);
   -- NOT CASE SENSITIVE COMPARE
   STRING1       VARCHAR2 (250) := UPPER (STR1);
   STRING2       VARCHAR2 (250) := UPPER (STR2);
BEGIN
   -- TEST IF ALREADY A MATCH
   IF STRING1 = STRING2
   THEN
      RETURN (1);
   ELSE
      -- TEST IF THERE IS A SPACE IN THE STRING
      POS_SPACE := INSTR (STRING1, ' ');

      IF POS_SPACE <> 0
      THEN
         LOOP
            SINGLE_WORD := SUBSTR (STRING1, 1, POS_SPACE - 1);
            STRING1 := SUBSTR (STRING1, POS_SPACE + 1);
            POS_WORD := INSTR (STRING2, SINGLE_WORD);

            IF POS_WORD = 0
            THEN
               THE_RESULT := 0;
            ELSE
               THE_RESULT := 1;
               -- REMOVE WORD FROM STRING2 - 3 SCENARIOS: WORD AT THE FRONT, MIDDLE, OR END - DON'T WORRY ABOUT REMOVING SPACES IN STRING2  
               STRING2 :=
                  SUBSTR (STRING2, 1, POS_WORD - 1) || SUBSTR (STRING2, POS_WORD + LENGTH (SINGLE_WORD) );
            END IF;

            -- REPEAT IN THE CASE OF 3 OR MORE WORDS
            POS_SPACE := INSTR (STRING1, ' ');

            EXIT WHEN POS_SPACE = 0 OR THE_RESULT = 0;
         END LOOP;

         IF TRIM(STRING1) <> TRIM(STRING2) -- CHECK IF STRING2 STILL HAS MORE WORDS LEFT, IF NOT THE REMAINING WORDS SHOULD MATCH
         THEN
            THE_RESULT := 0;
         END IF;

         RETURN (THE_RESULT);
      ELSE
         RETURN (0);                                 -- NO MATCH AND NO SPACES
      END IF;
   END IF;
END;
/

Update or Append (Insert) a Single Record

What if you want to update a record, but if its not already in the table you want to insert it (Append query in Access).

One way is to check if the record is there with a select statement and then either update or append.

But easier is to run the update statement and then check the recordsAffected property of the database object to see if the value is equal to 0, if not run the append (Insert) query.

For example:

sqlUpdateStatement = "Update field1 where field 2 = somevalue"

dbs.execute sqlUpdateStatement , dbFailOnError

if dbs.RecordsAffected = 0 then
dbs.Execute sqlAppendStatement
end if

What about multiple records?

Well, then you'd have to know the count of the records you want updated (a select statement run priorly), then run the update, check if records affected was less, if so append where id not in (records already in the table).

Triggering an Event via VB Code - Name of event = string, ie dynamic

Lets say you're using a calendar control to populate a text box. (I recommend Allen Browns version). You set it up and everything works just fine, however you want the afterUpdate event to trigger after the calendar form updates the textbox value. Because the value is updated via code (visual basic) the event does not trigger.

I've researched for quite awhile trying to figure out how to make it trigger but I couldn't find a solution - raiseEvent? Seems to have something more to do with creating a new class with events and then raising them (dim withEvents aControl new control - never used this before).

callByName - they seems like a possibility but couldn't figure it out.

Eval (eventname as text) didn't work.

The only solution (that is NOT really dynamic) that works is a case or if statement that says if the control.name = the control name that has the event that I want to trigger then [call] thatControls_actualEventName.

Please note that the event (sub procedure) that you want to call from another form possible must then be a public sub (or function) instead of a private sub (or function)

Any better solutions - actual dynamic solutions?

Excel Pivot Tables - Changing External Data Source

I know this is Access, but Excel pivot tables linked to an Access DB are quite useful - but, how do you change the external source to a different Access DB?

I found it here and it bears repeating --> http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Objects/Q_22910802.html

Basically you create another pivot table in the same workbook and then change the source of the old to 'Another PivotTable report or PivotChart report'. Once you've done that you can delete the new pivot table. MS... always consistent.

Change Name to Last Name, First using a query

Use these functions in an update query:

Name: Right([Distributed To],Len([FullName])-InStr([FullName]," ")) & ", " & Left([FullName],InStr([FullName]," ")-1)

Of course this wont work well with those with a middle name...

Of course, you should save names as separate fields; lastname, firstname, middlenamesorinitial, etc.

A real name object should be something like this: surname, givennamefirst, givennamemiddle, calledbyname --- anything else? Or how about priorsurname (for the married or otherwise who have changed their name since employeement.

Combo Box Lookup that populates other fields besides the combo box

On rare occasions -- Lets say you want to populate a table of 'reviewers' using the employee table as a lookup. Your first instinct is to only capture the key of the employee table and use the employee table to display employee data. But the review table is static, that is, the tracking of when a employee reviewed something is done at one point in time. When the employee gets a promotion and a new title or moves to a different department, you don't want that information in the review table -- you want ot populate the same fields in another table in a different table.

You want to select the employee for example using a combobox to lookup the key field and save it in the combobox fields source. But also return various other values and populate other fields.

Set the source of the combo box to include the key column plus the other columns.

Use the after_update event of the combobox to populate the other fields:

Private Sub cb_Employee_FK_AfterUpdate()
Me.Department = cb_Employee_FK.Column(2)
Me.Title = cb_Employee_FK.Column(3)
end sub

Important to remember however is if the property Column Count isn't set to the number returned you wont have access to them using the column(#) property above.

But this displays too many columns in the when using the combobox for a lookup right!?

Hide the ones you don't want by setting the width property 0;1.5;0;0 for example.

Using Pictures in Reports and Forms

The Problem: Large Access database size even after compacting.

Evidently, Access converts pictures into bit map (.BMP) file and a small .jpg or other type of compressed picture is therefore expanded to a larger size. In addition, if you started with a large file that you then scaled to fit in the corner of a report or form the original size is still the size that is stored in your Access database. The result is, everytime you create an additional report in your database (perhaps copying from a already created report), that has two logo images in the reporter header and page header, your database increases in size by 2 MEGABYTES! (if the picture file, expanded is 1MB, which is not that big for a picture file).

Yikes!

The solution: There are two possibilities.

1. If the the database is local, or if the tables are on a shared file server, but the forms, reports, etc are local you can link to the picture instead of embedding it (this is one of the properties for the picture control on the form or report) - this requires the picture file to be on everyone's computer as well as the .mdb file. If the database is shared on the file server, you'll have to make sure the network speed is fast enough not to create an unacceptable delay when opening the reports.

2. The second option, if you embed the picture is to transform the picture to a bitmap or GIF file to begin with so at least you know how large the picture file is. Try copying and pasting the picture into Word, scaling it to the right size and and 'cut'ing and 'paste special' to select a different format. Bitmap will be big but keep some quality, HTML and GIF are smaller but lose quality and perhaps total number of colors.

To have more control you'll need to use a real graphics application such as GIMP. I thought one might be able to save the picture as a GIF file with a transparent background. However, I've read that this i still a problem as access will take a transparent background and add a background to it.