Easy parameter question

[bibbyd01]

Hi

I have a simple question, but I hardly use access so don't know how to resolve it.

I have a column with an expression which adds three columns together to give me exposure. I then need a third column to compare it to credit, so that the query only displays records where the exposure is higher than the credit. How do I go about doing this?

I tried '=xposure>creditlimit' as a new expression but this doesn't work, so I'm now stuck!!

 

[DCrake]

Lets say col1 is the Exposed calculation, col2 = creditlimit

add col3 which will be Wanted:Iff(Exposed>CreditLimit,True,False)
Then in the criteria underneath col3 you would put True or False depending on what you want to see.

David

 

[namliam]

Or in col1 in the criteria simply add "> creditlimit" (without the quotes obviously)

 

[bibbyd01]

Ok, entered that, but it didn't seem to work. I trie using true or false, and 1 and 0, but neither worked. When I entered either true or 1 into the criteria, nothing comes up

I also ran it without the criteria, and it displayed the 1 and 0 correctly.

 

[DCrake]

Can you post a snippet from your mdb to test?

 

[bibbyd01]

erm, I guess so. How do I go about doing that?

 

[bibbyd01]

Just realised that whenever I go to run the query, it comes up with a box asking me to enter a paramet value for exposure, but only when I put criteria in the wanted column.

If I use the >creditlimit in the exposure field, then it just produces nothing in the query.

 

[DCrake]

Create a small mdb with a sample table and the queries you have attempted to make and any other objects that would help us to get to the bottom of this issue.

Then click on the paper clip icon to attach the mdb.

David

 

[bibbyd01]

Ok, stripped out most things and left some data, the query and the report I've created

 

[namliam]

Your problem with the new columsn is your cannot revert to a field that is beeing calculated in the same query...
Ie. adding a column which says Exposure > intCreditLimit will ask for the parameter Exposure. Becuase prior to this query running that column doesnt exist yet...
Well actually you can, but then you cannot put a limitation on it (limiting only to true...)


Solution
Change your >"intcreditlimit"
to >[intcreditlimit]

[] denotes it as a Fieldname.... "" denotes it as a string.

Or repeat your Exposure calc in the new column:
[intdebts]+[intopenorders]+[intwip] > [intCreditLimit]

Good luck!