My text field Criteria : "N/A" - How to resolve "/"?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

beta - 10 Feb 2005 23:34 GMT

Hello everyone,
I need some help here. If anyone has encountered this, knidly give me
your advice.

I have a command button (Command0) and a listbox (List1). Upon
clicking the command button, I want my listbox to be populated. Please
look at the code below. The fields Day2 and Filename are both text
fields. My problem is that Day2 field has rows with the text value
"N/A" (without the quotes). MS Access is evaluating the "/" sign in
between "N/A" as an operator(division).

How do I suppress MS Access to read it as it is without an implicit
conversion? I will appreciate any help. Thanks.

Ben

This is my code:

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

Reply to this Message

Ed Robichaud - 11 Feb 2005 14:26 GMT

Well, there are no operators within a character string of a text field. I
can't duplicate your problem. Could you check the field (and control) types
and formats, then explain your setup a bit more?
-Ed

> Hello everyone,
> I need some help here. If anyone has encountered this, knidly give me
[quoted text clipped - 23 lines]
> & "' AND Left(q.FileName,3) = '" & stringNpa & "';"
> End Sub

Reply to this Message

beta - 14 Feb 2005 21:51 GMT

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

Reply to this Message

Ed Robichaud - 15 Feb 2005 14:53 GMT

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

> Hi Ed,
> Thanks for your reply. I think I did not properly explain my problem in
[quoted text clipped - 26 lines]
> & "' AND Left(q.FileName,3) = '" & stringNpa & "';"
> End Sub

Reply to this Message