Find/Replace Programmatically in a Module (Access 2000 Win 2000 SR-1)

Topic

New Reply

I am finalizing a project involving pulling data from Outlook to Access, where the source data is then parsed out to match a county-by-county query. There is one field on the table called ‘Categories’ that may have more than one County listed and my system depends on these counties being comma-separated (actually, like this: [Count], [County] <– comma and space seperated. I will get this formatting if people select the county list using the Categories button.

However, it is possible for someone to use '/' as a separator as that has been the 'old' way of doing things, so i'd like to test for this before building a new county-by-county list from the Outlook data. So, what i want to do is add some code to the table builder sub that selects the column holding the Categories information and do some search-and-replace stuff. I can find all sorts of things in VB Help re. recordsets, etc. but have not had much luck finding out how to specify a field in a recordset, and the search-and-replace routines i have found end up generating the Find dialog box, which is distracting at the least to end users.

as always, i find the online help a tad too abstract for implementation and the error message a whole lot too uniformative. I've looked around the Lounge for some solutions but nothing really fit the bill. So…

I thank you for any help you can provide!

Reply | Quote

Replies

I think the missing link here may be the Instr function which can be use to find any occurance of a particular character in a string value. With that you can check for the ‘/’ character or the ‘,’ character and thus you can parse the data into its components. As to working with a specific field in a table, you simply use the the syntax
myrec!FieldName = Value_I_Want_To_Assign_To_It
or
myVariable = myrec!FieldName
Hope this helps get you started – BTW, storing data in Outlook is always a pain as it doesn’t store it in any nice structured (or relational) way, so you are always stuck with this kind of thing.

Reply | Quote

hmmm… According to VB Help, Instr tells you the *position* of a character in a string, so that’s not quite it.

OTOH, I am starting to think this part of the project should be left alone: what I am trying to do is idiot-proof the incoming data from Outlook, but the only way Outlook would give ‘bad’ data is if the user doesn’t use the Categories list for the Counties: by typing in a county directly, for example. This is a Bad Thing because it leads to possible misspellings and possible non-alpha order county lists. The goal, really, is to enforce best practices, not necessarily to programmically assume and correct for the lack thereof — which is tough anyway, because there’s any number of ways someone might choose to deliminate counties and the only way to account for all of them is to do some complex pattern matching such that any instance of multi-county records is force-deliminated to ‘, ‘ style. But to do this, in the case where there is no space between two or more counties and you don’t know what the deliminator is, you have to search for the second capital letter in the string and call that the next county (and some counties have two words, underscore-separated, so that has to be trapped out).

It’s something of a philosophical issue: do you want to allow bad practices to go unpunished? Bear in mind, this has nothing to do with data loss; no real harm is being done — you can enter something funky in Outlook, it’ll port to Access but it won’t make it to the final report because it’s not ‘legal’. End user can correct this be re-entering the data in the Correct Manner. Also, we have a small shop here so the training on this point is fairly minimal.

I’ve worked on another method that is also not there yet programmically: i can build a simple query in Access that tests for the ‘/’ deliminator or the ‘,’ (comma with no trailing space) deliminator and, after the main table is built, i’m trying to open and count records in the queries: if there are any a message box pops up warning that Records A-Z won’t appear in the final report because of blah blah. in this case, the queries should be run for the recordcount read but the queries themselves should not open on the screen (ideally). note this is not a globally secure method as it doesn’t test for any possible delimination not equal to ‘, ‘ but it does remind the end user that they’ve subverted the rules. I am leaning towards this solution as it will reinforce the training.

To that end I have this:

Private Sub Command10_Click()
Dim dbs As Database, rst As Recordset

‘ Return reference to current database.
Set dbs = CurrentDb
‘ Open table-type Recordset object.
Set rst = dbs.OpenRecordset(“ContainsSlash”)
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing

End Sub

I keep getting Type Mismatch and the OpenRecordSet line is the offending one. Any clues on this?

Reply | Quote

What is “ContainsSlash”? If not a table, post the query here.
Another possibility, references.
Pat

Reply | Quote

ContainsSlash is a query (stored procedure) in the db that opens the target data and captures any record that has ‘/’ somewhere in it for the Categories field. Basically SQL:

SELECT * from tblOutlookContacts WHERE Categories LIKE “*/*”;

Reply | Quote

I presume the field Categories is a text field.
If not a references problem, I’m out of ideas.
Post your database taking out any confidential data first, so we can all have a go at this.
Pat

Reply | Quote

i don’t see why it would be a references problem. because of Type Mismatch? well… in general, i have another module that does the whole rst.OpenRecordSet and it’s fine. perhaps i need to run the SQL in the module rather than try to launch a Query. that shouldn’t be too difficult but at the moment i am bashing out code in another access project so will not worry about this particular problem for now. like i said, this is some adhoc validation stuff that can also be replaced altogether with a little training.

but yes the Categories field is a text field.

Reply | Quote

In reference to Type Mismatch error, it’s probably because you need to declare the database & recordset variables as DAO object variables. Ex:

Dim db as DAO.Database
Dim rst as DAO.Recordset

In A2K and later by default a reference is set to the ADO Object Library. To use DAO objects you need to set a reference to Microsoft DAO 3.6 Object Library and explicitly declare object variables with the type library qualifier (DAO in this case). This is necessary because ADO also has a Recordset object. An ADO recordset and a DAO recordset are two different types of objects, defined by two different type libraries and that’s why you get a type mismatch error.

In reference to problem with the Outlook data, it’d be useful to post a representative sample of “before” (the bogus records) and “after” (what you want it to look like after you fix it) – I’m not clear on problem. It may not be hard to fix by using standard string manipulation functions like Left, Right, Mid, InStr, etc.

HTH

Reply | Quote

oh, yeah. and here i am, religiously reading the AccessWatch newsletter. i’ll have to explicitly declare DAO recordsets. duh!! welp, I’ll start in on this next week. perverse as it may sound, i’m having too much fun with DSum’s right now on a new project.

here’s what the Outlook –> Access Categories data *has* to look like to function:

Callahan, Jones, Taylor
Williamson
Brewster, Jeff_Davis, Presidio
Bastrop, Fayette, Lee
Brazos, Burleson, Grimes, Leon, Madison, Robertson, Washington

In the past, people have used / to deliminate and that will never happen if people don’t type county lists directly into the Categories text field. somebody might, somehow, do that and that’s what i’m trying to trap out. And, since we’re talking computers here, the info won’t get manipulated correctly if the commas don’t have a trailing space. and i shudder to think of what other things people might type in if they felt like it. so, it’s a potential problem. trapping anything that doesn’t conform to the correct deliminator looks like a rather hairy pattern-matching function, as you’d have to look for consecutive Capitol letters (assuming no space for instance around a deliminator character of unknown type); some counties have two names (hence 2 Capitol letters). and it’s too late in the week for me to think about it right now.

thanks for all the feedback — lounge at ya later!

Reply | Quote

Sorry, I assumed you would jump to the next step, which involves using the Left, Right and Mid string manipulation functions to do the text parsing. Of course the entire process would then need to be recordset based manipulation in VBA, which can be a pain. A more fundamental question in my mind is why you want to continue to store data in Outlook – granted, everyone has it, but even using categories can be problematic, as people can alter the list of categories to suit their own whims. Life gets a bit easier if all this is being done in Exchange Server, but the nicer solution would involve a common database. That could of course mean everyone would need a copy of Access. Guess this is mostly rambling thoughts on a Saturday morning.

Reply | Quote