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(divisi on).
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("Pleas e enter NPA.")
Me.List1.RowSou rce = "SELECT q.FileName, q.Day2 FROM " _
& "qryChangeSorte d q " _
& "WHERE q.Day2 = '" & str _
& "' AND Left(q.FileName ,3) = '" & stringNpa & "';"
End Sub 3 2414
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.goo glegroups.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(divisi on).
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("Pleas e enter NPA.") Me.List1.RowSou rce = "SELECT q.FileName, q.Day2 FROM " _ & "qryChangeSorte d q " _ & "WHERE q.Day2 = '" & str _ & "' AND Left(q.FileName ,3) = '" & stringNpa & "';" End Sub
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. 'qryChangeSorte d' 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("Pleas e enter NPA.")
Me.List1.RowSou rce = "SELECT q.FileName, q.Day2 FROM " _
& "qryChangeSorte d q " _
& "WHERE q.Day2 = '" & str _
& "' AND Left(q.FileName ,3) = '" & stringNpa & "';"
End Sub
Try using a fully qualified object name like:
WHERE (((myTable.txtF ield)='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******** *************@f 14g2000cwb.goog legroups.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. 'qryChangeSorte d' 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("Pleas e enter NPA.") Me.List1.RowSou rce = "SELECT q.FileName, q.Day2 FROM " _ & "qryChangeSorte d q " _ & "WHERE q.Day2 = '" & str _ & "' AND Left(q.FileName ,3) = '" & stringNpa & "';" End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: mike |
last post by:
Ok, I have a page that queries a database and retrieves values to
create a custom report in ms excel format on the fly so that it can be
downloaded. I can use the following sub to generate the report just
fine. It works correctly for every field in the database and creates
the report as desired except for one field and one field only. The
column type for this field is the text data type in MySQL. I chose
this type because, the field...
|
by: Roald Oines |
last post by:
I have a query that uses another query as a data source (Access 97). One
of the fields is a one-character string (it's parsed from an
eight-character string in the underlying table using the Left$ function)
that is always either "R" or "N". For this query I only want to view the
records where that field is "N", but using
"N"
or
|
by: RWC |
last post by:
Hello,
I'm getting the error "The field is too small to accept the amount of data
you attempted to add. Try inserting or pasting less data" when I run a
report. I'm simply trying to apply the following criteria/filter to this
report "kEmployee in(select kEmployee from proCriteria;)" I'm not really
trying to insert any data. This same line works on all my other reports,
and I can't seem to figure out why it doesn't work on this...
|
by: Monir |
last post by:
Hi guys,
I made a database with two tables ( tblsender, tblreceiver) with one
to many relationship.
tblsender has sender_Id (pk) sender_name, address, phone, email etc.
tblreceiver has receiver_id (pk), sender_id (fk) receiver_name,
address, phone, date, etc.
|
by: S. van Beek |
last post by:
Dear reader,
How can I filter a numeric field with Like as criteria in a query.
To filter a numeric field with <10 as criteria this will com back with the
result of those records for which the value is smaller as teen.
But in case this value is variable and you prefer to work with "Like
| |
by: google |
last post by:
This is something I've done plenty of times in '97, but I can't seem to
get it to work correctly in Access 2003. Say, for example, I have a
form with an unbound combobox, the data source is a table of customers,
first column is the key field, which is hidden with a 0" width, and the
second column is the customer name. The SQL is a union query that also
inserts Null in the first column, and "All" in the second column. That
combobox gets...
|
by: Tim Marshall |
last post by:
Access 2003. The situation is this: I have a "criteria selection form"
in which there are a many different criteria, mostly displayed as combo
and text boxes in which a user can enter stuff, click an associated
button and that criteria becomes part of the where clause of a select
statement. The selected criteria is stored on a hidden form and when
the user is ready to run their report(s), a button action goes through
the hidden form and...
|
by: justin tyme |
last post by:
Hello Experts!
I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query....
|
by: Robertf987 |
last post by:
Hi,
Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000.
What I want is to be able to do a search, taking it's criteria fro a text box on a form. This bit is easy, done it a jazillion times. But I want it to be an "Or" search. If I type in it "Cakes, Cheese", I don't want it to bring up results that contain cakes AND cheese, I want it to bring...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |