Webmaster Forum / ASP / Database Access / January 2006

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.