Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 3rd, 2005, 02:36 PM
Friend of Wrox
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default Wrong result on select query

Hi experienced programmers,
I'm surprised with the SQL qry which follows:

PARAMETERS [tblDadosIN]![CodigoNome] Long;
SELECT tblDadosIN.CodigoNome, tblFuncionarios.Nome, tblDadosIN.Data, tblDadosIN.Dia, tblDadosIN.Ocorrencia, tblDadosIN.HExtra
FROM tblFuncionarios INNER JOIN tblDadosIN ON tblFuncionarios.CodigoNome = tblDadosIN.CodigoNome
WHERE (tblDadosIN.CodigoNome)=[tblDadosIN]![CodigoNome];

my tblDadosIN is a table where the parameter that is filled
when prompted happens four times ( four rows). Although when
i run the query above prompted by the parameter
the datasheet displayed as the result qry has five times
more rows than the source table (twenty rows). Would some one
be pacient enough to teach me the right road ?:(
Rgds Penta.

  #2 (permalink)  
Old May 5th, 2005, 07:14 AM
Friend of Wrox
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

I'm not much help with parameter queries. Never really use them. (As you may have gathered from your prior query about your report and the form.) Therefore, I'm not really clear about what you're trying to accomplish.

But I'll take a guess here. Since you are INNER JOINing, maybe you should put tblDadosIN first....

FROM tblDadosIN INNER JOIN tblFuncionarios ...

Just a guess that tblDadosIN has to know the criteria for each record it's trying to join with tblFuncionarios.

I don't use PARAMETERS since they seem superfluous to me (except that PARAMETERS will validate that user input is Long). You could simply leave off PARAMETERS and get the same result... if you want the user prompted to enter [tblDadosIn]![CodigoNome].

What confuses me about your query is that the WHERE clause is essentially saying, "give me everything in tblDadosIN". That is, except for the fact that you've tried to declare "[tblDadosIN]![CodigoNome]" as a parameter, "tblDadosIN.CodigoName" and "[tblDadosIN]![CodigoNome]" mean the same thing in an Access query. I don't know if the fact that you've declared it as a parameter overrides the functional definition which means, give me the value of the field CodigoName from table tblDadosIN. Perhaps because you have declared it as a PARAMETER, it thinks it has to ask for a value?

Just curious... why do you use PARAMETERS in your queries? If you're building code to use the queries, why not just build the SQL statement and substitute the parameters in the SQL? If you're trying to control the data that displays on a report or form, just set the filter criteria for the report or form. If you're trying to control what displays on a subform or subreport, just use the Master/Child link fields.

I suppose I should learn more about PARAMETERS. Maybe there is a really good use for them. But as I recall, the last time I tried to use them (so many years ago) I gave up in frustration because I kept getting problems similar to those you are having.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
  #3 (permalink)  
Old May 5th, 2005, 07:57 AM
Friend of Wrox
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks u Randall for ur care,
I had this unexpected behavior on an apparently elementary task and thought
that it was something wrong i was doing. I have been learning SQL recently
and i think i read something about the odd result on a book which is not
on hands. If i find something about the behavior i'll let u know. I've been
using parameters before i create a criteria form as a step for building
a desktop application, and i'm addicted to MS ways of doing things with as
few coding as possible.
Rgds Penta.:)

  #4 (permalink)  
Old May 5th, 2005, 09:05 AM
Friend of Wrox
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts

Again on the same subject,
I've tried to run the query without the parameter using the criteria directly
on the qry grid and it worked OK. Paying much attention on the wrong result
i've had before i've missreported the result. When i run the parameter qry the
result is the whole table.That is the parameter is not filtering at all.
Trying to fill the SQL directly with the parameter value as an alternate way for instance, and go back to the qry grid and try to run the query i've had an error on access which stops working. Yes, as Randall says parameters dont seem to be reliable.
rgds P.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Replacing null table entries in select result? TPP SQL Server 2005 6 July 11th, 2007 03:10 AM
Unexpected result from the query tsimsha Classic ASP Databases 1 August 23rd, 2004 03:50 AM
Query Result mateenmohd SQL Server 2000 0 November 1st, 2003 03:53 AM
SELECT and INSERT in wrong order sgarstin MySQL 0 October 16th, 2003 02:31 PM
Archiving query result Ned SQL Server 2000 5 October 8th, 2003 03:34 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.