Negative Figures In A Query

[Bateman28]

Hi

I have greated a query that sums up alot of different fields into numbers,
the only problem is the numbers always return as a negative! How do I need to
wright the expressions so that the figures return a positive number and not a
negative.

Example: I get (-214) when it should be (214)

Any help would be great

 

[RonaldoOneNil]

Abs(-214)

or

-214 *-1

 

[Bateman28]

I have tried both types and i get the same error.

Error circular reference caused by alias " Field Name " in Query Definitions
select list.

The query has selected files that i can't change as they pick up specific
files in a specific format that i need.....

anymore help would be appreciated

 

[John Spencer]

Post the SQL View of your query.

Try changing the alias of "Field Name" in the query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have tried both types and i get the same error.

Correct the error in your query. If you would like help doing so please post
the SQL view of the query - we can't see it from here.

Error circular reference caused by alias " Field Name " in Query Definitions
select list.

If you're trying to define [Field Name] on the basis of [Field Name] you'll
get this error; e.g.

[Field Name]: Abs([Field Name])

can't work. Try

[Corrected field name]: Abs([Field name])

or some *other* name.

The query has selected files that i can't change as they pick up specific
files in a specific format that i need.....

You'll need to explain that statement. Do you mean "fields" instead of
"files"? What does the format have to do with anything?

 

[Bateman28]

As you can see there is alot of script in my query:

SELECT DISTINCTROW [Branch Codes].MarketArea, IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]) AS Branch, Year([SaveDate]) AS [Year],
Format([SaveDate],'mmmm') AS [Month], Month([SaveDate]) AS MonthNum,
[NonOpps]+[Opps] AS Calls, Sum(tbl_MAIN.NonOpps) AS NonOpps,
Sum(tbl_MAIN.Opps) AS Opps, Sum(tbl_MAIN.BookingMade) AS SumOfBookingMade,
Sum(tbl_MAIN.NonBookings) AS SumOfNonBookings, Sum(tbl_MAIN.PriceMatch) AS
SumOfPriceMatch, Sum(tbl_MAIN.Service) AS SumOfService,
Sum(tbl_MAIN.ServiceMot) AS SumOfServiceMot, Sum(tbl_MAIN.ServiceRepair) AS
SumOfServiceRepair, Sum(tbl_MAIN.MOT) AS SumOfMOT, Sum(tbl_MAIN.MOTRepair) AS
SumOfMOTRepair, Sum(tbl_MAIN.MB2U) AS SumOfMB2U, Sum(tbl_MAIN.Repair) AS
SumOfRepair, Sum(tbl_MAIN.RepeatRepair) AS SumOfRepeatRepair,
Sum(tbl_MAIN.VHC) AS SumOfVHC, Sum(tbl_MAIN.VHCRepair) AS SumOfVHCRepair,
Sum(tbl_MAIN.WarrantyRepair) AS SumOfWarrantyRepair, Sum(tbl_MAIN.NotDueYet)
AS SumOfNotDueYet, Sum(tbl_MAIN.CustomerAvailabiity) AS
SumOfCustomerAvailabiity, Sum(tbl_MAIN.Price) AS SumOfPrice,
Sum(tbl_MAIN.NoLongerHaveTheVehicle) AS SumOfNoLongerHaveTheVehicle,
Sum(tbl_MAIN.SellingVehicle) AS SumOfSellingVehicle, Sum(tbl_MAIN.Escalation)
AS SumOfEscalation, Sum(tbl_MAIN.ServiceQuotation) AS SumOfServiceQuotation,
Sum(tbl_MAIN.ServiceMotQuotation) AS SumOfServiceMotQuotation,
Sum(tbl_MAIN.MOTQuotation) AS SumOfMOTQuotation,
Sum(tbl_MAIN.BookedAtOtherMBRSite) AS SumOfBookedAtOtherMBRSite,
Sum(tbl_MAIN.CollectionAndDeliveryArranged) AS
SumOfCollectionAndDeliveryArranged, Sum(tbl_MAIN.In48Hrs) AS SumOfIn48Hrs,
Sum(tbl_MAIN.Amendment) AS SumOfAmendment, Sum(tbl_MAIN.Cancellation) AS
SumOfCancellation, Sum(tbl_MAIN.ServiceInformation) AS
SumOfServiceInformation, Sum(tbl_MAIN.TransferredInErrorNew) AS
SumOfTransferredInErrorNew, Sum(IIf([TransferredInErrorReason]="STM
Enquiry",1,0)) AS [TIE STM Enquiry],
Sum(IIf([TransferredInErrorReason]="Breakdown",1,0)) AS [TIE Breakdown],
Sum(IIf([TransferredInErrorReason]="Progress Update - Vehicle On Site
Today",1,0)) AS [TIE Update], Sum(IIf([TransferredInErrorReason]="Parts
Enquiry",1,0)) AS [TIE Parts Enquiry],
Sum(IIf([TransferredInErrorReason]="Directions",1,0)) AS [TIE Directions],
Sum(IIf([TransferredInErrorReason]="Technical Enquiry",1,0)) AS [TIE
Technical], Sum(IIf([TransferredInErrorReason]="Walk In / Same Day
Request",1,0)) AS [TIE Walkin], Sum(IIf([TransferredInErrorReason]="Body
Services / Accident Repair
Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Dealership
Information",1,0))+Sum(IIf([TransferredInErrorReason]="Sales",1,0))+Sum(IIf([TransferredInErrorReason]="Service
History Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Tyre
Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Windscreen Enquiry",1,0))
AS [TIE Misc], Sum(tbl_MAIN.MBRUCBooking) AS SumOfMBRUCBooking
FROM [Branch Codes] RIGHT JOIN tbl_MAIN ON [Branch Codes].BranchName =
tbl_MAIN.Retailer
GROUP BY [Branch Codes].MarketArea, IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]), Year([SaveDate]),
Format([SaveDate],'mmmm'), Month([SaveDate])
HAVING (((IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]))>""))
ORDER BY IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]), Month([SaveDate]);


Hope This Helps John!