I need help with DLookup criteria

[Thelma Lubkin]

I would like my DLookup criteria [and I *hate* having to say that for
my poor single criterion!] to say this: Trim(fieldX) = strVar:

myVar = _
DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")

I don't believe that this will work, and I won't be at a machine with
access to Access for a while, so can someone please tell me how to write
this?

thanks, --thelma

[salad]

Thelma Lubkin wrote:

I would like my DLookup criteria [and I *hate* having to say that for
my poor single criterion!] to say this: Trim(fieldX) = strVar:

myVar = _
DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")

I don't believe that this will work, and I won't be at a machine with
access to Access for a while, so can someone please tell me how to write
this?

thanks, --thelma

You might want to do Like instead. In A97
"fieldX Like *'" & strVar & "'*")
Look at Like in help

But why do you need to trim a table field to look for a var. Let's say
the field width is 10. You can pad strVar with leading stuff if you
want to...
strVar = Right("123" & space(10),10)
will return "123 "

Or
strVar = Right("123" & string(10,"0"),10)
will return "1230000000"
If your field is 10 chars in length and the value is "123" and your
strVar is "123", there's no need to trim anything.

But you might find Like to be your best solution

[Randy Harris]

* salad:

Thelma Lubkin wrote:

I would like my DLookup criteria [and I *hate* having to say that for
my poor single criterion!] to say this: Trim(fieldX) = strVar:

myVar = _
DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")

I don't believe that this will work, and I won't be at a machine with
access to Access for a while, so can someone please tell me how to write
this?

thanks, --thelma
You might want to do Like instead. In A97
"fieldX Like *'" & strVar & "'*")
Look at Like in help

Salad, wouldn't she want the asterisks inside the single quotes?

But why do you need to trim a table field to look for a var. Let's say
the field width is 10. You can pad strVar with leading stuff if you
want to...
strVar = Right("123" & space(10),10)
will return "123 "

Or
strVar = Right("123" & string(10,"0"),10)
will return "1230000000"

If she used the padding, wouldn't she want Left, rather than Right?

If your field is 10 chars in length and the value is "123" and your
strVar is "123", there's no need to trim anything.

But you might find Like to be your best solution

I too am puzzled why the field would need to be "Trim"med. I thought
Access discarded trailing spaces from text fields.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[salad]

Randy Harris wrote:

* salad:

Thelma Lubkin wrote:

I would like my DLookup criteria [and I *hate* having to say that for
my poor single criterion!] to say this: Trim(fieldX) = strVar:

myVar = _
DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")

I don't believe that this will work, and I won't be at a machine with
access to Access for a while, so can someone please tell me how to write
this?

thanks, --thelma

You might want to do Like instead. In A97
"fieldX Like *'" & strVar & "'*")
Look at Like in help

Salad, wouldn't she want the asterisks inside the single quotes?

You're right. Senior moment.

[Thelma Lubkin]

salad <oi*@vinegar.com> wrote:
: Thelma Lubkin wrote:
:> I would like my DLookup criteria [and I *hate* having to say that for
:> my poor single criterion!] to say this: Trim(fieldX) = strVar:
:>
:> myVar = _
:> DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")
:>
:> I don't believe that this will work, and I won't be at a machine with
:> access to Access for a while, so can someone please tell me how to write
:> this?
:>
:> thanks, --thelma
:>
: You might want to do Like instead. In A97
: "fieldX Like *'" & strVar & "'*")
: Look at Like in help

: But why do you need to trim a table field to look for a var.

I'm extending an existing form. Meanwhile I'm also making some
changes that may be improvements, and may, actually, only be imposing
my style of doing things.

Currently I find a variable set by using a select statement to capture
one row into a record set and then extracting from it the variable
value. I'd prefer to use DLookup.

I don't know how the data in the table was gathered. As the Trim
function is being used in the Select statement, I'm afraid that the
data may contain leading or trailing spaces that do not necessarily
come from a string value with fewer characters than the max field
length. e.g strVar = "xxx " with a field length of 15.

I could use the Like operator, but that's only because of the
special nature of what I'm trying to extract. If I were trying to
extract a string, e.g. 'mother', and my data contained both 'mother
goose' and 'mother', which of these DLookup returned would depend on
their order in the dataset. So I rejected Like on general grounds,
even though I will not have this problem
: Let's say
: the field width is 10. You can pad strVar with leading stuff if you
: want to...
: strVar = Right("123" & space(10),10)
: will return "123 "

...and if field contains "123 " or " 123" it still won't
match.

: Or
: strVar = Right("123" & string(10,"0"),10)
: will return "1230000000"
: If your field is 10 chars in length and the value is "123" and your
: strVar is "123", there's no need to trim anything.

: But you might find Like to be your best solution

Thanks for the answer, but I ask again whether it's
possible to use Trim on the field value that is the criterion! for the
DLookup
--thelma

[salad]

Thelma Lubkin wrote:

Thanks for the answer, but I ask again whether it's
possible to use Trim on the field value that is the criterion! for the
DLookup
--thelma

Sure. Why not. Doesn't make sense as to why you need to do it.

You could have a field called "Junk" that is 255 characters. In field
Junk you entered "Test". If you search for "Test", it will be found.

Now if you are saving your variables with trailing spaces for some odd
reason, yeah, you can trim it in your dlookup. You can even experiment
with it. Ex:
Dim var As Variant
var = DLookup("ID", "Table", "Trim(Junk) = 'Test'")
MsgBox var

You should also make sure Junk only permits unique values otherwise you
may get incorrect results by selecting the wrong record.

Each person that posts here might have reasons for doing something that
seems unnecessary by others. This is one case. But it works so go
ahead and use it.

[Thelma Lubkin]

salad <oi*@vinegar.com> wrote:
: Thelma Lubkin wrote:

:> Thanks for the answer, but I ask again whether it's
:> possible to use Trim on the field value that is the criterion! for the
:> DLookup
:> --thelma

: Sure. Why not. Doesn't make sense as to why you need to do it.

Thanks. That's great. I guess I'm too rooted in programming
language constructs where the left side of an equal sign is the
variable being defined, so you couldn't enclose it in a function.
: You could have a field called "Junk" that is 255 characters. In field
: Junk you entered "Test". If you search for "Test", it will be found.

: Now if you are saving your variables with trailing spaces for some odd
: reason, yeah, you can trim it in your dlookup. You can even experiment
: with it. Ex:
: Dim var As Variant
: var = DLookup("ID", "Table", "Trim(Junk) = 'Test'")
: MsgBox var

: You should also make sure Junk only permits unique values otherwise you
: may get incorrect results by selecting the wrong record.

: Each person that posts here might have reasons for doing something that
: seems unnecessary by others. This is one case. But it works so go
: ahead and use it.

This really is a somewhat quirky case--the value in the field
I want returned will be the same in all of the records that
meet the criterion so I don't care which one comes back.

Thank you for your help.

--thelma
who can't do the experiments on her Mac

[Rich P]

Hi Thelma,

myVar = _
DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")

try this

myVar = _
DLookup("someField", "someTable", "fieldX = '" & Trim(strVar) & '")

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Thelma Lubkin]

Rich P <rp*****@aol.com> wrote:
: Hi Thelma,

:>>
: myVar = _
: DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '")
:>>

: try this

