473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL Linked table: -1 does not equal True

107 Expert 100+
I notice on my installation that for linked SQL Server tables, -1 does not evaluate the same as true.

Is that correct, or is there a patch that fixes that?

I've tried on 2 systems: one with Access 2003 and another with Access 2010.

When I Google the topic I can't seem to get confirmation.

This kind of code seems to always result in rst.EOF=True if referring to a linked SQL Server table:

Expand|Select|Wrap|Line Numbers
  1. Public Function TestMinus1()
  2.     Dim sql As String
  3.     Dim rst As DAO.Recordset
  4.     Dim db As DAO.Database
  5.  
  6.     Set db = CurrentDb
  7.  
  8.  
  9.     sql = "SELECT MyBlnField FROM MyTable WHERE MyBlnField=-1"
  10.  
  11.     Set rst = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
  12.  
  13.     If rst.EOF Then
  14.         Debug.Print "Nothing returned"
  15.     End If
  16.  
  17. End Function
  18.  
Oct 4 '13 #1
8 2335
zmbd
5,501 Expert Mod 4TB
you didn't move to the first record.
you didn't check to see if any records were returned.

insert line 12
Expand|Select|Wrap|Line Numbers
  1. rst.movefirst
  2. if rst.count then
insert line 16
Expand|Select|Wrap|Line Numbers
  1.  end if

also, your question doesn't make sense.
At the start you are asking about "-1" == True
Then you switch to dealing with the end of the recordset - these have very little to do with each other outside of the returned record(s).
Oct 4 '13 #2
Steven Kogan
107 Expert 100+
rst.MoveFirst gives error 3021, no current record.

rst.Count gives method or data member not found.

With an actual linked SQL Server table table changing -1 to True returns records.
Oct 4 '13 #3
Rabbit
12,516 Expert Mod 8TB
SQL Server has no data type of boolean. Most likely it is a bit field and the Access SQL parser is making the conversion of the keywords true/false into a bit field. A bit field has only two possible values, 0 and 1.
Oct 4 '13 #4
Steven Kogan
107 Expert 100+
I agree with you. It is actually a bit field.

When viewed as a datasheet the values display as 0 and -1. If you use -1 as criteria it returns no records. If you use 1 as criteria it returns records, but displays the values as -1. If you use True as criteria it also returns the records that display as -1.

At least those are my results. I'm not sure if it is my installation.
Oct 4 '13 #5
Rabbit
12,516 Expert Mod 8TB
It's how Access is interpreting the data types from SQL Server to Access. The value on SQL Server is 1. But Access is interpreting it as a boolean, making the display conversion to -1.
Oct 4 '13 #6
Steven Kogan
107 Expert 100+
That seems to be what's happening. It displays -1, but the underlying value is 1. It resolves to True and to 1, but not to -1. For an Access table you can code -1 as if it is True, but for linked tables -1 is not the same as true.

I'd be interested to hear if others get the same result, or if a later patch fixes it so that using -1 as criteria on a linked SQL Server table returns 'True' results (which is actually equal to 1 in the underlying data, but is displayed as -1).
Oct 4 '13 #7
Steven Kogan
107 Expert 100+
Here's something odd.

Expand|Select|Wrap|Line Numbers
  1. SELECT Val(MyBlnField) FROM MyTable WHERE Val(MyBlnField)=-1
Returns records, and if the field is True, Val(MyBlnField) evaluates to -1.

So Access is converting the SQL bit value = 1 to -1, but not in all cases. At least that's what I'm finding...
Oct 4 '13 #8
Rabbit
12,516 Expert Mod 8TB
Val() is an Access function. Val() will therefore take the SQL Server data and convert it to a data type that Access can read, ie boolean, ie -1.

Access is not ANSI SQL compliant. It's not a simple matter of patching Access as it would be rewriting the SQL engine that that underlies Access. I wouldn't hold your breath on a patch for this.
Oct 4 '13 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: James | last post by:
Hello group: I guessing there is no way to create an OLEDB linked table in Access to SQL Server, am I correct? Basically, I've currently migrated one of my Access database tables to SQL Server,...
0
by: me here | last post by:
I have a VBA subroutine that links an MS Excel spreadsheet and copies the data into a local table. This process is controlled by a form that allows users to select the spreadsheet from the file...
5
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
4
by: Neil Ginsberg | last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked tables do not have the password stored in them, so the first time the user accesses them, they need to enter the SQL...
3
by: Zlatko Matić | last post by:
Hi! What happens with linked tables if they were linked using File DSN, when I copy the Access file on some other PC without File DSN ? What is the difference between DSN on linked tables and...
4
by: Wayne Wengert | last post by:
I am trying to create a VB.NET Windows application to move some data from a local Access DB table to a table in a SQL Server. The approach I am trying is to open an OLEDB connection to the local...
3
by: Janelle.Dunlap | last post by:
I have a table in my database that is linked to an excel spreadsheet. I need to be able to manipulate the data in this linked table so that I can create smaller normalized tables that work with...
2
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
2
nico5038
by: nico5038 | last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype. Problem: When placing a split database in another folder, the Linked table manager should...
2
by: troy_lee | last post by:
What is the best way of copying all the records from a linked table into a replica table that is local on my computer? This is for development work at home where I can not access the main table. Is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.