Query not retrieving data that is definitely there

[Guest]

I posted about this problem in the database design forum, but it seems to
definitely be a query issue.

I have a quoation table that is linked to a quoation details table. I then
have a form with a quoation table main form and quation details subform. This
subform contains three fields, all of which are controlled by the RTF2
ActiveX control so that words can be individually formatted. The subform is
linked to the main form by the Quotation No.

Data is entered normally into the form and it is entered into the table
correctly. The problem occurs in the query that I use to create a report on
the data. It worked fine before I instituded the RTF2 control and fine for
most of the time after it, but now it has stopped working entirely and I
cannot figure out why. Whenever I click on the query, it is blank even though
there is data in all the tables it is retrieving from and the report is
compltely blank.

Here is the SQL:

SELECT [Quotation Sheet].[CC4:], [Quotation Sheet].[CC3:], [Quotation
Sheet].[CC2:], [Quotation Sheet].[CC1:], [Quotation Sheet].QuoteID,
[Quotation Sheet].Date, [Quotation Sheet].[Quotation No], [Quotation
Sheet].[Customer Inquiry No], [Quotation Sheet].[Customer Information],
[Quotation Details].Description, [Quotation Details].Quantity, [Quotation
Details].[Price Per], [Matrix Employees].[Full Name], [Matrix Employees].Title
FROM [Matrix Employees] INNER JOIN ([Quotation Sheet] INNER JOIN [Quotation
Details] ON [Quotation Sheet].[Quotation No] = [Quotation Details].[Quotation
Number]) ON [Matrix Employees].EmployeeID = [Quotation Sheet].CboName;

 

[Allen Browne]

Okay, the query is not returning values.
The RTF control is not used in the query, so it is not a factor.
There must be a problem with the data itself.

Firstly, you may need to use outer joins. Details in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

There's no criteria in the query, so unless you are opening the report with
a WhereCondition, that can't be a factor.

Presumably you have already created relations (Tools | Relationships)
between the tables, with the Referenial Integrity box checked.

If you are still stuck, let us know which way the relationships go (which is
the "one" side), and what the data types and sizes are of the fields you
have joined, e.g. AutoNumber (Long Integer) joined to Number (Long Integer.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I posted about this problem in the database design forum, but it seems to
definitely be a query issue.

I have a quoation table that is linked to a quoation details table. I then
have a form with a quoation table main form and quation details subform.
This
subform contains three fields, all of which are controlled by the RTF2
ActiveX control so that words can be individually formatted. The subform
is
linked to the main form by the Quotation No.

Data is entered normally into the form and it is entered into the table
correctly. The problem occurs in the query that I use to create a report
on
the data. It worked fine before I instituded the RTF2 control and fine for
most of the time after it, but now it has stopped working entirely and I
cannot figure out why. Whenever I click on the query, it is blank even
though
there is data in all the tables it is retrieving from and the report is
compltely blank.

Here is the SQL:

SELECT [Quotation Sheet].[CC4:],
[Quotation Sheet].[CC3:],
[Quotation Sheet].[CC2:],
[Quotation Sheet].[CC1:],
[Quotation Sheet].QuoteID,
[Quotation Sheet].Date,
[Quotation Sheet].[Quotation No],
[Quotation Sheet].[Customer Inquiry No],
[Quotation Sheet].[Customer Information],
[Quotation Details].Description,
[Quotation Details].Quantity,
[Quotation Details].[Price Per],
[Matrix Employees].[Full Name],
[Matrix Employees].Title
FROM [Matrix Employees]
INNER JOIN ([Quotation Sheet]
INNER JOIN [Quotation Details]
ON [Quotation Sheet].[Quotation No]
= [Quotation Details].[Quotation Number])
ON [Matrix Employees].EmployeeID
= [Quotation Sheet].CboName;