SimpleCSV() v2.0

 A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of this function is it doesn't work with parameter queries.

I have always pointed people to Leigh's Generic Recordset function to deal with parameter queries and avoid the "3061. Too few parameters" error.

Today, I was trying to help someone in the forum and decided to combine the two functions together. The following updated version of the SimpleCSV() function should be able to handle certain parameter queries. Please let me know if you find any bug.

Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://www.accessmvp.com/thedbguy
'v1.0 - 8/20/2013
'v2.0 - 8/25/2021 Handles parameter queries

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Variant
Dim strCSV As String

Set db = CurrentDb()

Set qdf = db.CreateQueryDef("", "SELECT * FROM (" & strSQL & ")")

With qdf
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
End With

Set rs = qdf.OpenRecordset

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Function

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with parameter queries, you can review my article on Parameter Query Basics.

The following SQL statement is an example of  a parameter query.

SELECT Title, FirstName, LastName, Position
FROM Employees
WHERE Department=[Enter Department]

The above query will prompt the user to enter a department and the result will show all the employees working for that department. So, users are free to enter any department they are interested to see, each time they run the query. But what if the user wants to see all the employees for more than one department?

Problem

Typically, in a non-parameterized query, the SQL statement might look something like this.

SELECT Department, Title, FirstName, LastName, Position
FROM Employees
INNER JOIN Detpartments
ON Employees.DeptID=Departments.DeptID
WHERE Employees.DeptID In(1,3,5)

