Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validating/checking a value (Word / Access 97 VBA)

    Using VBA in Word 97 I have a process that reads data from an Access Database whci I then want to compare with a given value to see if the given value is in the list. Currently,
    1) The the database values are stored in an array
    2) A FOR-NEXT loop then cycles through each of the array values to see if there is a match with the given value. If found then TRUE, if not then FALSE.

    As the list gets bigger the FOR-NEXT method would take more time.

    Does anyone have any suggestions for a more efficient way to do such a compare?

    many thanks for any suggestions,
    otk

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Could you require a match on that value in your database query? First question, how are you getting the Access data and if you are reading a whole table, is there any reason you could not use a query instead?

  3. #3
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    jscher2000,
    OK, that approaches it from the other angle and should work - be interesting what the overhead doing it this way would be i.e. would be using query many times as opposed to reading all values at start and cycling through them...as initially posted.

    I may not have my terms right but I was looking to see if an "array variable" could be populated with the values and be tested something like.

    testtext="A"
    if testtext IN Array("A","B","C")

    Where,
    "A","B","C" are the values read from the database
    IN is a term used just to illustrate a union type function.

    In the end if performance is not an issue then your suggestion of turning it around will suit me perfectly.

    Thanks,
    otk

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    I suppose it's only fair that I tackle your original question, then. <img src=/S/wink.gif border=0 alt=wink width=15 height=15> In brief, I don't know of a way to get all the field data out of a recordset other than one record at a time. As long as you have to examine each record anyway, it seems easiest to compare immediately rather than using an intermediate array.

    Well, actually now that I think about it, there is <A target="_blank" HREF=http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetstringmethod(recordset)ado.asp>the GetString method</A> of an ADO recordset which will jam all of the data into a single large string. You can choose your own field delimiter and search for a match including your delimiter (to make sure it isn't part of some other field). For example, follwing this syntax:

    strTemp = myRS.GetString(,, ColumnDelimiter, RowDelimiter, SwapForNullValue)

    you could use:

    strTemp = myRS.GetString(,, "!", "@", "#")

    and your string would look like this:

    rec1field1!rec1field2!rec1field3@rec2field1!rec2fi eld2!rec2field3@ etc.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Hi,
    Are you looking for the value in a specific field (you didn't mention how many fields you were returning - one or many)? If so, you could use the recordset's findfirst method.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Can you "filter" the recordset by creating a query that does not return so many rows?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Thanks jscher2000 , I'll investigate.

    otk

  8. #8
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    rory,
    Tks for feedback, pls see reply to Kevin

    otk

  9. #9
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Kevin,
    I think it best I outline more specifically whats happenning.
    1) From Word I do a search of all files in a nominated folder. Then I load each filename into an array.
    2) In Access I have a list of file types (extensions). From Word I read this list at the start (once) and populate an array - I now have an array of extensions that I want to learn more about.
    3) For each file found I test using a loop against the valus found in (2). If there is a match then get some properties, If not then move onto the next.

    Essentially, I wanted to see if there was a more efficient way to do step (3) e.g. rather than doing a loop (until found or EOF), use a single statement, maybe like MATCH in Excel 97. In english this would be - If filefound has a MATCH in current list then TRUE else FALSE.

    If I adopt the the initial reply by jscher2000 I would not bother reading the list from Access but testing each file found using a filter query against the database. While that would work, I am not sure about the performance implications especially if a number of people are doing this at once.

    I hope that better clarifies the process.

    thanks,
    otk

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Hi,
    In that case, I think the array is probably the fastest way to go as it should have the least overhead. I'd suggest using the Filter function on your array (assuming it's one-dimensional) as it's probably the fastest way of determining if there's a match.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Validating/checking a value (Word / Access 97 VBA)

    Oh, yes, I thought you only had to do the query once. Bad assumption on my part.

    Your instinct to use a string is, I think, a good one. If you only are retrieving the extension field from the database, you can use the GetString function. Otherwise, you can loop once and concatenate all the extensions into a string (preceded by a period; that seems to be a natural delimiter). You then can use Instr to test the entire set in one go. Advisable to make it case insensitive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •