expression in default value

[alexlds]

Hi Everyone

Is it possible to use something like this in the default value for a field [rank] in a table

= if([firstname] ="john", 8,9)

ie when [firstname] for a new record is entered as john, the default field value for [rank] in the new record is set to 8 otherwise it is 9.

Cant seem to get it to work - do I need to be using iif - or - me.[firstname] or me![firstname]

Im hoping it is just a syntax problem . . . .

 

[AliyuKatsina]

Hi Alexlds,
Normally you don't input data directly to a table. You do that through a user form.
You should create a form to input data to your table,then create a textbox control to input firstname. create an afterupdate proceedure for the control to check if the entered name is 'John' then make the rank field '8' otherwise '9'.
Try it.

 

[Pat Hartman]

You could do this in the default property of a control on a form but the default values and validation rules are severely limited in tables. Table level stuff is handled by Jet/ACE rather than Access/VBA. That means that you can only use functions that are supported by SQL. You cannot use VBA "IIf()" or user defined "MyFunc()" functions.

 

[raskew]

Hi -

You need to be using an immediate if (iif). Here's an example from the debug (immediate) window:

x = "john"
? iif(x ="john", 8,9)
 8 
x = "joe"
? iif(x ="john", 8,9)
 9

HTH - Bob

 

[Pat Hartman]

Bob, Did you test this as the default value of a column in a table? I don't beleive it will work there. You can't reference other columns.