: myVar = _
: DLookup("someField", "someTable", "fieldX = '" & Trim(strVar) & '")

I am using code to generate strVar so I know that it has no
extra spaces, but I don't know what's in the database.

thank you for answering.
--thelma
: Rich

: *** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

To use DLookup effectively, you need to already know what you are
looking up. If you don't know what is in the database, then it sounds
more like you are doing a search. To search for something in a table
that you don't know anything about you could try something like this:

Dim DB As DAO.Database, RS1 As DAO.Recordset
Dim i As Integer, j As Integer, RS2 As DAO.Recordset

Set DB = CurrentDB
Set RS1 = DB.OpenRecordset("SomeTable")
For i = 0 to RS1.Fields.Count - 1
Set RS2 = DB.OpenRecordset("Select " & RS1(i).Name & " From SomeTable
Where somthing = '" & strVar & "'")
If Not RS2.EOF Then
Do Something
...
Exit For
End IF
Next

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Thelma Lubkin]

Rich P <rp*****@aol.com> wrote:
: To use DLookup effectively, you need to already know what you are
: looking up. If you don't know what is in the database, then it sounds
: more like you are doing a search. To search for something in a table
: that you don't know anything about you could try something like this:

: Dim DB As DAO.Database, RS1 As DAO.Recordset
: Dim i As Integer, j As Integer, RS2 As DAO.Recordset

: Set DB = CurrentDB
: Set RS1 = DB.OpenRecordset("SomeTable")
: For i = 0 to RS1.Fields.Count - 1
: Set RS2 = DB.OpenRecordset("Select " & RS1(i).Name & " From SomeTable
: Where somthing = '" & strVar & "'")
: If Not RS2.EOF Then
: Do Something
: ...
: Exit For
: End IF
: Next

I am working with an existing database. The field value that I'm
looking for should come from a very small set of choices: if I had
been involved in the design I would have made sure they were inserted
without any extraneous spaces. But I find code that uses a select
statement with Trim(fieldX) = strVar as its where clause, so I have to
assume that extraneous spaces have somehow been allowed to clutter up
the very limited [5 or 6] valid choices. This isn't really a search
situation. I'm also able to accept the DLookup result from any record
that matches the criterion, so I'm not worried about multiple records
that match.

I'm sorry that I was unclear; I hope that I haven't now
just tangled things up further. I appreciate all of your help.
I think that I know what to do now.

--thelma
: Rich

: *** Sent via Developersdex http://www.developersdex.com ***

[salad]

Thelma Lubkin wrote:

Rich P <rp*****@aol.com> wrote:
: To use DLookup effectively, you need to already know what you are
: looking up. If you don't know what is in the database, then it sounds
: more like you are doing a search. To search for something in a table
: that you don't know anything about you could try something like this:

: Dim DB As DAO.Database, RS1 As DAO.Recordset
: Dim i As Integer, j As Integer, RS2 As DAO.Recordset

: Set DB = CurrentDB
: Set RS1 = DB.OpenRecordset("SomeTable")
: For i = 0 to RS1.Fields.Count - 1
: Set RS2 = DB.OpenRecordset("Select " & RS1(i).Name & " From SomeTable
: Where somthing = '" & strVar & "'")
: If Not RS2.EOF Then
: Do Something
: ...
: Exit For
: End IF
: Next

I am working with an existing database. The field value that I'm
looking for should come from a very small set of choices: if I had
been involved in the design I would have made sure they were inserted
without any extraneous spaces. But I find code that uses a select
statement with Trim(fieldX) = strVar as its where clause, so I have to
assume that extraneous spaces have somehow been allowed to clutter up
the very limited [5 or 6] valid choices. This isn't really a search
situation. I'm also able to accept the DLookup result from any record
that matches the criterion, so I'm not worried about multiple records
that match.

I'm sorry that I was unclear; I hope that I haven't now
just tangled things up further. I appreciate all of your help.
I think that I know what to do now.

--thelma
: Rich

: *** Sent via Developersdex http://www.developersdex.com ***

I think you have a bunch of seasoned Access programmer confused.

Have you thought about running an update query that trims all values in
the field you are searching on...IOW run some cleanup code?

[Thelma Lubkin]

salad <oi*@vinegar.com> wrote:
: Thelma Lubkin wrote:

:> Rich P <rp*****@aol.com> wrote:
:> : To use DLookup effectively, you need to already know what you are
:> : looking up. If you don't know what is in the database, then it sounds
:> : more like you are doing a search. To search for something in a table
:> : that you don't know anything about you could try something like this:
:>
<snip search code>
:>
:> <snip>... This isn't really a search
:> situation. I'm also able to accept the DLookup result from any record
:> that matches the criterion, so I'm not worried about multiple records
:> that match.
:>
:> I'm sorry that I was unclear; I hope that I haven't now
:> just tangled things up further. I appreciate all of your help.
:> I think that I know what to do now.
:>
:> --thelma
:> : Rich

: I think you have a bunch of seasoned Access programmer confused.

: Have you thought about running an update query that trims all values in
: the field you are searching on...IOW run some cleanup code?

I'm sorry about the confusion, but I am learning a lot from the
communication nonetheless. I am working on forms, and learning Access
as I go, as a volunteer. I'm not in charge of the database, haven't
been authorized to change anything in it. The form I'm working on now
is purely for display purposes: it changes nothing in the database.

I have thought about seeing whether there actually exist any
unwanted spaces in the field's contents, but I'm spending so little
time at a machine with Access on it that the form extensions I'm
doing have always got priority.

--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

I have thought about seeing whether there actually exist any
unwanted spaces in the field's contents, but I'm spending so little
time at a machine with Access on it that the form extensions I'm
doing have always got priority.

When you have Access available why not test?

Make a little table
ID
Name

INSERT INTO LIttleTable (Name) Values (' Thelma ')

Tell us what happens.

Amost all we know comes from testing. The help file is often wrong.
Here in CDMA Ie sometimes find that long and complicated and generally
accepted strategies are ... unnecessary.

You can download BETA Office 2007 free. It's 500 megs so don't, unless
you have a quick connection or can let it run all night.

I have never used DLookup. This is because it used to be very, very
slow. That's been cured. Regardless I use (air code)
myvar = DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
WHERE ID=1").Collect(0)

I think it's clearer, It's at least as fast. And it works no matter
what your datasource is (Well, not really .... one needs to use
CurrentProject.Connect.Execute( ... often.

blah, blah

[rkc]

Lyle Fairfield wrote:

Thelma Lubkin wrote:

I have thought about seeing whether there actually exist any
unwanted spaces in the field's contents, but I'm spending so little
time at a machine with Access on it that the form extensions I'm
doing have always got priority.

When you have Access available why not test?

Make a little table
ID
Name

INSERT INTO LIttleTable (Name) Values (' Thelma ')

Tell us what happens.

My money is on it working exactly as written in the original post.

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: Thelma Lubkin wrote:

: When you have Access available why not test?

: Make a little table
: ID
: Name

: INSERT INTO LIttleTable (Name) Values (' Thelma ')

: Tell us what happens.

I'll do that the next time I go in.

: Amost all we know comes from testing. The help file is often wrong.
: Here in CDMA Ie sometimes find that long and complicated and generally
: accepted strategies are ... unnecessary.

: You can download BETA Office 2007 free. It's 500 megs so don't, unless
: you have a quick connection or can let it run all night.

I have a relatively fast connection ... to my Mac or my
SCO-unix box. I have nothing that runs Windows at home.
...and I use elm to read mail.

: I have never used DLookup. This is because it used to be very, very
: slow. That's been cured. Regardless I use (air code)
: myvar = DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
: WHERE ID=1").Collect(0)

This answers a question I've been meaning to ask you. I once
got the impression that you had said that you never use recordsets.
I could understand the preference for using SQL statements instead of
searching recordsets, but I couldn't see what you did with the result
of the SQL--I see that I read too much into your dislike for
recordsets--you do use them to store SQL results, as above.

--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

Lyle Fairfield <ly***********@aim.com> wrote:
: Thelma Lubkin wrote:

: When you have Access available why not test?

: Make a little table
: ID
: Name

: INSERT INTO LIttleTable (Name) Values (' Thelma ')

: Tell us what happens.

I'll do that the next time I go in.

: Amost all we know comes from testing. The help file is often wrong.
: Here in CDMA Ie sometimes find that long and complicated and generally
: accepted strategies are ... unnecessary.

: You can download BETA Office 2007 free. It's 500 megs so don't, unless
: you have a quick connection or can let it run all night.

I have a relatively fast connection ... to my Mac or my
SCO-unix box. I have nothing that runs Windows at home.
...and I use elm to read mail.

: I have never used DLookup. This is because it used to be very, very
: slow. That's been cured. Regardless I use (air code)
: myvar = DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
: WHERE ID=1").Collect(0)

This answers a question I've been meaning to ask you. I once
got the impression that you had said that you never use recordsets.
I could understand the preference for using SQL statements instead of
searching recordsets, but I couldn't see what you did with the result
of the SQL--I see that I read too much into your dislike for
recordsets--you do use them to store SQL results, as above.

--thelma

Hmmm ... guess you got me.

DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
WHERE ID=1") is a recordset but I think I did not use it to store a
value; as I use it, it's not recoverable or usable beyond its one line
manifestation. I guess I should have said that I seldom declare
recordsets as in

Dim rcs as DAO.Recordset
Set rcs = DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
WHERE ID=1")

but I often
do something with
or set some variable equal to
CurrentProject.Connection.Execute("SELECT Name FROM LittleTable WHERE
ID=1").Collect(0)
Collect being a method which returns the value of Field zero.

I also use
NOT CurrentProject.Connection.Execute("SELECT Name FROM LittleTable
WHERE ID=1").BOF
to check for the existence of a record

and
Split(CurrentProject.Connection.Execute("SELECT Name FROM LittleTable
WHERE ID=1").GetString(adClipString, other parameters, ....),Delimiter
to get arrays of record values.

All of these methods give me one-liners which do the work of other
multi-line code which may tedious.

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:

: Thelma Lubkin wrote:
:> Lyle Fairfield <ly***********@aim.com> wrote:
: Hmmm ... guess you got me.

: DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
: WHERE ID=1") is a recordset but I think I did not use it to store a
: value; as I use it, it's not recoverable or usable beyond its one line
: manifestation. I guess I should have said that I seldom declare
: recordsets as in

: Dim rcs as DAO.Recordset
: Set rcs = DBEngine(0)(0).OpenRecordSet("SELECT Name FROM LittleTable
: WHERE ID=1")

: but I often
: do something with
: or set some variable equal to
: CurrentProject.Connection.Execute("SELECT Name FROM LittleTable WHERE
: ID=1").Collect(0)
: Collect being a method which returns the value of Field zero.

: I also use
: NOT CurrentProject.Connection.Execute("SELECT Name FROM LittleTable
: WHERE ID=1").BOF
: to check for the existence of a record

: and
: Split(CurrentProject.Connection.Execute("SELECT Name FROM LittleTable
: WHERE ID=1").GetString(adClipString, other parameters, ....),Delimiter
: to get arrays of record values.

I've wanted to save my SQL select statements into arrays, but
now that you've shown me how to do it, I'm not sure that I see
it as an advantage. if I do this for something like

"SELECT anINT, aSTRING, aBOOL FROM aTABLE"

first I'll need to worry about what I can use as a column delimiter;
aSTRING may contain internal spaces: if all characters might legally
occur within it, I'm stuck for a delimiter. But usually I will be
able to find a reasonable column delimiter, so now I have an array of
strings. Each string has to be split again to extract its 3
components; then I need to translate those components to their
original data types.

: All of these methods give me one-liners which do the work of other
: multi-line code which may tedious.

Can you really do the work above as a one-liner? I want that
one-liner. [although I'm afraid it may take me one-year to
understand]
--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

I've wanted to save my SQL select statements into arrays, but
now that you've shown me how to do it, I'm not sure that I see
it as an advantage. if I do this for something like

"SELECT anINT, aSTRING, aBOOL FROM aTABLE"

first I'll need to worry about what I can use as a column delimiter;
aSTRING may contain internal spaces: if all characters might legally
occur within it, I'm stuck for a delimiter. But usually I will be
able to find a reasonable column delimiter, so now I have an array of
strings. Each string has to be split again to extract its 3
components; then I need to translate those components to their
original data types.

: All of these methods give me one-liners which do the work of other
: multi-line code which may tedious.

Can you really do the work above as a one-liner? I want that
one-liner. [although I'm afraid it may take me one-year to
understand]
--thelma

I often use vbNewLine and vbTab as delimiters.

As I mentioned previously the Help file is sometimes not such a Help.
If one studies GetString in the Object Browser one finds

Function GetString([StringFormat As StringFormatEnum = adClipString],
[NumRows As Long = -1], [ColumnDelimeter As String], [RowDelimeter As
String], [NullExpr As String]) As String
Member of ADODB.Recordset

This suggests, correctly, that we may use more than one character as a
delimiter. Of course, that capability greatly improves our chances of
selecting delimiters that are not in any of the records:

Dim ColumnDelimiter As String
Dim RowDelimiter As String
Dim WorkingArray As Variant
Dim TableasArray() As Variant
Dim z As Long

ColumnDelimiter = Chr$(255) & Chr$(254) & Chr$(255)
RowDelimiter = Chr$(254) & Chr$(255) & Chr$(254)

WorkingArray = CurrentProject.Connection.Execute("SELECT * FROM
Employees").GetString(adClipString, , ColumnDelimiter, RowDelimiter)

WorkingArray = Split(WorkingArray, RowDelimiter)

ReDim TableasArray(UBound(WorkingArray))

For z = 0 To UBound(WorkingArray)
TableasArray(z) = Split(WorkingArray(z), ColumnDelimiter)
Next z

Debug.Print TableasArray(4)(2)

' displays Steven

End Sub

This is clumsy in VBA, not so clumsy in other languages, Javascript for
instance.

****

I think we can return the table as array with this line:

EmployeesArray = CurrentProject.Connection.Execute("SELECT * FROM
Employees").GetRows()

as in

Public Sub CreateEmployeesArray()
Dim EmployeesArray() As Variant
EmployeesArray = CurrentProject.Connection.Execute("SELECT * FROM
Employees").GetRows()
Debug.Print EmployeesArray(2, 4)
' displays Steven
End Sub

note that GetRows returns (field,row) orientation.

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: As I mentioned previously the Help file is sometimes not such a Help.
: If one studies GetString in the Object Browser one finds

I usually search for help using google: it retrieves even
information residing somewhere in Microsoft's own documentation much
more reliably and conveniently than the Microsoft site.

: Function GetString(<snip a long story>)

: This is clumsy in VBA, not so clumsy in other languages, Javascript for
: instance.

: ****

: I think we can return the table as array with this line:

: EmployeesArray = CurrentProject.Connection.Execute("SELECT * FROM
: Employees").GetRows()

That's much better! Do I now have a
2-dimensioned Variant array whose rows
retain the datatype of their underlying field
definitions in original table? [No need to answer:
I'll try it when I get back to an Access equipped
computer]

Thank you for the invaluable lessons.

Lauren would be horrified at what I'm getting away with
by hanging around over here.

--thelma

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: When you have Access available why not test?

: Make a little table
: ID
: Name

: INSERT INTO LIttleTable (Name) Values (' Thelma ')

: Tell us what happens.
I created a table, 'TRLTable' and put three records into it: see below.
I used the following code to search for the string "Thelma" using DLookup,
and also to print the actual table entries.

Dim strLook As String
Dim strName As String

Dim intCount As Integer

Dim TRL_Array() As Variant

strLook = "intCount = DLookup('RefCount', 'TRLTable', 'Name = ''Thelma'')"
intCount = DLookup("RefCount", "TRLTable", "Name = 'Thelma'")
Debug.Print _
"This statement [with quotemarks fudged so I wouldn't have to " _
& "lookup ASCII codes]" _
& vbCrLf & vbCrLf & strLook & vbCrLf & vbCrLf _
& "Matches Thelma: " & intCount & vbCrLf & "Contents of TRLTable:"

TRL_Array() = _
CurrentProject.Connection.Execute("SELECT * FROM TRLTable ").GetRows()
For intCount = 0 To UBound(TRL_Array, 2)
Debug.Print "*" & TRL_Array(1, intCount) & "*" & Chr(9) _
& TRL_Array(2, intCount)
Next
This code generated output:
----------------------------------
This statement [with quotemarks fudged so I wouldn't have to lookup ASCII codes]

intCount = DLookup('RefCount', 'TRLTable', 'Name = ''Thelma'')

Matches Thelma: 33

Contents of TRLTable:
*Thelma* 33
* Thelma* 44
*Thelma* 55

endOfOutput

The stars were added to the print to make any leading or trailing
spaces visible. The table I tried to create had as its three text
entries, "Thelma ", "Thelma" and " Thelma", so leading spaces are
retained, but trailing spaces not.

--thelma

[Lyle Fairfield]

> The stars were added to the print to make any leading or trailing

spaces visible. The table I tried to create had as its three text
entries, "Thelma ", "Thelma" and " Thelma", so leading spaces are
retained, but trailing spaces not.

I think this is a useful thing to know. How does this impact on your
original search for Trim?