Hi
I've not explained this properly my problem is I am using a query as data
source for a report . My problem is that when I add a table to this query
there are no longer any records in the query.
HI this is the query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id] = Repairs.RepairsID) ON
fee.RepairsID = Repairs.RepairsID) ON Products.ProductID = [repair
details].ProductID) INNER JOIN [Repair Type] ON Repairs.RepairType = [Repair
Type].RepairType;
The tables are customer details for repair of an appliance . This query is
for data for invoices .After I followed the earlier suggestion re outside
join I get all fields displayed in query but a null error in the report . I
have just added the Repairs type table to query and I receive a Type mismatch
in expression error.
John W. Vinson said:HI this is the query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id] = Repairs.RepairsID) ON
fee.RepairsID = Repairs.RepairsID) ON Products.ProductID = [repair
details].ProductID) INNER JOIN [Repair Type] ON Repairs.RepairType = [Repair
Type].RepairType;
The tables are customer details for repair of an appliance . This query is
for data for invoices .After I followed the earlier suggestion re outside
join I get all fields displayed in query but a null error in the report . I
have just added the Repairs type table to query and I receive a Type mismatch
in expression error.
Are there in fact records in [repair details]? What is the datatype of
[RepairsID] in [Repairs] and in [Repair Details]? How about ProductID? If you
do this two tables at a time - joining Repairs to [Repair Details], and
Products to [Repair Details], and [Repairs] to [Repair Type], do you get
similar errors?
My data base is for my appliance repair company with customer repair details.
I've been able to list all records by using the outer join but when I try
and open the report I get a "INVALID USE OF NULL" I know this is because a
field is empty,
it is the extended price field !
How can I ask it to display £0.00 if empty?
John W. Vinson said:My data base is for my appliance repair company with customer repair details.
I've been able to list all records by using the outer join but when I try
and open the report I get a "INVALID USE OF NULL" I know this is because a
field is empty,
it is the extended price field !
How can I ask it to display £0.00 if empty?
Use the NZ() function to convert the Null (empty) field to 0. E.g.
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(NZ(Products.UnitPrice,0) * NZ([Quantity], 0) * (1-NZ([Discount],
1))/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType, Customers.CompanyName,
Customers.CompanyName
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id]=Repairs.RepairsID) ON
fee.RepairsID=Repairs.RepairsID) ON Products.ProductID=[repair
details].ProductID) LEFT JOIN (Customers RIGHT JOIN [Repair Type] ON
Customers.CompanyName=[Repair Type].[INVOICE TO]) ON
Repairs.RepairType=[Repair Type].TypeID;
HI
Thank you for your help. When i try to open query as you posted I receive
error
Syntax error in join i cannot find an error in query can you help
John W. Vinson said:HI
Thank you for your help. When i try to open query as you posted I receive
error
Syntax error in join i cannot find an error in query can you help
Odd. I didn't change the JOIN. Does the original query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType, Customers.CompanyName,
Customers.CompanyName
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id]=Repairs.RepairsID) ON
fee.RepairsID=Repairs.RepairsID) ON Products.ProductID=[repair
details].ProductID) LEFT JOIN (Customers RIGHT JOIN [Repair Type] ON
Customers.CompanyName=[Repair Type].[INVOICE TO]) ON
Repairs.RepairType=[Repair Type].TypeID;
work (at least does it only give the Invalid Use of Null error?)
Sorry I do not understand I posted the original query earlier
John W. Vinson said:Does the original query give the same error? That is - did the change I
suggested cause an (apparently unrelated) error message to appear?
Steve said:Just an idea .............
Make labour fee a "part" in your part table. If you have more than one
labour fee depending on the the type, duration or whatever of the labour,
make several labour records in your part table. Then treat labour as one or
more line items on your invoice the same as you treat one or more parts.
TblPart
PartID Partname PartPrice
1 PartA $3.50
2 PartB $.75
3 PartC $8.90
4 Labour Type A $25 (per hour)
5 Labour Type B $55 (per hour)
Steve
cjgav said:My problem is my invoices sometimes only have a labour fee and no parts
(products) are used this expression in the query creates a null error in
my
report,
ExtendedPrice: CCur([Products].[UnitPrice]
*[Quantity]*(1-[Discount])/100)*100
Any idea how I could prevent this
Steve said:Your original query returned all the records that met the criteria you
set
and where the joined fields at each join have the same value in both
tables.
When you added another table, you imposed another constraint where the
joined field in both the original query and in the new table has to have
the
same value. The amount of records changed because there are less records
in
the new table that match records in the original query where the joined
field have the same value.
Here's a simple example:
QryCustomer is based on TblCustomer with CustomerID as the PK and where
CustomerTypeID = 1 is residential and CustomerTypeID = 2 is commercial.
For
QryCustomer, CustomerTypeID criteria is set as 2 so QryCustomer returns
only
commercial customers. QryCustomer does not return all the records in
TblCustomer. Now say you have TblSale that looks like:
TblSale
SaleID
CustomerID
etc
and you join TblSale to TblCustomer in QryCustomer on CustomerID. Adding
TblSale will constrain QryCustomer to only return commercial customers
whom
you have made a sale to.
Steve
Hi
I have a problem in access 2002. When I add a table to a query it
changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?
Regards
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.