SQL Statement Problem with Text field

  • Thread starter Thread starter bymarce
  • Start date Start date
B

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 & "))"
 
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"
 
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.
 
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 & ")"
 
Back
Top