Wrox Programmer Forums
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
Old April 4th, 2007, 11:48 PM
Authorized User
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with Query

Hi All,

i need some help with the following,
I am drawing data through access from an SQl datasource. Due to this i am unable to change existing data. I am running a query to create a total field from a qty * Saleprice. However the data stored in sql is (eg) qty = -1 and Saleprice = -$100. This is creating a problem for me as when i return a total my result = $100, where as it should be -$100, I need to know if i can somehow trim/remove the (-) character where it exists, from the Saleprice field, using a new field i have create in the query. My Query data look like so
Field = Qty (From SQL Source)
Field = Saleprice (From SQL Source)
Field = TotalSell:[qty]*[Saleprice] My created Field

Thanks in Advance

Old April 5th, 2007, 03:01 AM
Friend of Wrox
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post

Hi darrenb

TotalSell: [qty]*IIf([qty]<0 AND [Saleprice]<0,0-[Saleprice],[Saleprice])

This will turn the Sale price into a positive number if both values are negative, otherwise it will use whatever value is in the data.

I hope this helps.


Old April 9th, 2007, 11:35 PM
Authorized User
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts

Thx Rob,

That worked a treat, i now have vaild data again


Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM

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