A function that replicates DLookup, and can use parameterized queries This function was built to replace DLookup so that i could pass in parameterized queries for a speed increase. I also wanted a simple way to get more than one value if needed. For example, if i want 4 or 5 fields out of a particular record or maybe 4 or 5 records with only one field, I could get that with this function.

EDIT: the whole filter string portion of the fuction is legacy functionality from when i initially built this function and had to leave in untill all of the places that use this piece of code have been updated.

Public Function Qlookup(ByVal argQuery As String, Optional ByRef argParameters As Variant = Null, Optional ByVal argIsFilterString As Boolean = False) As Variant
    'Parameters -
    'argQuery: the query definition name
    'argParameters: the list of parameters (can come in an array or not) that should be in the order and starting at 0
    'argIsFilterString: Allows you to use a 'Where' Clause instead of a parameter list
    'that the parameters in your query are.
    'Output -
    'Will output a single item, 1d array or 2d array depending on the query you feed it.
    'This comment line is here to fix the formatting messing up.
    If Not BasicInclude.DebugMode Then On Error GoTo Error_Handler
    Dim rs As DAO.Recordset
    Dim qry As QueryDef
    Dim u As Long
    Dim out() As Variant
    Dim i As Long
    Dim j As Long
    Qlookup = Null
    Set qry = dbLocal.QueryDefs(argQuery)
    If argIsFilterString Then
        Set rs = qry.OpenRecordset(dbOpenSnapshot)
        rs.filter = argParameters
        Set rs = rs.OpenRecordset(dbOpenSnapshot)
    Else
        If IsArray(argParameters) Then
            u = UBound(argParameters)
            If u = (qry.Parameters.count - 1) Then
                For i = 0 To u
                    qry.Parameters(i).value = argParameters(i)
                Next i
            Else
                Err.Raise vbObjectError, "Qlookup", "Number of Parameters in query(" &amp; qry.Parameters.count &amp; ") do not match the number of parameters passed in(" &amp; u + 1 &amp; ")"
            End If
        Else
            If Not (IsNull(argParameters)) And qry.Parameters.count = 1 Then
                qry.Parameters(0).value = argParameters
            ElseIf qry.Parameters.count = 0 And Not (IsNull(argParameters)) Then
                Err.Raise vbObjectError + 1, "Qlookup", "Number of Parameters in query(" &amp; qry.Parameters.count &amp; ") do not match the number of parameters passed in(1)"
            ElseIf qry.Parameters.count = 0 And (IsNull(argParameters)) Then
            End If
        End If
        Set rs = qry.OpenRecordset(dbOpenSnapshot)
    End If
    If rs.RecordCount Then
        rs.MoveFirst
        rs.MoveLast
        rs.MoveFirst
        If rs.RecordCount &gt; 1 Then
            If rs.Fields.count &gt; 1 Then
                ReDim out(rs.RecordCount - 1, rs.Fields.count - 1)
                For i = 0 To rs.RecordCount - 1
                    For j = 0 To rs.Fields.count - 1
                        out(i, j) = rs.Fields(j).value
                    Next
                    rs.MoveNext
                Next
            Else
                ReDim out(rs.RecordCount - 1)
                For i = 0 To rs.RecordCount - 1
                    out(i) = rs.Fields(0).value
                    rs.MoveNext
                Next
            End If
            Qlookup = out
        Else
            If rs.Fields.count &gt; 1 Then
                ReDim out(rs.RecordCount - 1, rs.Fields.count - 1)
                For i = 0 To rs.RecordCount - 1
                    For j = 0 To rs.Fields.count - 1
                        out(i, j) = rs.Fields(j).value
                    Next
                    rs.MoveNext
                Next
                Qlookup = out
            Else
                Qlookup = rs.Fields(0).value
            End If
        End If
    End If
Error_Exit:
    Set rs = Nothing
    Set qry = Nothing
    Exit Function
Error_Handler:
    StandardErrorBox "Qlookup", Err, Errors
    Qlookup = Null
    Resume Error_Exit
End Function
I'm going to cover other points that aren't covered...

Unnecessary recordset movement

You have this:

If rs.RecordCount Then
    rs.MoveFirst
    rs.MoveLast
    rs.MoveFirst
    ...
End If


This is totally unnecessary and also problematic:


