Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 677
  • Last Modified:

MS Access query regex and loop


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.
Victor Kimura
Victor Kimura
1 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")
Victor KimuraAuthor Commented:
Ok, thanks.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now