Hi,
I am stuck with a problem in MS Access which does not occur in SQL
Server and I have been banging my head against the wall for a couple of
days now trying to resolve it. Namely, when I execute the following
query in Access not all records that fit the condition are returned,
and when I run this same query in SQL Server all the records I want are
retrieved.
The query is:
SELECT Events.nAsset, Events.nTime,
DickeyJohnContr oller.nActualAp plicationRate, Events.nAddress 1
FROM Events, DickeyJohnContr oller
WHERE Events.nTime >= #2007-01-05 16:47:00# AND Events.nTrimWeb EventID
= DickeyJohnContr oller.nTrimWebE ventID
ORDER BY Events.nTime
In SQL Server this query is the same except for the ' character instead
of the # for dates.
Interestingly enough removing the time condition from the where clause
also does not return all expected records. It looks like the join is
the problem but I don't see what's wrong. There are no null values
among the nTrimWebIDs in either table.
I must be missing something very obvious. Your help is greatly
appreciated!
Mrdjan
Here are some details about the setup:
The query is hardcoded in a VB .NET application that connects to any
ODBC compliant DB. I just change my connection string and can run
either Access or SQL Server.
The data types in the MS Access database are as follows:
Events table:
nTrimWebEventID ------ Primary key, Long Integer, Required
nAsset Text, Not Required
nTime Date, Required
nAddress1 Text, Not Required
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, Long Integer, Required
nActualApplicat ionRate Double, Not Required
By way of comparison, the SQL Server setup is as follows:
Events table:
nTrimWebEventID ------ Primary key, bigint, doesn't allow nulls
nAsset varchar(50), allows nulls
nTime datetime, does not allow nulls
nAddress1 varchar(50), allows nulls
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, bigint, not allow nulls
nActualApplicat ionRate float, allow nulls 1 3862 mr*****@hotmail .com wrote:
Interestingly enough removing the time condition from the where clause
also does not return all expected records. It looks like the join is
the problem but I don't see what's wrong. There are no null values
among the nTrimWebIDs in either table.
By "query in Access", you presumeably mean against an MS Jet database.
You are not using the correct ANSI format for the join. Try using the
excellent graphical MS Access query design view to do what you're doing.
You'll end up with something like:
SELECT
Events.nAsset,
Events.nTime,
DickeyJohnContr oller.nActualAp plicationRate,
Events.nAddress 1
FROM
Events INNER JOIN DickeyJohnContr oller ON Events.nTrimWeb EventID
= DickeyJohnContr oller.nTrimWebE ventID
WHERE
Events.nTime >= #2007-01-05 16:47:00#
ORDER BY
Events.nTime
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Roar |
last post by:
Hi!
I have got 1 access 2000 DB, one simple search form, and 3 .asp pages (one
for deleting a record, one for inserting a record and one for listing
searchresults). Deleting records works fine, Inserting records works fine,
but my headache is the searchform.. Whatever i type in the searchbox (sok),
the result is all database entries, not just the entries matching the term
I'm asking for. No error code is given.The code for the search is...
|
by: David |
last post by:
Hi everyone.
I have read every page that Google returns on this topic, but can't find
anything that resolves my problem.
Basically, I have an Access Database that does a number of different
calculations. One of these calculations is rather complex, and could not be
implemented properly using just nested IIF statements or anything similar, so
was coded using VBA (in the VBE) as a function. This calculation is included
in a query in the...
|
by: dk |
last post by:
Hi all,
Would appreciate some advice on the following:
I am trying to speed up an Access database connected to a SQL Server
back-end. I know I can use a pass-through query to pass the sql code
directly to SQL Server and speed things up.
However, I want to be able to "filter" or place conditions on which records
to retrieve based on LOCAL tables in Access.
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: MacDermott |
last post by:
I have an Access MDB which instantiates a class in a custom DLL, manipulates
it for a while, then sets it equal nothing.
The MDB does other things,too, and generally behaves itself as desired.
But, if I have done the DLL thing, even though I close the MDB, Access
refuses to close.
If I haven't used the DLL, all is well.
The DLL is one I wrote myself in VB.
I'm quite stumped, and rather guess the problem may be in the DLL (I've also
| |
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.
I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not...
|
by: olle |
last post by:
Hi folks.
I learning asp.net and compare it with traditional asp and
Access-developing.
The issue is this one:
1/I have this Ms Acceess adp-project application that works fine on my
Ms Sql server database.
In my main form I have an Access-combobox with Customer-names from my
customer table.
In this combo-box are about 2000 records.
|
by: Henning M |
last post by:
Hi all,
I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!
"Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
But when I try it from my vb.net app, I get ALL the records in the tabel??
What goes wrong?
I haven't been able to find any info on the net, besides others having
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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...
| |