RecordCount is not the most idiomatic way of checking for an empty. EOF is more common and is usually effective. It also works in both DAO and ADO; I'm a big advocate of writing code that is portable and consistent; it means less thing to remembers and less surprises when one quirky thing that works in one library doesn't in other.
MoveLast in order to get a count. You already know that RecordCount doesn't return accurate count until you've accessed all records in the recordset. But the more important question to ask yourself is: Did you really need that? I assert that 99% of time you don't. You have two options at your disposal:


1) Use GetRows method to extract an array out of recordset.

2) Use either a VBA.Collection or Scripting.Dictionary and stop caring about how many records there are; just use a Do Until rs.EOF loop and insert it into the dynamic collection variable. 

The point being, you do not need to know the count of the records to do what you need. Eliminate that from your procedure and it becomes much more simpler and also less error-prone.

Do loop instead of For loop

In similar vein you have this code:

For i = 0 To rs.RecordCount - 1
    out(i) = rs.Fields(0).Value
    rs.MoveNext
Next


You need to stop caring about how many iterations you need to do. This is much less verbose and easier to understand:

Do Until rs.EOF
    'do something
    rs.MoveNext
Loop


No local variables is needed just to iterate; nor do you need to care about count to iterate. You just keep iterating until.... you get to the end!

Unnecessary branching

Others already commented on the need to extract functionality into smaller functions and I fully agree. I want to call your attention to this:

    If rs.RecordCount &gt; 1 Then
        If rs.Fields.count &gt; 1 Then
            ReDim out(rs.RecordCount - 1, rs.Fields.count - 1)
            For i = 0 To rs.RecordCount - 1
                ...
            Next
        Else
            ReDim out(rs.RecordCount - 1)
            For i = 0 To rs.RecordCount - 1
                ...
            Next
        End If
        ...
    Else
        If rs.Fields.count &gt; 1 Then
            ReDim out(rs.RecordCount - 1, rs.Fields.count - 1)
            For i = 0 To rs.RecordCount - 1
                ...
            Next
            ...
        Else
            ...
        End If
    End If


Note how all branch, except for very last one basically do the same thing. In fact, the last one is only special since it's only one column. But do we really care? I say no. All you really want to do is this:

Do Until rs.EOF
    For Each fld In rs.Fields
       ...
    Next
    rs.MoveNext
Loop


This does the same thing as the above branches would have done and does so in much less verbose manner and will work whether there's only one column or several, only one records or several records. Heck, it even works even if there's no records! 

So in short, you've been a victim of being obliged to hold the compiler's hand and painstakingly tell it each individual step it must do instead of stepping back and see what you are really trying to do, and convey it to the compiler in most idiomatic manner. It's literally the compiler's job to make sense of the above instructions into machine operations which are mind-numbingly detailed well beyond the point of boredom. In fact, it's also the database engine's job to figure out what you want and how to get it to you in most efficient manner. So you want to step back and be a bit more "declarative"1 in your programming. 

Legacy Support

You have this section:

If argIsFilterString Then
    Set rs = qry.OpenRecordset(dbOpenSnapshot)
    rs.filter = argParameters
    Set rs = rs.OpenRecordset(dbOpenSnapshot)
Else
    ...
End If


Which you already indicated is there for backward compatibility. But as others have alluded to, that only add noise and make it hard to understand your function. Note that even if you're the only one writing and reading this code, future you won't know what the hell past you were doing/thinking. So if you don't want your future you to go back and kill the past you, you owe it to your future you to make it easy to ready. One easy way to set up for legacy support would be to do something akin to this:

Public Function Everything(A As Foo, B As Bar) As Baz
  If B Then
    Everything = EverythingLegacy(A, B)
  Else 
    Everything = EverythingCurrent(A)
  End If
End If

Private Function EverythingLegacy(A As Foo, B As Bar) As Baz
...
End Function

Private Function EverythingCurrent(A As Foo) As Baz
...
End Function


This gives you two big wins:

1) The code for Everything function is immediately much easier to read and make sense. The B parameter which is only used for legacy becomes more obvious.

2) It's now easy to test the individual functions without having to change the implementation and you know that changes you make won't break the legacy implementation when you tweak your current implementation. When you've finished removing all the legacy implementation, then it's easy to delete the legacy implementation and make EverythingCurrent the new Everything.

--

1 Technically speaking, that's inaccurate because VBA is an imperative language whereas SQL is a declarative language, so I use the term loosely in the sense that you need to focus on describing the problem rather than outlining the steps yourself and getting lost in the forest of details.