Try using a fully qualified object name like:
WHERE (((myTable.txtField)='N/A'))
either single or double quotes should work.
"N/A" can only be a string value from a text type field, and a query should
be able to find any string value.
Have you tried just a simple Access design-grid query of that data source?
That would eliminate nearly all SQL syntax errors. BTW, if "q.Day2" is
your field name, then you'll need to refer to it as "[q.Day2]" in your code.
-Ed
"beta" <be*******@juno.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hi Ed,
Thanks for your reply. I think I did not properly explain my problem in
the previous posting and I think I have another bigger problem here.
Let me explain the scenario.
My front end is MS Access and the back end is SQL Server 2000 Standard
Edition(on Windows 2000 Server). I connect to SQL data source through
system DSN. 'qryChangeSorted' is a table type link in MS Access to a
view in SQL Server.
i) If I double click the said link, I could see all the rows having
value N/A's on the Day2 field.
ii) When I remove the filter "WHERE q.Day2 = 'N/A'", then I would get
all expected rows displayed in the listbox.
iii) But when I keep the filter, I get no rows displayed in the
listbox.
iv) When I make the filter "WHERE q.Day2 = N/A", then I would get a
pop-up asking for the value of N then followed by another pop-up asking
for the value of A.
v) I tried directly SQL Server - Enterprise Manager querying my table
with the filter value "N/A" on the Day2 and I got an empty result.
I think the problem goes back to SQL Server.
Private Sub Command0_Click()
Dim stringNpa As String, str As String
str = "N/A"
stringNpa = InputBox("Please enter NPA.")
Me.List1.RowSource = "SELECT q.FileName, q.Day2 FROM " _
& "qryChangeSorted q " _
& "WHERE q.Day2 = '" & str _
& "' AND Left(q.FileName,3) = '" & stringNpa & "';"
End Sub