Extract specific data from one cell

  • Thread starter ozgirl via AccessMonster.com
  • Start date
O

ozgirl via AccessMonster.com

Hi,

i have a csv file that i import into my database. in one of these excel
cells there is quite a lot of information and it imports ok into access.

what i need to be able to do though is have a query that extracts the date
and time only from all this information.
below is an example. of the cell. so what i need the query to show is after
Customer restore time: all i want to see is 03/112005 19:00:00

any help greatly appreciated

Lee-Anne

SLA Description:Medical Priority Workable SLA + Locality

Outage Ref:not defined
SIIAM Customer Restore Time:03/11/2005 19:00:00
NOTES:


*** PHONE LOG *** [27/10/2005 11:17:12 WATZ] d227467
CLI:0893984052
cus reporting socket faulty
 
M

MacDermott

AFAIK Access doesn't supply a built-in function which will do what you want,
but you can write your own function, if you don't mind dabbling a bit in
VBA.
The functions you'd want to look at might include InStr() and Mid().
 
B

Bob Miller

Try this field in a query
datr:
Mid([dsv],InStr([dsv],"Time:")+5,InStr([dsv],"Notes:")-InStr([dsv],"Time:")+5)
replacing dsv with your field name.
ozgirl said:
Hi,

i have a csv file that i import into my database. in one of these
excel
cells there is quite a lot of information and it imports ok into
access.

what i need to be able to do though is have a query that extracts the
date
and time only from all this information.
below is an example. of the cell. so what i need the query to show is
after
Customer restore time: all i want to see is 03/112005 19:00:00

any help greatly appreciated

Lee-Anne

SLA Description:Medical Priority Workable SLA + Locality

Outage Ref:not defined
SIIAM Customer Restore Time:03/11/2005 19:00:00
NOTES:


*** PHONE LOG *** [27/10/2005 11:17:12 WATZ] d227467
CLI:0893984052
cus reporting socket faulty
Click to expand...
 
O

ozgirl via AccessMonster.com

thanks everyone for your replys and helpful advice

i'm sure i can get this to work using mid and instr

Lee-Anne

Bob said:
Try this field in a query
datr:
Mid([dsv],InStr([dsv],"Time:")+5,InStr([dsv],"Notes:")-InStr([dsv],"Time:")+5)
replacing dsv with your field name.
ozgirl said:
Hi,
Click to expand...
[quoted text clipped - 23 lines]
CLI:0893984052
cus reporting socket faulty
Click to expand...
Click to expand...
 
Top