syntax error in statment

[Ran BD]

=iif(isnull([warranty_period]),null,dateadd("m",[warranty_period],[purchesed
_date]))

can anyone tell me where is the syntax error in the statement above ?

 

[Allen Browne]

Looks okay, assuming:
- warranty_period is a field of type Number, and
- purchesed_date is a field of type Date/Time, and
- both fields are available (once only) in the RecordSource of your form or
report, and
- the expression is in the Control Source of a text box.

If this is a report, you could try putting warranty_period and
purchesed_date onto the report as well (setting their Visible property to
No).

If it's still broken, press Ctrl+G to open the Immediate Window.
Then choose References from the Tools menu, and see if anything is marked
"MISSING".

 

[Ran BD]

Warrenty_Period is type of int
purchesed_date is type of date/time
both fields are available in the table,

the expression is in the Control Source of a text box.

yet I'm getting a syntax error, why ?

 

[Allen Browne]

What setting? i.e. on a form? In the Detail section?
Only under certain conditions, e.g. when no records?

 

[Ran BD]

while putting the statement in the control source of the unbound text box
doesn't matter if there are/aren't any records.

thanks

 

[Ran BD]

and i'm using access 2002 with sql 2000 in the back end

 

[Allen Browne]

Sorry: I don't think I can help with this one.

If the error occurs while trying to enter the Control Source property of the
text box while the form is in design view, it normally means you have
mismatched brackets or invalid syntax, but that does not appear to be the
case. You did check the References?

The SQL Server back end should not present an issue in the context of a
Control Source that contains Access functions.

 

[Ran BD]

I know strange, nothing is missing in the ref section.

 

[Van T. Dinh]

Just in case: I know you mentioned that [Warranty_Period]
and [Purchesed_Date] (Purchased???) are Fields in the
Table but are they included in the RecordSource Query of
the Form?

Actually, my bet is still with Refernce Errors. Try a few
more tips from Douglas J. Steele:

<http://members.rogers.com/douglas.j.steele/AccessReference
Errors.html>

HTH
Van T. Dinh
MVP (Access)

 

[Ran BD]

just to verify theses references are shown:
VBA - english/standard
Access 10.0 object library - standard
Active X 2.1 object library - standard
Outlook view control - standard
Calendar Control 10.0 - standard
forms 2.0 object library - standard
fpdtc 1.0 type library - standard
all the rest are set to standard but are not selected.

further more a simple dateadd function works in the specific control source.
is there any other method you guys can think about to eliminate the error
msg, while the variable=null ?

thanks

 

[Ran BD]

ok I made some progress I can post the condition in the control box, and it
displays #name? as the value at all times.
I really don't understand what's going on.

any idea MR. Wizard ?

Just in case: I know you mentioned that [Warranty_Period]
and [Purchesed_Date] (Purchased???) are Fields in the
Table but are they included in the RecordSource Query of
the Form?

Actually, my bet is still with Refernce Errors. Try a few
more tips from Douglas J. Steele:

<http://members.rogers.com/douglas.j.steele/AccessReference
Errors.html>

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I know strange, nothing is missing in the ref section.

 

[Van T. Dinh]

Is it an MDB with ODBC-linked Tables or an ADP?

 

[Van T. Dinh]

Without your answer to my question, no ideas.

Perhaps, you should post the relevant Table details, the SQL String of the
Form_RecordSource and the latest expression you used.

Have you tried Douglas J. Steele's tip of unchecking a Reference, getting
out then getting back in the References and rechecking the unchecked
Reference?

 

[Kelvin]

Have you tried the IIF without the DateAdd to see if there is a problem with
the ISNULL statement. Something like
=iif(isnull([warranty_period]),null,"Horray")

Kelvin