Un concatenate? Extrapolate data from description field 1

[supportsvc]

I got great help to do this in SQL, unfortunately I can't save it as a View to link to and was hoping someone knows how to modify this to work in Access query?

select LogDate, LogTime, CompanyCode, UserLogon, ModuleCode, logdescription
from SY_ActivityLog
Where LogDate >= '4/18/2018' and ModuleCode = 'S/O' and LogDescription like 'Unable to reset Qty to Ship%'
select LogDate, LogTime, CompanyCode, ModuleCode, logdescription
      ,replace(substring(logdescription
                        ,charindex('Item ',logdescription)+5
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Item ',logdescription)+5
                                  )
                        -charindex('Item ',logdescription)-5
                        )
              ,','
              ,''
              )
       as item
      ,replace(substring(logdescription
                        ,charindex('Invoice ',logdescription)+8
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Invoice ',logdescription)+8
                                  )
                        -charindex('Invoice ',logdescription)-8
                        )
              ,','
              ,''
              )
       as invoice
      ,replace(substring(logdescription
                        ,charindex('Sales Order ',logdescription)+12
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Sales Order ',logdescription)+12
                                  )
                        -charindex('Sales Order ',logdescription)-12
                        )
              ,','
              ,''
              )
       as salesorder
  from sy_activitylog
  Where LogDate >= '5/8/2018' and ModuleCode = 'S/O' and LogDescription like 'Unable to reset Qty to Ship%'
;

 

[dhookom]

If the data is on a server, you can use a pass-through query which is more efficient and uses the same syntax as the server.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[supportsvc]

Oh WOW! Thank you!
That worked after removing the 1st section of the SELECT