Help on If..Then.. Else Statement

[desikan]

I have a query with field [SL NO] having values from 1 to 30.

I wish to control this field using an unbound text box [Text1] in another form say FORM-A in such a way that if [Text1]="APPLE" then [SL NO] field gives out values less than 13. If [Text1]="MANGO" then [SL NO] field gives out values less than 25. For other values of [Text1], [SL NO] field outputs all values from 1 to 30.

I am trying to make a If..Then..else statement but as I am new to SQL I keep getting Operand/Syntax errors.

Any help on getting the correct statement will be appreciated.

Thanks in advance.

 

[oldduffer]

Your code is probably correct, access likes to grumble a lot about which line the code is on, the following should be fine, have as many ELSEIF's as you like, keep this line structure, let me know how it goes

dim maxvalue as int

IF [text1] = 'Apples' THEN
@maxvalue = 13
ELSEIF [text1] = 'Bananas' THEN
@maxvalue = 25
ELSE
@maxvalue = 30
ENDIF


now set whatever you like to maxvalue

 

[jebry]

Hi!

SQL in Access doesn't accept the If-Then-Else structure. You will need to use nested IIf statements instead or write a public function in a module that you can call from the SQL statement. The general syntax for an IIf statement is:

IIf(Condition, What to do if true, What to do if false)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[desikan]

Hi friends,

Thanks a lot for all the trouble taken to solve my query.

I will try the IIF condition and find whether it works for me.

Thanks once again.