SQL Statement Problem with Text field

[bymarce]

I'm using the following lines of code to build an SQL statement. The
Data.MLO field is a text field of the format ####-####. When the query runs
based on this SQL statement it removes zeros after the hyphen. How do I get
it to keep the zeros? Do I need to add or change the quotes? Thanks.
Marcie

stSQL = "SELECT DISTINCT Personel.Email FROM Data "
stSQL = stSQL & "INNER JOIN Personel ON " & "Data.TestAssignedTo = _
Personel.Initials WHERE "
stSQL = stSQL & "((" & "Data.MLO" & ")" & " IN (" & Me.txtMLO & "))"

 

[bymarce]

I think I just realized that my problem isn't in that part of the code. It's
in a earlier section that builds Me.txtMLO . Here's the whole thing. Thanks
for your help.

Dim vItm As Variant 'Make the query to get the email addresses.
Dim stWhat As String 'Based on

'http://www.mvps.org/access/reports/rpt0005.htm
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef
Dim lngLen As Long


stWhat = "": stCriteria = ","
For Each vItm In Me!fMLO.ItemsSelected
stWhat = stWhat & Me!fMLO.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!txtMLO = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryEmailsLB")
stSQL = "SELECT DISTINCT Personel.Email FROM Data "
stSQL = stSQL & "INNER JOIN Personel ON " & "Data.TestAssignedTo =_
Personel.Initials WHERE "
stSQL = stSQL & "((" & "Data.MLO" & ")" & " IN (" & Me.txtMLO & "))"
loqd.sql = stSQL
loqd.Close
DoCmd.OpenQuery "qryEmailsLB"

 

[bymarce]

I got this worked out by using a series of OR statements in the sql rather
than IN. My query didn't like the "In" statement. Thanks.

 

[Paul Shapiro]

If the list of items in the IN clause are text, it needs string delimiters,
something like this:
Where Data.MLO In ('Item1', 'Item2', 'Item3')

So maybe your loop would be something like:
strWhere = "Where Data.MLO In ("
For Each vItm In Me!fMLO.ItemsSelected
'Append this data item, inside single quote sql text delimiter
strWhere = strWhere & "'" & Me!fMLO.ItemData(vItm) & "', "
Next vItm
'Remove trailing comma-space
strWhere = Left(strWhere, len(strWhere) - 2)
strWhere = strWhere & ")"