Field error Question?

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Hi,

I use this
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
)" in a query field,
but it still show #Error, hope someone can help!

Thank!!

Gary
 
Hi,


The syntax does not seem to be right. It seems there is an odd number of ",
it should be even. The number of parenthesis is wrong. If I make a running
count +1 for ( and -1 for ), I got:


Iif(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:

Iif(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment to get
more argument).


What you WANT to do is not clear... cannot be much of further assistance.
Please, try to tell what you WANT to do, rather than just HOW you do it.



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to fix the
#Error
But it not work.
Thank for help again!!

Gary

Michel Walsh said:
Hi,


The syntax does not seem to be right. It seems there is an odd number of ",
it should be even. The number of parenthesis is wrong. If I make a running
count +1 for ( and -1 for ), I got:


Iif(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:

Iif(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment to get
more argument).


What you WANT to do is not clear... cannot be much of further assistance.
Please, try to tell what you WANT to do, rather than just HOW you do it.



Hoping it may help,
Vanderghast, Access MVP


Gary said:
Hi,

I use this
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
)" in a query field,
but it still show #Error, hope someone can help!

Thank!!

Gary
Click to expand...
Click to expand...
 
Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



Gary said:
Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to fix the
#Error
But it not work.
Thank for help again!!

Gary

Michel Walsh said:
Hi,


The syntax does not seem to be right. It seems there is an odd number
Click to expand...
of
",
it should be even. The number of parenthesis is wrong. If I make a running
count +1 for ( and -1 for ), I got:


if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:

if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment to get
more argument).


What you WANT to do is not clear... cannot be much of further assistance.
Please, try to tell what you WANT to do, rather than just HOW you do it.



Hoping it may help,
Vanderghast, Access MVP


Gary said:
Hi,

I use this
Click to expand...
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Hi Michel,

Sorry! to give you wrong information, I only have field(time1)
so IIf(IsError(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))) is
correct.

Thank!

Gary

Michel Walsh said:
Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



Gary said:
Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to fix the
#Error
But it not work.
Thank for help again!!

Gary

Michel Walsh said:
Hi,


The syntax does not seem to be right. It seems there is an odd number
Click to expand...
of
",
it should be even. The number of parenthesis is wrong. If I make a running
count +1 for ( and -1 for ), I got:


if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:

if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment to get
more argument).


What you WANT to do is not clear... cannot be much of further assistance.
Please, try to tell what you WANT to do, rather than just HOW you do it.



Hoping it may help,
Vanderghast, Access MVP


"Gary" <abc> wrote in message
Hi,

I use this
Click to expand...
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Hi,


You can also try:

iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )

which has the advantage to return a NULL, or a date, rather than either a
string, either a date.




Vanderghast, Access MVP



Gary said:
Hi Michel,

Sorry! to give you wrong information, I only have field(time1)
so IIf(IsError(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))) is
correct.

Thank!

Gary

Michel Walsh said:
Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



"Gary" <g> wrote in message news:[email protected]...
Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to
Click to expand...
fix
the
#Error
But it not work.
Thank for help again!!

Gary

Hi,


The syntax does not seem to be right. It seems there is an odd
Click to expand...
number
of
",
it should be even. The number of parenthesis is wrong. If I make a running
count +1 for ( and -1 for ), I got:
Click to expand...
if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:
Click to expand...
if(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment
Click to expand...
to
get
more argument).


What you WANT to do is not clear... cannot be much of further assistance.
Please, try to tell what you WANT to do, rather than just HOW you do it.



Hoping it may help,
Vanderghast, Access MVP


"Gary" <abc> wrote in message
Hi,

I use this
Click to expand...
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Hi Michel,

I change iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )
to iif( time1="", NULL, CDate(Mid( time1, 7, 5)) )
then it ok.

Thank so much!!


Michel Walsh said:
Hi,


You can also try:

iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )

which has the advantage to return a NULL, or a date, rather than either a
string, either a date.




Vanderghast, Access MVP



Gary said:
Hi Michel,

Sorry! to give you wrong information, I only have field(time1)
so IIf(IsError(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))) is
correct.

Thank!

Gary

Michel Walsh said:
Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



"Gary" <g> wrote in message Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and
change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to fix
the
#Error
But it not work.
Thank for help again!!

Gary

Hi,


The syntax does not seem to be right. It seems there is an odd number
of
",
it should be even. The number of parenthesis is wrong. If I make a
running
count +1 for ( and -1 for ), I got:



f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2 3
2 1 0 -1 -2

which means that your iif ends with:


