Returning more data than requested

S

Sun Communications

I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?
 
S

Sun Communications

I am using MS SQL 2000.

Banana said:
When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun said:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

As I asked before, what's the complete SQL of the problematic query?

Sun said:
I am using MS SQL 2000.

Banana said:
When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun said:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
S

Sun Communications

Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


Banana said:
As I asked before, what's the complete SQL of the problematic query?

Sun said:
I am using MS SQL 2000.

Banana said:
When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun said:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


Banana said:
As I asked before, what's the complete SQL of the problematic query?

Sun said:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
S

Sun Communications

SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


Banana said:
Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun said:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


Banana said:
As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun said:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


Banana said:
Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun said:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
S

Sun Communications

Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and termcd
of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


Banana said:
Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun said:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


Banana said:
Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and termcd
of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


Banana said:
Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun said:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
S

Sun Communications

Yep same results. I have not seen that before myself that's why I put it
here. It's been a few years since I have had to write anything.


Banana said:
Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and termcd
of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


Banana said:
Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun Communications wrote:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
B

Banana

Personally, I'm suspecting there's some weird corruption going on
because it definitely shouldn't be doing that.

Few more tests to try out.

1) Re-do the same query but on the SQL Server directly (e.g. using SQL
Server Management Studio to execute the ad hoc query and verify it's not
something to do with SQL Server. I doubt SS is at fault, but want to
eliminate this from the list of offenders.)

2) Verify that the data types as seen in the dbo_calltrak in the
Access's table design view as what you see in the SSMS.

3) Create a new blank database, disable the AutoCorrect option then
import in all objects and re-run the query. See if it changes.

4) Verify there are no code running behind the form that may dynamically
modify the same query you tried to run.

See where it takes you.

Sun said:
Yep same results. I have not seen that before myself that's why I put it
here. It's been a few years since I have had to write anything.


Banana said:
Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and termcd
of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


:

Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun Communications wrote:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date: (mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for the
MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback on
what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS SQL 2000
database. So when I run my MSAccess query it is pulling in the extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your SQL
Server database? (I only ask because several people incorrectly use the
phrase 'SQL database' to refer to Microsoft SQL Server causing some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this statement =[Enter
Term Code]. It prompts for the code that I would like the report for example
"NT", but when it returns the query there are other term codes included. How
do I exclude the others.
 
G

Gina Whipp

Sun,

Make a copy of the query... then remove ALL parameters EXCEPT the termcd
one. What results do you get?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Sun Communications said:
Yep same results. I have not seen that before myself that's why I put it
here. It's been a few years since I have had to write anything.


Banana said:
Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and
termcd
of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


:

Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the
criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign.
Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun Communications wrote:
SELECT dbo_calltrak.number, dbo_calltrak.termcd,
dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date:
(mm/dd/yyyy)])))
AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for
the
MSAccess query.

