MS Access query regex and loop

Posted on 2012-09-07
Last Modified: 2012-09-07

I'm new to VBA but I've coded some stuff before. I'm wondering if someone can provide some basic code to help me.

I have this SELECT statement:

SELECT BillItemLine.[TimeCreated], BillItemLine.[TxnDate], BillItemLine.[APAccountRefListID], BillItemLine.[APAccountRefFullName], BillItemLine.ItemLineSeqNo, BillItemLine.[ItemLineItemRefListID], BillItemLine.[ItemLineItemRefFullName], BillItemLine.[ItemLineDesc], BillItemLine.[ItemLineQuantity], BillItemLine.[ItemLineCost], BillItemLine.[ItemLineAmount], BillItemLine.[ItemLineCustomerRefListID], BillItemLine.[ItemLineCustomerRefFullName], BillItemLine.[ItemLineClassRefListID], BillItemLine.[ItemLineClassRefFullName], BillItemLine.[ItemLineBillableStatus], BillItemLine.[TxnNumber], BillItemLine.[VendorRefListID], BillItemLine.[VendorRefFullName], BillItemLine.[TxnID]
FROM BillItemLine
WHERE (((BillItemLine.[VendorRefFullName]) Like "*suns*") AND ((BillItemLine.[TxnID])="20A8D-1325181637"));

The result is about 10 records. How do I get the count of the records and I guess I would save as Dim num_records As Integer and use a for loop? I know how to do this in PHP but still learning VBA.

I need to search the Field BillItemLine.[ItemLineItemRefFullName] which contains text in the format "Music Lessons:Smith, John" and like "Music Lessons:Lee, Wendy". I have to get the text after "Music Lessons:". So, for the first example, I would get the text "Smith, John". I guess I would save the result of "Smith, John" as a variable Dim name As String in the loop going through the records.

Then I would do a SELECT from table Customer:
SELECT Customer.[ListID], Customer.[FullName], Customer.[Name]
FROM Customer
WHERE (((Customer.[Name])="Smith, John"));

The  Customer.[ListID] is used to SET BillItemLine.[ItemLineCustomerRefListID].

dbs.Execute "UPDATE BillItemLine " _
        & "SET ItemLineCustomerRefFullName = 'Smith, John' , ItemLineCustomerRefListID = '80000431-1322772965'" _
        & "WHERE (((BillItemLine.[VendorRefFullName]) Like '*su*') " _
        & "AND ((BillItemLine.[TxnID])='20A8D-1325181637') AND " _
        & "((BillItemLine.[ItemLineSeqNo])='9') );"

I tried to run the above and I get a Run-time error '3464': Data type mistmatch in criteria expression. Both fields ItemLineCustomerRefFullName and ItemLineCustomerRefListID are text data types.

If you could help me alone with some example code I think it'll get me started.

Thank you and any help is appreciated.
Question by:Victor Kimura
    LVL 9

    Accepted Solution

    I don't know exactly what you are trying to do with the Initial Query, but I'm guessing you want to do something with each row returned?

    If so you can just use not EOF (end of file)
    Set RS = curdb.OpenRecordset("YOURSELECT QUERY")
        Do Until RS.EOF
            Do what ever you want to do in here

    Or if you just want a count of the fields:

        Dim LTotal As Long

        LTotal = DCount("AnyFieldName", "TableName", "WhereClause")

    Author Closing Comment

    by:Victor Kimura
    Ok, thanks.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now