By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,524 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,524 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <be*******@juno.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.