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

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

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

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

"beta" <benito111@juno.com> wrote in message
news:1108078477.390830.128210@l41g2000cwc.googlegr oups.com...[color=blue]
> 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
>[/color]

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

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

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

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" <benito111@juno.com> wrote in message
news:1108417906.889570.14490@f14g2000cwb.googlegro ups.com...[color=blue]
> 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
>[/color]