By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,786 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,786 IT Pros & Developers. It's quick & easy.

Access Program Problem

P: 5
I am not an expert but I am trying to develope an access program and have hit the following snag:

Using the queries in one of the fields there is an inserted amount say 3, now if that amount exceeds 3 I want to either * by a different number or add an additional amount. How do I do this and how is it written?

I have tried using the IIf and > but then I get stuck as there is no THEN or ELSE in the program.

Hope someone can help

Regards

John
Sep 17 '06 #1
Share this Question
Share on Google+
7 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi John,

Please give us the SQL of this that you have done... This you can do it by clicking on SQL View of your query...

And give us an exemple with your data so we can walk in your shoes...

:)
Sep 17 '06 #2

PEB
Expert 100+
P: 1,418
PEB
And

IIF has then and has else that is obligatory!


The syntax is:

IIF(Condition, Then value, ELSE VALUE)

:)
Sep 17 '06 #3

P: 5
Hi John,

Please give us the SQL of this that you have done... This you can do it by clicking on SQL View of your query...

And give us an exemple with your data so we can walk in your shoes...

:)
SQL View.
SELECT[NoGlassFrames+SashGlass].GlassHtg
From [NoGlassFrames+SashGlass];

So if glass Htg exceeds 0.8 then another factor has to be applied as a olus or as a multiplier.
I surely hope this is clear as I mentioned I am no expert
Sep 17 '06 #4

PEB
Expert 100+
P: 1,418
PEB
So your query can change like this:

SELECT IIF([NoGlassFrames+SashGlass].GlassHtg>0.8,[NoGlassFrames+SashGlass].GlassHtg*0.25, [NoGlassFrames+SashGlass].GlassHtg) AS newGlassHtg
From [NoGlassFrames+SashGlass];

So 0.25 is your multiplier

Is this helpfull?

:)

SQL View.
SELECT[NoGlassFrames+SashGlass].GlassHtg
From [NoGlassFrames+SashGlass];

So if glass Htg exceeds 0.8 then another factor has to be applied as a olus or as a multiplier.
I surely hope this is clear as I mentioned I am no expert
Sep 17 '06 #5

P: 5
So your query can change like this:

SELECT IIF([NoGlassFrames+SashGlass].GlassHtg>0.8,[NoGlassFrames+SashGlass].GlassHtg*0.25, [NoGlassFrames+SashGlass].GlassHtg) AS newGlassHtg
From [NoGlassFrames+SashGlass];

So 0.25 is your multiplier

Is this helpfull?

:)
I used the Build function and entered exactly as above and the run result was as follows.
"The Syntax of the Subquery in the expression is incorrect. Check the subquery's syntax & enclose in ()"
Sep 17 '06 #6

PEB
Expert 100+
P: 1,418
PEB
In design View you need to type only:

IIF([NoGlassFrames+SashGlass].GlassHtg>0.8, [NoGlassFrames+SashGlass].GlassHtg*2, [NoGlassFrames+SashGlass].GlassHtg)

And in your regional settings what is the symbol for decimal character?

If this is the virgula, so your expression should be:
IIF([NoGlassFrames+SashGlass].GlassHtg>0,8; [NoGlassFrames+SashGlass].GlassHtg*2; [NoGlassFrames+SashGlass].GlassHtg)

Have a nice evenenig...

:)
Sep 17 '06 #7

P: 5
In design View you need to type only:

IIF([NoGlassFrames+SashGlass].GlassHtg>0.8, [NoGlassFrames+SashGlass].GlassHtg*2, [NoGlassFrames+SashGlass].GlassHtg)

And in your regional settings what is the symbol for decimal character?

If this is the virgula, so your expression should be:
IIF([NoGlassFrames+SashGlass].GlassHtg>0,8; [NoGlassFrames+SashGlass].GlassHtg*2; [NoGlassFrames+SashGlass].GlassHtg)

Have a nice evenenig...

:)
Thanks for all your help but it still won't work. I have reduced the problem to a very short program and query as follows.

SELECT Frames.FramesID, Frames.FrameHtg, Frames.FrameWth, Frames.FrameLgt, Frames.FrameCost

IIF([Frames]FrameHtg>0.8,[Frame].FrameHtg*2,[Frame]FrameHtg)
FROM Frames;

This is a paste from the actual insertion and it is returning Syntax error in query.

I am almost desperate so thanks again for your help.

PS I have'nt a clue what a Virgula is!!
Sep 17 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.