In the above example,  we switched to using the foreign key (DeptID), which would be a more likely scenario anyway and also to make demonstrating the technique being presented a bit simpler to explain. Also, please note the In() clause in the above example is a shorthand for the following criteria (meaning, it's functionally the same).

WHERE Employees.DeptID=1 OR Employees.DeptID=3 OR Employees.DeptID=5

So, our goal is to prompt the user to enter multiple DeptID using a parameter. One might be tempted to try the following as a query criteria.

WHERE DeptID In([Enter DeptID])

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails. Of course, the obvious solution is to use the following criteria.

WHERE DeptID=[Enter Dept1] OR DeptID=[Enter Dept2] OR DeptID=[Enter Dept3]

The above approach will work, but the user will be prompted to enter a single DeptID three times, which would be fine but what if the user wants to know more (or less) than three DeptID? As you can see, this approach is not very flexible.

Solution

Instead of using the above approach, we could try either of the following approaches.

WHERE InStr(";" & [Enter DeptID (separated by a semicolon ';')] & ";", ";" & DeptID & ";")>0

or

WHERE ";" & [Enter DeptID (separated by a semicolon ';')] & ";" Like "*;" & DeptID & ";*"

When the user runs the query and gets the prompt, we are expecting the user to enter something like 1;3;5, which in our criteria will be converted into ;1;3;5; (I prefer to use semicolons but using commas would work the same way).

The first approach uses the InStr() function to search the current record's DeptID, encapsulated within a pair of semicolons, within the user's input string from the parameter prompt. If a match is found, the result of the function should be a number greater than zero (0), which would make the criteria evaluate as True and include the current record in the query's result set.

The second approach simply uses the Like operator with a pair of wildcard characters to accomplish the same effect as the first approach. I prefer to use the second approach because it is simpler and avoids a function call, but either approach will work as intended.

Caveat

Unfortunately, this solution is not perfect. The first drawback is you'll have to "train" your users to enter their input using the exact format your query is expecting (e.g. 1,3,5 or 1;3;5 or 1 OR 3 OR 5). Otherwise, the query will not work if the user enters their input using any other format (e.g. user enters 1,3,5 but you expect 1;3;5). Adding the expected format in the parameter prompt, as in the example above, should help a little bit in reminding the user what format is expected.

The other drawback I would like to point out affects any parameter query, in general. As I mentioned in my article on Parameter Query Basics, there is really no way to validate the user's input when using a parameter query. Your query might be asking users to enter a DeptID in the parameter prompt but nothing will stop them from making a mistake and end up entering a DeptName instead. For this reason, it is considered "best practice" to use an unbound form to accept user input for your query parameters. Using a form will allow you more control over the user's input. You can easily validate their input when you use a form.

The last point I'll mention, if you do end up using parameters in your query, is to make sure you declare the data type for each parameter. This is more applicable when each parameter is a single value. To declare the parameter's data type, you can click on the Parameters button on the Design Ribbon to open the Query Parameters window.

To declare the parameter data types manually, you can add the PARAMETER clause at the beginning of your query's SQL statement. For example:

PARAMETERS [Enter DeptID] Long, [Enter Department] Text ( 255 );
SELECT ...
FROM ...
etc...

Please note, it is very important to end the PARAMETER clause with a semicolon, or you will get a syntax error. Also, the PARAMETER clause is typically required in Crosstab queries.

Conclusion

Parameter queries allow us to create semi-dynamic queries, which could help reduce the number of queries we need to create if all we need to change is the criteria condition of the query, based on user input. It is even possible to create a parameter query that accepts multiple values from the user. However, parameter queries have some limitations that it is highly recommended to use input forms whenever possible. Still, parameter queries are good as quick solutions for those times when the requirement is simple enough with very low chance of user input errors.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statement, so here goes...

Delimiters

Before we start building a string in code, I think it's important to understand the concept of "delimiters" first. When you declare a String variable in VBA, the value you assign to it must be delimited with a double-quote character. For example:

strVariableName = "Some String Value"

That should be easy enough to understand, but the problem comes when building a string while concatenating field values into the string as well. Table fields have different data types and some data types need a specific delimiter when using them in code. The most common data types and their corresponding delimiters are as follows:

Data Type	Delimiter
Text	Double-quote (") or Single-quote (')
Date/Time	Octothorpe (#)

Here are some examples of their uses:

rs!CompanyName = "UtterAccess"

rs!CompanyName = 'UtterAccess'

rs!VisitDate = #4/29/2014#

rs!VisitTime = #10:30 AM#

As you can probably imagine, the problem we will try to tackle in this article is in how to build the string with Text data types within it since both String variables and Text data types use the same delimiters that usually causes some confusion during coding.

Building the string one step at a time

We are going to discuss an approach that was presented by another UtterAccess member when tackling this issue. First, let's look at the final version of the string we're trying to build. The goal will be to write code that will result in a string value that looks like this:

INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)

In our code, we will assume that each value to be inserted into the table are held in variables called strCustLastName, strCustFirstName, lngCustNo, and dteDOB. We will then insert these variables in their proper places to replace the hard values we used above.  Let's get started:

Step 1 - Naturally, the first thing to do is to delimit the whole value because it is a string, so let's add a double-quote at the beginning and at the end of our string:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)"

Step 2 - Next, we will replace the  hard values with the variables that represent each hard value:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (strCustLastName, strCustFirstName, lngCustNo, dteDOB)"

Step 3 - Now, the variables must be concatenated into the string so that VBA will then replace them with the values they represent when the code executes:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (" & strCustLastName & "," & strCustFirstName & ","
& lngCustNo & "," & dteDOB & ")"

Notice how we enclosed each segment of the concatenation with the double-quote delimiters because we are now trying to combine multiple strings together.

Step 4 - At this point, we'll need to add the proper delimiters for each data type for our variables. Remember that strCustLastName and strCustFirstName variables are for the CustLastName and CustFirstName fields respectively, which are Text fields and therefore need a double or a single quote for its delimiters. The lngCustNo variable represents the value for the CustNo field, which is a Number data type and therefore does not need any delimiter. And, the dteDOB variable is for a Date/Time field, which requires the octothorpe (hash mark):

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (""" & strCustLastName & """,""" & strCustFirstName & ""","
& lngCustNo & ",#" & dteDOB & "#)"

Notice how we actually used two double-quotes to delimit the Text data types (strCustLastName and strCustFirstName)? Now would be a good time to discuss some possible issues with Text data types when concatenating them into string values. If the hard values we expect will never contain a single-quote within it, then we could have just use a single-quote as delimiters and be done with it. However, remember that for our example, the strCustLastName variable actually represents the hard value "O'Hara." So, using a single-quote to delimit that variable will fail because the hard value also contains a single-quote. So to avoid that problem, we need to use a double-quote as a delimieter. But why use two of them? Well, if we used just one double-quote, then VBA will interpret it as the delimiter for our string (the whole thing that we're building) instead of just for the variable. So, we used two double-quotes to tell VBA to replace it with one double-quote when it interprets the code during execution.

Step 5 - Actually, that's it. We're done! There's no Step 5. However, one last item that I need to point out is that I didn't use any "continuation" character in the above code examples to reduce any confusion while learning the basic concept of building the string in code. So, the above code samples should be considered as one long line even if they were displayed as multiple lines above. If you wanted to break them down into multiple lines in your code using the continuation character, it might look something like this:

strSQL = "INSERT INTO tblCustomers (CustLastName, CustFirstName, " _
               & " CustNo, DOB) VALUES (""" & strCustLastName _
               & """,""" & strCustFirstName & """," & lngCustNo _
               & ",#" & dteDOB & "#)"

If you want to see the UtterAccess thread that inspired this article, click here. As usual, thanks for reading, and I would appreciate any comments or feedbacks.

Original post date: April 28, 2014

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will both update existing data in the table and add new records at the same time.

The Scenario

The member stated that he has a master table of data in Access and receives updated information every month in an Excel file. The Excel file contains the same information as the master table but with user changes to the data and possibly new information as well. The challenge is to update the Access table with the user changes and append the additional information as new records - at the same time.

The Usual Solution

The first step, usually, is to link to the Excel spreadsheet so we can access the data from within Access. We can then create an UPDATE query that joins the master table with the linked table to apply the latest changes to the data. However, this doesn't include the new information from the linked table.
As a second step, we might also create an APPEND query to grab all the new information from the linked table and add them to the master table. We would then need to execute both queries, one at a time, to complete the task of updating the master table.

The Combined Solution

But, since the challenge was to perform both the update and append actions in one query, we had to use a different technique. I can't say that this is a new technique because I'm sure it's been around for a while, and most of you probably already know it. I just thought of writing it down here as a possible reference for me in the future.
So, how do we execute an UPDATE and APPEND query at the same time? Well, we also start out by linking to the Excel spreadsheet to access the data. We then create one query that joins the master table to the linked table. However, in this case, we will use an OUTER JOIN to return all the records from the linked table.
As a result, the database engine will update existing data and append new records at the same time. Here's an example of how the query's SQL statement might look like:

UPDATE tblMaster
RIGHT JOIN tblExcel
ON tblMaster.FieldName=tblExcel.FieldName
SET tblMaster.FieldToUpdateOrAdd=tblExcel.FieldToUpdateOrAdd

Original post date: November 30, 2013