Get specific words/letters in a field and put in another field

[dhen21dx]

Hi

Can anyone know here how i will get some words or letter in the fields of the table and put it in another field.
Thanks
Here is the example:

tblfromemail:

Field Name: Subject
Ticket Number: Num '7003894' Implement Task. Due Date: '2017-10-16 15:40:42'.Changes are released

Result:
tblfromemail:

Field Name: Ticket Num
7003894
Field Name: Due Date
2017-10-16 15:40:42

 

[ranman256]

create a module , then paste this code into it.
run a query to get the data out of the field:
usage:
select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from table

'------------
function getTicket(pvFld)
'------------
dim i as integer
dim vWord

i = instr(pvfld,"'")
vWord = mid(pvFld,i+1)
i = instr(pvfld,"'")
getTicket = left(vWord,i-1)
end function


'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function

 

[dhen21dx]

Hi Ranman,

Thank you, but have some minor problem i think on Due Date because of the format of Date is 'yyyy-m-d hh:nn:ss', so some month and date vary. example is below:

'2017-8-1 05:16:08'
'2017-7-31 05:43:20'
'2017-10-11 03:39:21'

 

[Joe4]

What is the Data Type of the table field that you are trying to post this to?
How exactly are you trying to insert that value into that field?

 

[dhen21dx]

Hi Joe,

The data type is Date/Time field, i am inserting it using the function
'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function

 

[Joe4]

The data type is Date/Time field, i am inserting it using the function
'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function

That is just the function. That doesn't insert it into any table, it just returns a value.
You need to use it in something like an Update Query or VBA code to actually insert it into a table field.

 

[dhen21dx]

I use this

select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from tblfromemail

 

[Joe4]

I use this

select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from tblfromemail

That is just a select query returning calculating fields.
Once again, that is not inserting any values into any table fields. It is just returning calculations.
You would need to use an Update Query to update field values.

See: http://www.databasedev.co.uk/update_query.html