473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculated Field problem

Hi

Can anyone spot an obvious problem with the following calculated field
on a report. Im trying to use the field [End Date] only when it
contains data (which it normally doesnt)

=(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])/7)*[Weekly Impact]*-1

I'm just getting a blank field, or alternatively a better way of
getting the result

Thanks for any suggestions
Paul

Jun 6 '06 #1
5 1459
Hi Paul

I am no expert here with Access I have just a good background in
programming.

Is this supposed to be

IIf([End Date] Is Null,#31/03/2007#

or

If([End Date] Is Null,#31/03/2007#

Just my observation.

Maybe it helped.

DaveG
pa************@hotmail.com wrote:
Hi

Can anyone spot an obvious problem with the following calculated field
on a report. Im trying to use the field [End Date] only when it
contains data (which it normally doesnt)

=(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])/7)*[Weekly Impact]*-1

I'm just getting a blank field, or alternatively a better way of
getting the result

Thanks for any suggestions
Paul


--

Jun 6 '06 #2
One thing you could try is to break your expression apart and just get
one value at a time. If that expression works then add the next
expression to it.
=(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End

Date])),[Forecast Date])/7)*[Weekly Impact]*-1

try enclosing the -1 in parentheses. I think the code is interpreting
this

[weekly impact] * - 1

instead of

[weekly impact] * (-1)

Or

(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])
/7)

you are getting a difference of days and dividing that by 7. Then you
are multiplying this value by a value contained in [weekly impact]

*[Weekly Impact]

then it appears you want the total value to be negative. If you enclose
(-1), then this is what the computer would interpret.

*
(-1)

Try breaking each expression apart to see if you get the desired value.
Start with the difference of days. If you get the correct result then
try adding the divide value by 7 part. If that gives a correct result
then add the * [weekly impact]...
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 6 '06 #3
Change iif([End Date] Is Null, ...

Make it iif(IsNull([End Date)=True, ...

The "=true" is not strictly necassary, since IsNull() returns only true or
false anyway, but I have grown fond of seeing explicit comparison to False
or True instead of having to decode what a function returns when I'm
actually trying to figure out something else.
Jun 6 '06 #4
Thanks a lot Rich! The (-1) did the trick!

Paul
Rich P wrote:
One thing you could try is to break your expression apart and just get
one value at a time. If that expression works then add the next
expression to it.
=(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End

Date])),[Forecast Date])/7)*[Weekly Impact]*-1

try enclosing the -1 in parentheses. I think the code is interpreting
this

[weekly impact] * - 1

instead of

[weekly impact] * (-1)

Or

(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])
/7)

you are getting a difference of days and dividing that by 7. Then you
are multiplying this value by a value contained in [weekly impact]

*[Weekly Impact]

then it appears you want the total value to be negative. If you enclose
(-1), then this is what the computer would interpret.

*
(-1)

Try breaking each expression apart to see if you get the desired value.
Start with the difference of days. If you get the correct result then
try adding the divide value by 7 part. If that gives a correct result
then add the * [weekly impact]...
Rich

*** Sent via Developersdex http://www.developersdex.com ***


Jun 6 '06 #5
Thanks a lot Rich! The (-1) did the trick!

Paul
Rich P wrote:
One thing you could try is to break your expression apart and just get
one value at a time. If that expression works then add the next
expression to it.
=(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End

Date])),[Forecast Date])/7)*[Weekly Impact]*-1

try enclosing the -1 in parentheses. I think the code is interpreting
this

[weekly impact] * - 1

instead of

[weekly impact] * (-1)

Or

(DateDiff("d",(IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])
/7)

you are getting a difference of days and dividing that by 7. Then you
are multiplying this value by a value contained in [weekly impact]

*[Weekly Impact]

then it appears you want the total value to be negative. If you enclose
(-1), then this is what the computer would interpret.

*
(-1)

Try breaking each expression apart to see if you get the desired value.
Start with the difference of days. If you get the correct result then
try adding the divide value by 7 part. If that gives a correct result
then add the * [weekly impact]...
Rich

*** Sent via Developersdex http://www.developersdex.com ***


Jun 6 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Allen Browne | last post by:
Subform is based on a single-table query that contains a calculated field: Amount: Round(CCur(Nz(*,0)),2) Continuous subform displays this field in a text box named Amount. As user enters new...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
2
by: hhathome | last post by:
In my report I have a calculated DateDiff Field, I also have a calculated count field. I'm trying to get a total of the DateDiff field and dividing it by the count field and I'm having problems --...
1
by: tconway | last post by:
I have an Access program that displays Customer data into a form and OrderID data into a subform. The totals in the Subform are based on calculated fields, i.e. the Total Amount field Calculates...
2
by: Ryker | last post by:
I have a Purchase Order where I have a calculated field called Price that is calculated by multiplying Qty * Sales Price. I have 10 of these fields...Price 1 - Price 10 (for each line of the PO). ...
2
by: ey.markov | last post by:
Greetings, in A2K VBA, I set the following recordset: Set rsGPValue = dbs.OpenRecordset("SELECT *, DateSerial(Year(),Month()+4,0) FROM tblGPValue AS OurDate, dbOpenSnapshot) and then I try...
5
by: Henrik | last post by:
The problem is (using MS Access 2003) I am unable to retrieve long strings (255 chars) from calculated fields through a recordset. The data takes the trip in three phases: 1. A custom public...
2
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by...
9
by: Haas C | last post by:
Hi all! Is there anyway I can override a value in a calculated field on a form? For example: I have a form which displays the following fields based on a query: Premium Due field has the...
6
by: Steve67 | last post by:
Wonder if I can get someone’s help with a problem I am having on where to place an IF statement in VBE. I have created a form in Access 2003 that has an automatic calculated field. Basically you...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.