1 Replies - 3966 Views - Last Post: 24 April 2012 - 12:11 PM Rate Topic: -----

#1 AN1554   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 130
  • Joined: 03-April 11

InStr function in SQL statement

Posted 24 April 2012 - 10:35 AM

I want to open a table which logs user actions. LogDate, LogTime and Username are single-word fields and Action is, for e.g., "Add customer with ID 1", "Edit product with ID 4", "Backup database to E:\". I want to be able to know if the Action field contains "Backup" so as to produce a backup report.

rsLog.Open "SELECT LogDate, LogTime, Username, Action FROM tblLog WHERE '" & InStr(Action, "Add") <> 0 & "'", Cnn


Obviously, WHERE '" & InStr(Action, "Add") <> 0 & "'" does not work. How do I proceed with this?

I am thinking of storing categorical data in the field Action, e.g. "Add customer", "Edit product", "Backup database", then add a field Detail to store details of the action, e.g. "with ID 1", "with ID 4", "to E:\". I would still like to know how to proceed with the above SQL statement, though.

Is This A Good Question/Topic? 0
  • +

Replies To: InStr function in SQL statement

#2 BobRodes   User is offline

  • Product Manager
  • member icon

Reputation: 601
  • View blog
  • Posts: 3,072
  • Joined: 19-May 09

Re: InStr function in SQL statement

Posted 24 April 2012 - 12:11 PM

View PostAN1554, on 24 April 2012 - 06:35 PM, said:

How do I proceed with this?


You start reading up on T-SQL, assuming your back end database is SQL Server. If not, you read up on whatever flavor of SQL you are using.

Look, AN, this is the second question you've asked lately that suggests that you think SQL ought to recognize VB syntax. It doesn't, obviously enough when I point it out. I would suggest that you begin experimenting with working out your queries directly in SQL management console (or your back end DB's equivalent), and then plugging them into VB.

Now, here:
SELECT LogDate, LogTime, Username, Action FROM tblLog WHERE Action LIKE '%Add%'
That SQL code will search for every member of field Action that contains the string Add.

And quit asking SQL to learn VB. Might as well ask the sun to quit shining. :)

This post has been edited by BobRodes: 24 April 2012 - 12:13 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1