Syntax Error Issue

  • Thread starter Thread starter Ran BD
  • Start date Start date
R

Ran BD

Hi Guys,
I have an issue I'm trying to solve for the last few days:
I have a form bound to a table within there are few fields:
Lease Period - type int.
Lease Period Start Date - Type Date
Lease period End Date is an unbound text field calculating the end date of
the lease period.
while entering dada into the form I'm getting #error in the lease period end
date after entering the value for field lease period I'm getting the correct
value as I'm using dateadd("m",[lease_period],[Lease_period_start_date])
I was trying to use and iff statement to eliminate the #error msg. the
statement I was using is:
=IIf(IsNull([Lease_period]),DateAdd("m",[Lease_period],[Lease_period_start_d
ate]))

which produces #error before there is a value entered in the lease_period
date and blank space after the value is entered.



what am I doing wrong ?



thanks



Ran.
 
How many threads do you want to create on the same
question?

There were at least 5 threads in the "forms" newsgroup and
you keep changing the flavours (Warranty_Period vs
Lease_Period, Purchase_Date vs Lease_Start_Date) with
spelling mistakes along the way which makes it very hard
to see where things go wrong.

With various threads like you have done, respondents don't
know what has been tried and basically repeat the same
suggestions.

Not only that, when respondents asked for more info., you
simply ignored questions and created new thread to repeat
the same process.

Please keep to the SAME thread and answer repondents'
questions for more info. Please see the Netiquette for
correct use of newsgroups

<http://www.mvps.org/access/netiquette.htm>


For the records:

1. The syntax in your latest attempt is wrong. IIf
function requires 3 arguments and you have only 2.

2. I tested in AXP and created a Table with 2 (relevant)
Fields:

LeaseDate DateTime
LeasePeriod Integer

Then created a Form bound to the above Table naming the
relevant TextBox Controls on the Form:

txtLeaseDate
txtLeasePeriod

(so that the Control names are *different* from the Field
names in the RecordSource of the Form).

In the 3rd (unbound) TextBox, I tried the following
ControlSources:

=IIf(IsNull([txtLeasePeriod]),Null,
DateAdd("m",[txtLeasePeriod],[txtLeaseDate]))

(using the Control names) and:

=IIf(IsNull([LeasePeriod]),Null,
DateAdd("m",[LeasePeriod],[LeaseDate]))

(using Field names)
including the equal sign in the ControlSource.

Both expressions worked without flaws, i.e. I didn't get
#error or #name during the data entry process.

3. It looks like expressions you posted previously were
correct (except for possible spelling mistakes) eand it is
likely that you have Reference Errors in your database.

Allen Browne & I have mentioned this possibility in one of
the previous threads and posted the link to Douglas J.
Steele's article on Reference Errors. I also advised to
try unchecking a Reference, OK'ing to get out, getting
back in and rechecking the reference as detailed by
Douglas J. Steele. Clearly, you didn't follow advices
offered. Then why do ask the question in the newsgroups
at the first place?

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi Guys,
I have an issue I'm trying to solve for the last few days:
I have a form bound to a table within there are few fields:
Lease Period - type int.
Lease Period Start Date - Type Date
Lease period End Date is an unbound text field calculating the end date of
the lease period.
while entering dada into the form I'm getting #error in the lease period end
date after entering the value for field lease period I'm getting the correct
value as I'm using dateadd("m",[lease_period], [Lease_period_start_date])
I was trying to use and iff statement to eliminate the #error msg. the
statement I was using is:
=IIf(IsNull([Lease_period]),DateAdd("m",[Lease_period], [Lease_period_start_d
ate]))

which produces #error before there is a value entered in the lease_period
date and blank space after the value is entered.



what am I doing wrong ?



thanks



Ran.




.
Click to expand...
 
Dear Mr. Van T Dinh

I didn't mean to cause such a big mess posting threads in different groups I
simply thought I'll be routed to a different support person.
as for the language barrier I do apologize for not being born as an American
therefore pardon my crappie English.

as for the link you attached, there was no web site behind.

to be specific, I'm new with access though I have enough experience with
visual basic and VC++ etc. and sometimes things that are bright as the high
noon sun to you are kind of cloudy for me.

if you could please elaborate again on the ref issue.

just to let you know I'm using access 2002 with SQL back end (2000) on a
server 2003 machine.

thanks for you patient.

Ran

Van T. Dinh said:
How many threads do you want to create on the same
question?

There were at least 5 threads in the "forms" newsgroup and
you keep changing the flavours (Warranty_Period vs
Lease_Period, Purchase_Date vs Lease_Start_Date) with
spelling mistakes along the way which makes it very hard
to see where things go wrong.

With various threads like you have done, respondents don't
know what has been tried and basically repeat the same
suggestions.

Not only that, when respondents asked for more info., you
simply ignored questions and created new thread to repeat
the same process.

Please keep to the SAME thread and answer repondents'
questions for more info. Please see the Netiquette for
correct use of newsgroups

<http://www.mvps.org/access/netiquette.htm>


For the records:

1. The syntax in your latest attempt is wrong. IIf
function requires 3 arguments and you have only 2.

2. I tested in AXP and created a Table with 2 (relevant)
Fields:

LeaseDate DateTime
LeasePeriod Integer

Then created a Form bound to the above Table naming the
relevant TextBox Controls on the Form:

txtLeaseDate
txtLeasePeriod

(so that the Control names are *different* from the Field
names in the RecordSource of the Form).

In the 3rd (unbound) TextBox, I tried the following
ControlSources:

=IIf(IsNull([txtLeasePeriod]),Null,
DateAdd("m",[txtLeasePeriod],[txtLeaseDate]))

(using the Control names) and:

=IIf(IsNull([LeasePeriod]),Null,
DateAdd("m",[LeasePeriod],[LeaseDate]))

(using Field names)
including the equal sign in the ControlSource.

Both expressions worked without flaws, i.e. I didn't get
#error or #name during the data entry process.

3. It looks like expressions you posted previously were
correct (except for possible spelling mistakes) eand it is
likely that you have Reference Errors in your database.

Allen Browne & I have mentioned this possibility in one of
the previous threads and posted the link to Douglas J.
Steele's article on Reference Errors. I also advised to
try unchecking a Reference, OK'ing to get out, getting
back in and rechecking the reference as detailed by
Douglas J. Steele. Clearly, you didn't follow advices
offered. Then why do ask the question in the newsgroups
at the first place?

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi Guys,
I have an issue I'm trying to solve for the last few days:
I have a form bound to a table within there are few fields:
Lease Period - type int.
Lease Period Start Date - Type Date
Lease period End Date is an unbound text field calculating the end date of
the lease period.
while entering dada into the form I'm getting #error in the lease period end
date after entering the value for field lease period I'm getting the correct
value as I'm using dateadd("m",[lease_period], [Lease_period_start_date])
I was trying to use and iff statement to eliminate the #error msg. the
statement I was using is:
=IIf(IsNull([Lease_period]),DateAdd("m",[Lease_period], [Lease_period_start_d
ate]))

which produces #error before there is a value entered in the lease_period
date and blank space after the value is entered.



what am I doing wrong ?



thanks



Ran.




.
Click to expand...
Click to expand...
 
Re-read Allen Brown's and my replies in one of the earlier threads. Follow
the link I posted to find Douglas J. Steele's doc. If the link is wrapped
in the posting, copy it into your browser as ONE line.

The Netiquette link works fine in my last post.
 
Back
Top