f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma (which
delimits arguments, but there is no function opened at that moment to
get
more argument).


What you WANT to do is not clear... cannot be much of further
assistance.
Please, try to tell what you WANT to do, rather than just HOW you
Click to expand...
do
it.
Hoping it may help,
Vanderghast, Access MVP


"Gary" <abc> wrote in message
Hi,

I use this
Click to expand...
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Hi,


You can make it a little bit more robust with:


iif( 0=len(time1 & ""), NULL, CDate(Mid( time1, 7, 5) ) )


since it will handle both cases where time1 is Null and where time1 is a
zero-length string.



Hoping it may help,
Vanderghast, Access MVP


Gary said:
Hi Michel,

I change iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )
to iif( time1="", NULL, CDate(Mid( time1, 7, 5)) )
then it ok.

Thank so much!!


Michel Walsh said:
Hi,


You can also try:

iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )

which has the advantage to return a NULL, or a date, rather than either a
string, either a date.




Vanderghast, Access MVP



"Gary" <g> wrote in message news:[email protected]...
Hi Michel,

Sorry! to give you wrong information, I only have field(time1)
so IIf(IsError(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))) is
correct.

Thank!

Gary

Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



"Gary" <g> wrote in message Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of field(time1) and
change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5)))
Click to expand...
to
fix
the
#Error
But it not work.
Thank for help again!!

Gary

Hi,


The syntax does not seem to be right. It seems there is an odd number
of
",
it should be even. The number of parenthesis is wrong. If I make a
running
count +1 for ( and -1 for ), I got:



f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2
3
2 1 0 -1 -2

which means that your iif ends with:


f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )


and whatever comes after has to use an operator rather than coma
(which
delimits arguments, but there is no function opened at that
Click to expand...
moment
to
get
more argument).


What you WANT to do is not clear... cannot be much of further
assistance.
Please, try to tell what you WANT to do, rather than just HOW
Click to expand...
Click to expand...
you
do
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Thank!!!

Michel Walsh said:
Hi,


You can make it a little bit more robust with:


iif( 0=len(time1 & ""), NULL, CDate(Mid( time1, 7, 5) ) )


since it will handle both cases where time1 is Null and where time1 is a
zero-length string.



Hoping it may help,
Vanderghast, Access MVP


Gary said:
Hi Michel,

I change iif( time1 IS NULL, NULL, CDate(Mid( time1, 7, 5)) )
to iif( time1="", NULL, CDate(Mid( time1, 7, 5)) )
then it ok.

Thank so much!!
Click to expand...
either
a
string, either a date.




Vanderghast, Access MVP



"Gary" <g> wrote in message Hi Michel,

Sorry! to give you wrong information, I only have field(time1)
so IIf(IsError(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))) is
correct.

Thank!

Gary

Hi,





CDate( Mid( iif (time1 IS Null , time2, time1) , 7, 5 ))




should work. If time1 is null, we take time2, else, we take it (time1).


Hoping it may help,
Vanderghast, Access MVP



"Gary" <g> wrote in message
Hi Michel,

I use this "CDate(Mid([time1],7,5)" to get a part of
Click to expand...
field(time1)
and
change
it to a date format,
but sometime the record in field(time1) is null, so I tried to use
this
IIf(IsError(CDate(Mid([time2],7,5))),"",CDate(Mid([time2],7,5))) to
fix
the
#Error
But it not work.
Thank for help again!!

Gary

Hi,


The syntax does not seem to be right. It seems there is an odd
number
of
",
it should be even. The number of parenthesis is wrong. If I
Click to expand...
Click to expand...
make
a
running
count +1 for ( and -1 for ), I got:
Click to expand...
f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
,"","") ")
1 2 3 4 3 2 1 2
3
2 1 0 -1 -2

which means that your iif ends with:
Click to expand...
f(Iserror(CDate(Mid([time1],7,5) ) ),"",CDate(Mid([time1],7,5) ) )
and whatever comes after has to use an operator rather than coma
(which
delimits arguments, but there is no function opened at that moment
to
get
more argument).


What you WANT to do is not clear... cannot be much of further
assistance.
Please, try to tell what you WANT to do, rather than just HOW
Click to expand...
you
do
it.



Hoping it may help,
Vanderghast, Access MVP


"Gary" <abc> wrote in message
Hi,

I use this
Click to expand...
Click to expand...
"D:Iif(Iserror(CDate(Mid([time1],7,5))),"",CDate(Mid([time1],7,5))),"","")"
Click to expand...
 
Back
Top