More issues with Access 2003! Simple query not working!

[MkIIISupra]

I am trying use a query to build a report. The data comes from two different tables that are joined in the relationship window.

Table Definitions:
-- tblTempAddSch
|-- ID - AutoNumber
|-- distName – Text = 28
|-- county – Text = 2
|-- distNum – Text = 5
|-- elemNum – Text = 7
|-- charterNum – Text = 4
|-- partNum – Text = 4
|-- coDist – Text = 7
|-- soNum – Number = Long Integer
|-- altDesc – Text = 15
|-- coDistNum – Text = 10 :: Format – 00\-00000;;

-- tblMasterSchool
PK |-- coDistNum – Text = 10 :: Format – 00\-00000;;
|-- schNum – Text = 8
|-- schName – Text = 13


Query Definition
-- qrySEL_PdbReport

| SELECT tblTmpAddSch.distNum, tblTmpAddSch.distName, tblTmpAddSch.elemNum, tblMasterSchool.schNum, tblMasterSchool.schName
| FROM tblTmpAddSch INNER JOIN tblMasterSchool ON tblTmpAddSch.coDistNum = tblMasterSchool.coDistnum
| WHERE (((tblMasterSchool.coDistNum)=[tblTmpAddSch]![coDistNum]));

Now I have been able to do this up till this new version of Access! This is the second major problem I have had with the newer versions! Is there a way around this? I can't downgrade, corporate standard says no. So I have to figure out how to make these simple things I used to be able to do in Access 2000 without a second thought, work in the [SARCASM]“newer improved Access!”[/SARCASM]

Access Version: 2003(11.6566.6568) SP2
Part of Microsoft Office Professional Edition 2003

Windows Version: Microsoft Windows XP Professional 5.1.2600 Service Pack 1 build 2600

One by one the penguins steal my sanity!

 

[PHV]

The WHERE clause is irrelevant due the JOIN.
Anyway, why not explaining your issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MkIIISupra]

The issue is very simple, I get no return even when all criteria is met. Nothing, nada, nill. I have made this query and several similar to it, and all have worked until the recent "upgrade" to Access 2003. Everyone of my queries that is similar to this has stopped working. That is at least 20 applications that are affected. Same thing, nothing is returned and I have verified the data is there and even made sure that the type and sizes matched as well where needed.


One by one the penguins return my sanity, as day by day Microsoft steals my sanity!

OpenSuSE 10.0 kicks fanny!

 

[easyit]

Hi,

Q: the queries don't work or the reports don't work?

easyit

 

[MkIIISupra]

Both don't work.

One by one the penguins return my sanity, as day by day Microsoft steals my sanity!

OpenSuSE 10.0 kicks fanny!

 

[Golom]

Although, as PHV said, your WHERE clause is redundant, I would be suspicious of the bang(!) operator. Specification of fields in an SQL statement should be of the form

Tablename.FieldName

The bang operator should be used only for referencing Fields in a recordset or members of a collection.

It may be (and I'm not certain about this) that

[tblTmpAddSch]![coDistNum]

is not being interpreted as "field coDistNum in table tblTmpAddSch"

 

[MkIIISupra]

The SQL was written by Access based on the tables / fields I selected in the Query building tool provided. I finally got it to work by using a different field to key off of. But it's not what the design specs called for so in accordance with my bosses it's not right. But it is working for now just not how it was designed.

I tried without the JOIN and same results, no return.

One by one the penguins return my sanity, as day by day Microsoft steals my sanity!

OpenSuSE 10.0 kicks fanny!

 

[dhookom]

I expect you may be using an input mask, format property, or lookup field that is creating unequal values in your two tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]