Access 2003 - Need assistance with simple math

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

I have a table with "Units Received" and "Units Shipped" fields, and want to
display the difference between the two in a third field "Units In Stock". A
simple expression works in Access 2007, but I can't get it to work in Access
2003. It appears to be correct in the SQL statement.

PLEASE HELP!!
 
Deb said:
I have a table with "Units Received" and "Units Shipped" fields, and want
to
display the difference between the two in a third field "Units In Stock".
A
simple expression works in Access 2007, but I can't get it to work in
Access
2003. It appears to be correct in the SQL statement.
Click to expand...


What is the expression, what is the SQL statement, and in what way does it
not work? Do you get an error message? Do you get a result that differs
from what you expect?

I can't think of a way that something like this should be different between
Access 2003 and 2007.
 
Dirk Goldgar said:
What is the expression, what is the SQL statement, and in what way does it
not work? Do you get an error message? Do you get a result that differs
from what you expect?

I can't think of a way that something like this should be different between
Access 2003 and 2007.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Dirk:

Thank you so much for responding. I copied the SQL statement (below). The
line
"tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock," is
the one that appears to me to be correct as far as the expression is
concerned.

The query displays a table, and displays a column for "UnitsInStock", along
with the "UnitsReceived" and "UnitsShipped". But there is nothing in the
UnitsInStock column. I have not checked the SQL statement in the 2007
version, and I will check that, but I can't determine why this would work in
the 2007 version and not in 2003. I'm more familiar with 2007, but my client
is using 2003. Any assistance you can offer is greatly appreciated!
SELECT tbl_Inventory.PartNumber, tbl_Inventory.SerialNumber, tbl_Inventory.IndexCode, tbl_Inventory.Sponsor, tbl_Inventory.Program, tbl_Inventory.DivisionCode, tbl_Inventory.Requisition, tbl_Inventory.EMR, tbl_Inventory.PurposeCode, tbl_Inventory.UnitOfIssue, tbl_Inventory.UnitCost,
Click to expand...

tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock,

tbl_Inventory.UnitsReceived, tbl_Inventory.UnitsShipped,
tbl_Inventory.Building, tbl_Inventory.Location, tbl_Inventory.[Warehouse#],
tbl_Inventory.NHA, tbl_Inventory.ConditionCode, tbl_Inventory.DateModified,
tbl_Inventory.UserModified
FROM tbl_Inventory;
 
Deb said:
Thank you so much for responding. I copied the SQL statement (below).
The
line
"tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock,"
is
the one that appears to me to be correct as far as the expression is
concerned.

The query displays a table, and displays a column for "UnitsInStock",
along
with the "UnitsReceived" and "UnitsShipped". But there is nothing in the
UnitsInStock column. I have not checked the SQL statement in the 2007
version, and I will check that, but I can't determine why this would work
in
the 2007 version and not in 2003. I'm more familiar with 2007, but my
client
is using 2003. Any assistance you can offer is greatly appreciated!
SELECT tbl_Inventory.PartNumber, tbl_Inventory.SerialNumber,
tbl_Inventory.IndexCode, tbl_Inventory.Sponsor, tbl_Inventory.Program,
tbl_Inventory.DivisionCode, tbl_Inventory.Requisition, tbl_Inventory.EMR,
tbl_Inventory.PurposeCode, tbl_Inventory.UnitOfIssue,
tbl_Inventory.UnitCost,
Click to expand...

tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock,

tbl_Inventory.UnitsReceived, tbl_Inventory.UnitsShipped,
tbl_Inventory.Building, tbl_Inventory.Location,
tbl_Inventory.[Warehouse#],
tbl_Inventory.NHA, tbl_Inventory.ConditionCode,
tbl_Inventory.DateModified,
tbl_Inventory.UserModified
FROM tbl_Inventory;
Click to expand...


You shouldn't use the bang (!) for table qualifications in a query. In
other words, don't write "tbl_Inventory!UnitsReceived" write
"tbl_Inventory.UnitsReceived" instead. Access 2007 allows the bang? That
must be a rare case where A2007 is more tolerant than other version.

You don't need all those table-qualifiers anyway (though Access would add
them in the query designer), since your query uses only one table and your
field names aren't reserved words. Try this:

--------------- start of SQL statement ----------------
SELECT
PartNumber,
SerialNumber,
IndexCode,
Sponsor,
Program,
DivisionCode,
Requisition,
EMR,
PurposeCode,
UnitOfIssue,
UnitCost,
UnitsReceived-UnitsShipped AS UnitsInStock,
UnitsReceived,
UnitsShipped,
Building,
Location,
[Warehouse#],
NHA,
ConditionCode,
DateModified,
UserModified
FROM tbl_Inventory;

--------------- end of SQL statement ----------------
 
Dirk Goldgar said:
Deb said:
Thank you so much for responding. I copied the SQL statement (below).
The
line
"tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock,"
is
the one that appears to me to be correct as far as the expression is
concerned.

The query displays a table, and displays a column for "UnitsInStock",
along
with the "UnitsReceived" and "UnitsShipped". But there is nothing in the
UnitsInStock column. I have not checked the SQL statement in the 2007
version, and I will check that, but I can't determine why this would work
in
the 2007 version and not in 2003. I'm more familiar with 2007, but my
client
is using 2003. Any assistance you can offer is greatly appreciated!
SELECT tbl_Inventory.PartNumber, tbl_Inventory.SerialNumber,
tbl_Inventory.IndexCode, tbl_Inventory.Sponsor, tbl_Inventory.Program,
tbl_Inventory.DivisionCode, tbl_Inventory.Requisition, tbl_Inventory.EMR,
tbl_Inventory.PurposeCode, tbl_Inventory.UnitOfIssue,
tbl_Inventory.UnitCost,
Click to expand...

tbl_Inventory!UnitsReceived-tbl_Inventory!UnitsShipped AS UnitsInStock,

tbl_Inventory.UnitsReceived, tbl_Inventory.UnitsShipped,
tbl_Inventory.Building, tbl_Inventory.Location,
tbl_Inventory.[Warehouse#],
tbl_Inventory.NHA, tbl_Inventory.ConditionCode,
tbl_Inventory.DateModified,
tbl_Inventory.UserModified
FROM tbl_Inventory;
Click to expand...


You shouldn't use the bang (!) for table qualifications in a query. In
other words, don't write "tbl_Inventory!UnitsReceived" write
"tbl_Inventory.UnitsReceived" instead. Access 2007 allows the bang? That
must be a rare case where A2007 is more tolerant than other version.

You don't need all those table-qualifiers anyway (though Access would add
them in the query designer), since your query uses only one table and your
field names aren't reserved words. Try this:

--------------- start of SQL statement ----------------
SELECT
PartNumber,
SerialNumber,
IndexCode,
Sponsor,
Program,
DivisionCode,
Requisition,
EMR,
PurposeCode,
UnitOfIssue,
UnitCost,
UnitsReceived-UnitsShipped AS UnitsInStock,
UnitsReceived,
UnitsShipped,
Building,
Location,
[Warehouse#],
NHA,
ConditionCode,
DateModified,
UserModified
FROM tbl_Inventory;

--------------- end of SQL statement ----------------

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you!! I did use the query designer to build the query, and when I checked the 2007 version, it looks a lot like your solution. I'll try that tomorrow.
Click to expand...
 
Back
Top