How to query a field for Absolute Value in Access 2007

  • Thread starter Thread starter ComTech
  • Start date Start date
C

ComTech

I have a customer with a field in a table that I need to find the absolute
value of and so far no syntax I have tried has been successful. The field is
populated with both positive and negative numbers. I simply need to convert
these to a positive number for my further calculations. Consider the field
in question as MyTable.MyField which is a Number - Long Integer.
I tried this by creating a Make Table Query and used the Expression Builder
to create the desired results. Unfortunately I am not a visual basic or SQL
language expert by any means. The vast majority of these values are negative
numbers. If at worst case I had to just multiple everything by -1 then I
could deal with the few isolated negatives that would create. Below are a
couple of example of what produced zero results in my query.
[MyTable]![MyField]*-1
Abs ([MyTable]![MyField])
I would appreciate any ideas. Thanks in advance.
 
That's what I thought but I get nothing back from such a query. I can put
other conditions (not math functions) such as >500 and I get results. That
tells me the Add Table query is looking at the right table and is creating
the target table correctly. The darn syntax or something unknown must be
hanging it up. There are no errors of any kind.

Golfinray said:
If your field is numeric, abs([mytable]![myfield]) should work.

ComTech said:
I have a customer with a field in a table that I need to find the absolute
value of and so far no syntax I have tried has been successful. The field is
populated with both positive and negative numbers. I simply need to convert
these to a positive number for my further calculations. Consider the field
in question as MyTable.MyField which is a Number - Long Integer.
I tried this by creating a Make Table Query and used the Expression Builder
to create the desired results. Unfortunately I am not a visual basic or SQL
language expert by any means. The vast majority of these values are negative
numbers. If at worst case I had to just multiple everything by -1 then I
could deal with the few isolated negatives that would create. Below are a
couple of example of what produced zero results in my query.
[MyTable]![MyField]*-1
Abs ([MyTable]![MyField])
I would appreciate any ideas. Thanks in advance.
Click to expand...
Click to expand...
 
ComTech said:
I have a customer with a field in a table that I need to find the
absolute value of and so far no syntax I have tried has been
successful. The field is populated with both positive and negative
numbers. I simply need to convert these to a positive number for my
further calculations. Consider the field in question as
MyTable.MyField which is a Number - Long Integer.
I tried this by creating a Make Table Query and used the Expression
Builder to create the desired results. Unfortunately I am not a
visual basic or SQL language expert by any means. The vast majority
of these values are negative numbers. If at worst case I had to just
multiple everything by -1 then I could deal with the few isolated
negatives that would create. Below are a couple of example of what
produced zero results in my query. [MyTable]![MyField]*-1
Abs ([MyTable]![MyField])
I would appreciate any ideas. Thanks in advance.
Click to expand...

This is the correct syntax: Abs([MyTable]![MyField]), although I would
be more likely to use Abs([MyTable].[MyField]) in a query.

What happened when you tried it? Show us the query that yielded the
incorrect results (switch your query to SQL View via the View menu, or
the toolbar button, or the right-click menu) and paste the sql statement
into your reply.

It helps to see a few rows of sample data, as well as the incorrect
results from that sample data
 
Thanks Bob. When I run the SQL statement below via the builder I get only
the absolute value of the 4 positive numbers that already exist in the table.
______________________________
SELECT dbo_MailersSelected.[MailSelId] INTO ComTech_MailersSelected
FROM dbo_MailersSelected
WHERE
(((dbo_MailersSelected.[MailSelId])=Abs([dbo_MailersSelected]![MailSelId])));
______________________________

If I change it to trying to use multiplication then the resulting table is
empty.
______________________________
SELECT dbo_MailersSelected.[MailSelId] INTO ComTech_MailersSelected
FROM dbo_MailersSelected
WHERE
(((dbo_MailersSelected.[MailSelId])=[dbo_MailersSelected]![MailSelId]*-1));
______________________________
I sure do appreciate you taking the time to offer suggestions.


Bob Barrows said:
ComTech said:
I have a customer with a field in a table that I need to find the
absolute value of and so far no syntax I have tried has been
successful. The field is populated with both positive and negative
numbers. I simply need to convert these to a positive number for my
further calculations. Consider the field in question as
MyTable.MyField which is a Number - Long Integer.
I tried this by creating a Make Table Query and used the Expression
Builder to create the desired results. Unfortunately I am not a
visual basic or SQL language expert by any means. The vast majority
of these values are negative numbers. If at worst case I had to just
multiple everything by -1 then I could deal with the few isolated
negatives that would create. Below are a couple of example of what
produced zero results in my query. [MyTable]![MyField]*-1
Abs ([MyTable]![MyField])
I would appreciate any ideas. Thanks in advance.
Click to expand...

This is the correct syntax: Abs([MyTable]![MyField]), although I would
be more likely to use Abs([MyTable].[MyField]) in a query.

What happened when you tried it? Show us the query that yielded the
incorrect results (switch your query to SQL View via the View menu, or
the toolbar button, or the right-click menu) and paste the sql statement
into your reply.

It helps to see a few rows of sample data, as well as the incorrect
results from that sample data

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
ComTech said:
Thanks Bob. When I run the SQL statement below via the builder I get
only the absolute value of the 4 positive numbers that already exist
in the table. ______________________________
SELECT dbo_MailersSelected.[MailSelId] INTO ComTech_MailersSelected
FROM dbo_MailersSelected
WHERE
Click to expand...
(((dbo_MailersSelected.[MailSelId])=Abs([dbo_MailersSelected]![MailSelId
])));

Why wouldn't you only get the positive numbers? You are telling it to
only return the rows where the value of MailSelId equals the absolute
value of MailSelId - that only happens when MailSelId contains a
positive number.

Are you putting this in the WHERE clause because you don't know where to
put it? If you want to return all the rows, you can't use the Criteria
line in the grid for your formula: you have to put it in the Field line.
Also, why are you doing a MakeTable query? Just do a simple SELECT. The
sql will look like this:

SELECT dbo_MailersSelected.[MailSelId],
Abs([dbo_MailersSelected]![MailSelId]))) As AbsValue
FROM dbo_MailersSelected

Paste that into the sql view and switch to Design View to see how you
were supposed to do it.
 
Excellent. That worked perfectly. Thank you so much for the assistance. I
was off base from the start and didn't even realize it. Have a great day!

Bob Barrows said:
ComTech said:
Thanks Bob. When I run the SQL statement below via the builder I get
only the absolute value of the 4 positive numbers that already exist
in the table. ______________________________
SELECT dbo_MailersSelected.[MailSelId] INTO ComTech_MailersSelected
FROM dbo_MailersSelected
WHERE
Click to expand...
(((dbo_MailersSelected.[MailSelId])=Abs([dbo_MailersSelected]![MailSelId
])));

Why wouldn't you only get the positive numbers? You are telling it to
only return the rows where the value of MailSelId equals the absolute
value of MailSelId - that only happens when MailSelId contains a
positive number.

Are you putting this in the WHERE clause because you don't know where to
put it? If you want to return all the rows, you can't use the Criteria
line in the grid for your formula: you have to put it in the Field line.
Also, why are you doing a MakeTable query? Just do a simple SELECT. The
sql will look like this:

SELECT dbo_MailersSelected.[MailSelId],
Abs([dbo_MailersSelected]![MailSelId]))) As AbsValue
FROM dbo_MailersSelected

Paste that into the sql view and switch to Design View to see how you
were supposed to do it.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Back
Top