In Access, if you open the query that is causing the problem in
design
view, you can switch over to SQL view by going Tools -> SQL View,
then
copy and paste the SQL for the query here so we can provide feedback
on
what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS
SQL 2000
database. So when I run my MSAccess query it is pulling in the
extra codes
that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic
query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or
your SQL
Server database? (I only ask because several people incorrectly
use the
phrase 'SQL database' to refer to Microsoft SQL Server causing
some
confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this
statement =[Enter
Term Code]. It prompts for the code that I would like the
report for example
"NT", but when it returns the query there are other term codes
included. How
do I exclude the others.
 
S

Sylvain Lafontaine

Take the time of refreshing all links for the ODBC linked tables using the
Linked Table Manager.

Also, if you know of to do it, use the SQL-Server Profiler to see what is
the query that Access is sending to the SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Banana said:
Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and
termcd of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


Banana said:
Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun Communications wrote:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date:
(mm/dd/yyyy)]))) AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for
the MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback
on what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS
SQL 2000 database. So when I run my MSAccess query it is pulling in
the extra codes that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your
SQL Server database? (I only ask because several people
incorrectly use the phrase 'SQL database' to refer to Microsoft
SQL Server causing some confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this
statement =[Enter Term Code]. It prompts for the code that I
would like the report for example "NT", but when it returns the
query there are other term codes included. How do I exclude the
others.
 
S

Sun Communications

Thanks everyone for the help, but Sylvain got it right. After I did the OBDC
link refresh the queries started working properly.


Sylvain Lafontaine said:
Take the time of refreshing all links for the ODBC linked tables using the
Linked Table Manager.

Also, if you know of to do it, use the SQL-Server Profiler to see what is
the query that Access is sending to the SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Banana said:
Fascinating. It is as if everything in the criteria is being ignored.
Can't say I've seen that behavior before.

Now, let's try and rewrite the query so the criteria of "MD2",
#09/14/2009# and "NT" are hardcoded in the query instead of parameters.

Do you still get the same result?

Sun said:
Here is an example of my returned query:
number termcd calldate campaign
5555555555 NA 9/11/2009 MD2
5555555555 NA 7/8/2009 MD2
5555555555 NA 7/9/2009 MD2
5555555555 NT 9/14/2009 MD2
5555555555 NI 9/15/2006 MOE
5555555555 NA 2/13/2007 IA1
5555555555 NA 9/11/2009 MD2

My query was for campaign "MD2", Begin and end date of 09/14/2009 and
termcd of "NT" and those are my results.

Data types are termcd and campaign are text and date is date/time


:

Thanks.

Looking over it, I don't see anything wrong with the SQL. When you use
"NT" and get too many results, is this a case of difference in
lettercase; getting "nt" when you only want "NT"? What kind of term
codes end up in your result that shouldn't be there? What data type is
the termcd column?



FWIW, Here's the query cleaned up. Note the changes made to the criteria
for the date range. I don't expect it to fix the original problem, but
it may be helpful in making it readable.

PARAMETERS
[Enter Term Code] TEXT(2),
[Enter Campaign] TEXT(20),
[Enter Start Date] DATE,
[Enter End Date] DATE
;
SELECT c.number,
c.termcd,
c.calldate,
c.campaign
FROM dbo_calltrack c
WHERE c.termcd = [Enter Term Code]
AND c.campaign = [Enter Campaign]
AND c.calldate BETWEEN [Enter Start Date]
AND [Enter End Date]
;

Note: I guessed on data types & length for the Term Code & Campaign. Do
adjust if it's not correct. While you aren't required to declare
parameters, I do feel that it's best to do so mainly for documentation
and to provide a strong-typed variable.



Sun Communications wrote:
SELECT dbo_calltrak.number, dbo_calltrak.termcd, dbo_calltrak.calldate,
dbo_calltrak.campaign
FROM dbo_calltrak
WHERE (((dbo_calltrak.termcd)=[Enter Term Code]) AND
((dbo_calltrak.calldate)>=CDate([Enter Start Date: ]) And
(dbo_calltrak.calldate)<DateAdd("d",1,CDate([Enter End Date:
(mm/dd/yyyy)]))) AND ((dbo_calltrak.campaign)=[Enter Campaign]));


:

Yes, that's already cleared up. But what I was asking is the SQL for
the MSAccess query.

In Access, if you open the query that is causing the problem in design
view, you can switch over to SQL view by going Tools -> SQL View, then
copy and paste the SQL for the query here so we can provide feedback
on what might be causing the problem.

Did that clarify?

Sun Communications wrote:
Should not have used SQL. I have an Access database linked to a MS
SQL 2000 database. So when I run my MSAccess query it is pulling in
the extra codes that I am not looking for.


:

As I asked before, what's the complete SQL of the problematic query?

Sun Communications wrote:
I am using MS SQL 2000.

:

When you say "my SQL database", do you mean MySQL database or your
SQL Server database? (I only ask because several people
incorrectly use the phrase 'SQL database' to refer to Microsoft
SQL Server causing some confusion.)

What is the complete SQL of the query you're using?

Sun Communications wrote:
I have a query for a field in my SQL database and use this
statement =[Enter Term Code]. It prompts for the code that I
would like the report for example "NT", but when it returns the
query there are other term codes included. How do I exclude the
others.
 
B

Banana

Sun said:
Thanks everyone for the help, but Sylvain got it right. After I did the OBDC
link refresh the queries started working properly.

Cool. Will have to remember that for the next time I get weird results
like you had. I'd have never thunk it's just a simple refresh. :)

Best of luck!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top