=Sum([?]+[??])?

  • Thread starter Thread starter GitarJake
  • Start date Start date
G

GitarJake

Hi all,

Access2000, Win98

I really thought I had this function down, but apparently not.

I am trying to add the values of 2 fields. They are calculated in a query,
not on the form.

Here are my efforts:

=[Field1]+[Field2]=Field1Field2 (i.e: 10+20=1020)

=[Control1]+[Control2]=#Name?

=Sum([Control1]+[Control2])=#Error

=Sum([Field1]+[Field2])=#Error

[Field1]+[Field2]=#Name?

[Control1]+[Control2]=#Name?

Am I missing something here?

TIA,

Jake
 
GitarJake said:
Hi all,

Access2000, Win98

I really thought I had this function down, but apparently not.

I am trying to add the values of 2 fields. They are calculated in a query,
not on the form.

Here are my efforts:

=[Field1]+[Field2]=Field1Field2 (i.e: 10+20=1020)
Click to expand...

This one right here tells us that these are text fields rather than numeric
fields. Change your expression in the query to force a numeric output
instead of a string. You can use one of the following depending on your
needs.


Val()
CInt()
CLng()
CDbl()
CCur()
 
Hi,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
It sounds like you are adding fields that are of the Text data type, not
number. Try wrapping the field name with the CInt() function, Convert to
Integer. For example:

expression: CInt([Field1])+CInt([Field2])

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
 
Hi Amy,

Both of the following methods produce #Error:

=CInt([Field1])+CInt([Field2]) (in the controlsource of the textbox)

and

expression: CInt([Field1])+CInt([Field2]) (as an added field in the query)


What am I doing wrong?

TIA,

Jake
 
Hi,
What are the values like? An integer can only handle up to 32,000
Would any of the additions exceed that? If so, use CLng to convert to
a long integer.

HTH
Dan Artuso, MVP

GitarJake said:
Hi Amy,

Both of the following methods produce #Error:

=CInt([Field1])+CInt([Field2]) (in the controlsource of the textbox)

and

expression: CInt([Field1])+CInt([Field2]) (as an added field in the query)


What am I doing wrong?

TIA,

Jake



Amy Vargo said:
Hi,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
It sounds like you are adding fields that are of the Text data type, not
number. Try wrapping the field name with the CInt() function, Convert to
Integer. For example:

expression: CInt([Field1])+CInt([Field2])

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
Click to expand...
Click to expand...
 
Hi Dan,

I may be taking a completely wrong approach here, which may be why Amy's
suggestion didn't work.

What I want is a small app that will take in start and end hours on a daily
basis; then after two weeks, calculate total hours worked, gross and net
earnings, etc.

BTW, I looked everywhere I knew to look for a template for this, but to no
avail. If you know of one, please point the way!

On one record I have:

1MonIn; 1MonOut; 1TueIn; 1TueOut; 1WedIn; 1WedOut; Etc.

These are all Date/Time formats

Then, in the underlying query i have an expression like below for each
workday:

MondayTotal: DateDiff("n",[1MonIn],[1MonOut])\60 & ":" &
DateDiff("n",[1MonIn],[1MonOut]) Mod 60

So when I input start and stop times (i.e: If I put 10:00 AM in 1MonIn and
3:00 PM in 1MonOut) I get 5:0 in the MondayTotal.

Now I want to add up all the daily hours, hence my original post. I either
got #Error, #Name or 5:05:05:05:05:05:0 etc

Dan Artuso said:
Hi,
What are the values like? An integer can only handle up to 32,000
Would any of the additions exceed that? If so, use CLng to convert to
a long integer.

HTH
Dan Artuso, MVP

Hi Amy,

Both of the following methods produce #Error:

=CInt([Field1])+CInt([Field2]) (in the controlsource of the textbox)

and

expression: CInt([Field1])+CInt([Field2]) (as an added field in the query)


What am I doing wrong?

TIA,

Jake



Amy Vargo said:
Hi,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
It sounds like you are adding fields that are of the Text data type, not
number. Try wrapping the field name with the CInt() function, Convert to
Integer. For example:

expression: CInt([Field1])+CInt([Field2])

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
Click to expand...
Click to expand...
Click to expand...
 
Hi Dan,

I may be taking a completely wrong approach here, which may be why Amy's
suggestion didn't work.

What I want is a small app that will take in start and end hours on a daily
basis; then after two weeks, calculate total hours worked, gross and net
earnings, etc.
Click to expand...

A Totals query can do this, with some help from the DateDiff function.
BTW, I looked everywhere I knew to look for a template for this, but to no
avail. If you know of one, please point the way!

On one record I have:

1MonIn; 1MonOut; 1TueIn; 1TueOut; 1WedIn; 1WedOut; Etc.

These are all Date/Time formats
Click to expand...

Ummmm... sorry, but this is VERY badly non-normalized.

A much better table structure would be "tall and thin" - fields such
as EmployeeID; TimeIn; TimeOut. The time fields would of course be
date/time fields so that you would have *ten records* in the table
over the course of two (five-day) weeks - or however many days the
person actually works.
Then, in the underlying query i have an expression like below for each
workday:

MondayTotal: DateDiff("n",[1MonIn],[1MonOut])\60 & ":" &
DateDiff("n",[1MonIn],[1MonOut]) Mod 60

So when I input start and stop times (i.e: If I put 10:00 AM in 1MonIn and
3:00 PM in 1MonOut) I get 5:0 in the MondayTotal.

Now I want to add up all the daily hours, hence my original post. I either
got #Error, #Name or 5:05:05:05:05:05:0 etc
Click to expand...

That's because MondayTotal is neither a number nor a date/time - it's
a String. You'll need to *sum the times* first, and *then* use an
expression to format that sum.
 
Back
Top