Criteria to a text field: "N/A"

  • Thread starter Thread starter Benito
  • Start date Start date
B

Benito

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
 
Benito said:
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
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code should work, but you have some VBA syntax errors: the end-of
line should be ampersand then underline. E.g.:

Me!List1.RowSource = "SELECT q.FileName, q.Day2 FROM " & _
"qryChangeSorted q " & _
"WHERE q.Day2 = '" & str & "' " & _
"AND Left(q.FileName,3) = '" & stringNpa & "'"


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgvllIechKqOuFEgEQJEowCfb1MmU+dSNmH5lXcB3psrzZsufI8AoNSb
JIcHyrgIZyGPsQeogYeKJzhT
=JySV
-----END PGP SIGNATURE-----
 
I am sorry I had a typo in the previous posting. Yes the code looks
okay but not when it is executed.
To avoid it I just selected all, copied and pasted the code as per
below.
I built the Select statement first from the Query wizard of MS Access
and it was giving me the problem. So I wrote down the code and tried to
watch the value of the variable str.
Code 1 would implicitly convert "N/A" into an expression. A pop-up
would come up asking for the value of N then followed by another pop-up
for A.
For Code 2, I have a feeling that it is taking the ANSI integer values
of A and N.

I maybe mistaken with these feelings but I really need some help here
as it is also giving me the same problem in SQL Server 2000.

Our MVP's and guru's, kindly help. Thanks.


Code 1.
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

Code 2.
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
 
beta said:
I am sorry I had a typo in the previous posting. Yes the code looks
okay but not when it is executed.
To avoid it I just selected all, copied and pasted the code as per
below.
I built the Select statement first from the Query wizard of MS Access
and it was giving me the problem. So I wrote down the code and tried to
watch the value of the variable str.
Code 1 would implicitly convert "N/A" into an expression. A pop-up
would come up asking for the value of N then followed by another pop-up
for A.
For Code 2, I have a feeling that it is taking the ANSI integer values
of A and N.

I maybe mistaken with these feelings but I really need some help here
as it is also giving me the same problem in SQL Server 2000.

Our MVP's and guru's, kindly help. Thanks.


Code 1.
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

Code 2.
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
Click to expand...

I created a table with these fields/types:
Table name: ChangeSorted
ID: Autonumber (PK)
FileName: Text
Day2: Text

I wasn't sure if qryChangeSorted was a query or table, so I also created a
query named qryChangeSorted based on table ChangeSorted. I added 4 records, 2
of which had N/A in the Day2 field.

On a form, I created a button and a list box. I put the code from "Code 2"
in the button's OnClick event.

When the code ran, the list box had two records.

The only problem was 3 chars HAD to be entered in the pop-up box. If I
entered 2 or 4 chars, there were no items in the list box.

So either there should be some checking to make sure that 3 chars (and only
3) were entered into the pop-up box or (what I did) change the "Where" to:
(should be on one line)

Me.List1.RowSource = "SELECT q.FileName, q.Day2 FROM qryChangeSorted q "
& "WHERE q.Day2 = '" & str & "' AND q.FileName Like '" & stringNpa & "*';"


Your code seems to work OK in A2K; I don't have SQL Server 2000 so I can't
comment on it.

HTH

SteveS
 
Back
Top