displaying a only part of a field along with other fields of a recordset

hi all,


i have the following fields....


Document_Name, Document_No, Document_Date, Order_No, Order_Date,
Peripheral


The first 5 you can understand, peripheral is a memo field which
contains all less important (but important!) details like Address1,
Address2, Place_Of_Delivery in a comma delimited string such as:


Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;


I have already done something like this


select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable


then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17)


so i sql looks like


select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17) as Delivery_Place from mastertable


This method also works fortunately but there is only one huge
problem....


if place of delivery is not there at all.... and peripheral field is
blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
ERROR.......is there any inbuilt function in access that can do
this.....this is a night mare.......


thanks,
shripal dalal.

RE: displaying a only part of a field along with other fields of a rec

Is there any chance you can influence the designer of the DB to stop doing
that.

Give each piece of 'important' data it's own field.



"shripaldalal" wrote:

> hi all,
>
>
> i have the following fields....
>
>
> Document_Name, Document_No, Document_Date, Order_No, Order_Date,
> Peripheral
>
>
> The first 5 you can understand, peripheral is a memo field which
> contains all less important (but important!) details like Address1,
> Address2, Place_Of_Delivery in a comma delimited string such as:
>
>
> Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;
>
>
> I have already done something like this
>
>
> select document_no, document_date, order_no, order_date,
> getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable
>
>
> then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
> instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
> 'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
> 'Place_Of_Delivery') - 17)
>
>
> so i sql looks like
>
>
> select document_no, document_date, order_no, order_date,
> getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
> instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
> 'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
> 'Place_Of_Delivery') - 17) as Delivery_Place from mastertable
>
>
> This method also works fortunately but there is only one huge
> problem....
>
>
> if place of delivery is not there at all.... and peripheral field is
> blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
> ERROR.......is there any inbuilt function in access that can do
> this.....this is a night mare.......
>
>
> thanks,
> shripal dalal.
>
>

Re: displaying a only part of a field along with other fields of a recordset

shripaldalal wrote:
> if place of delivery is not there at all.... and peripheral field is
> blank.....

You are describing two separate situations here, I think. Instead of
describing the situation, could you show us a few rows of sample data that
illustrate the problem?

> then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
> ERROR.......is there any inbuilt function in access that can do
> this.....this is a night mare.......
>

You can use the iif function in your query to handle the case where
Peripheral contains Null:

...., iif(Peripheral IS Null, '', )

However, I would prefer to handle this field in my vbscript code. This task
is child's play using Split.

Actually, I would prefer to send this database back to its designer and tell
him to apply proper database design techniques to it.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: displaying a only part of a field along with other fields of a recordset

dear bob,

thanks a lot......it worked like a charm! thanks again!

best regards,
shripal dalal.

Bob Barrows [MVP] wrote:
> shripaldalal wrote:
> > if place of delivery is not there at all.... and peripheral field is
> > blank.....
>
> You are describing two separate situations here, I think. Instead of
> describing the situation, could you show us a few rows of sample data that
> illustrate the problem?
>
> > then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
> > ERROR.......is there any inbuilt function in access that can do
> > this.....this is a night mare.......
> >
>
> You can use the iif function in your query to handle the case where
> Peripheral contains Null:
>
> ..., iif(Peripheral IS Null, '', )
>
> However, I would prefer to handle this field in my vbscript code. This task
> is child's play using Split.
>
> Actually, I would prefer to send this database back to its designer and tell
> him to apply proper database design techniques to it.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"