Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is in table (2003)

    I apologise if this is a bit basic but is there an SQL statement that returns true if the item sought is contained in a given field and else returns false?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is in table (2003)

    What kind of field? Text, number, yes/no, date?
    If it is a text field, are you looking for a match on the entire field, or on part of the field?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is in table (2003)

    Hans

    It is a text field. A match statement will be OK, depending on how it deals with the case of the item not being found. I tried the following but this fails if the ReqNo does not exist

    'strSQL = "Select 'Already exists' FROM OrderHeader WHERE '" & CurrentRequest.RequestNo & "' IN (ReqNo);"
    'Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    'Ws1.Cells(1, 1).CopyFromRecordset rs
    'If Ws1.Cells(1, 1).Value = "Already exists" Then
    ' dbs.Close
    ' Set dbs = Nothing
    ' MsgBox ("the req already exists in the DB" & vbCr & " No updates have been made")
    ' End
    'End If

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is in table (2003)

    Is RequestNo the text field?
    What is ReqNo?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is in table (2003)

    ReqNo stands for Request Number and is a text field

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is in table (2003)

    Text field? In what? A table? Or do you mean a text box on a form? Or ...?

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is in table (2003)

    It is a text field in a table. Every time a new record is added to the table I need to check whether the record is a brand new record, or an update of an existing record.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is in table (2003)

    I'm getting more and more confused. What is CurrentRequest.RequestNo and why is the name different from ReqNo?

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is in table (2003)

    Hans

    Good news, I have sorted the problem out. All the code was working correctly. I have various spreadsheets that put data into and gather data back from Access. I performed a compare and contrast activity with one of my other spreadsheets to trace the problem, which finally came don to:

    Each spreadsheet has references to both Microsoft DAO 3.6 Object Library and Microsoft ActiveX DataObjects 2.1 LIbrary
    When I changed the priority of these 2 references all the code worked.

    I have come across this problem a few times before and will be grateful for your advice on the following:
    Is there a set order in which these references need to appear. Past experience seems to indicate that there is not. However please let me know
    Are there later/better versions of these references which might be more compatible?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is in table (2003)

    If you fully qualify all DAO and ADO objects when you declare them, the order in which the references are listed don't matter. For example:

    Dim fldIndex As DAO.Field

    Dim rstTarget As ADODB.Recordset

    But if you don't quailify an object that occurs in both libraries, VBA assumes that it belongs to the highest one in the References list. So depending on your specific setup

    Dim rstTarget As Recordset

    can result in a DAO recordset or in an ADO recordset. This can cause problems.

    There is no later version of the DAO library
    If your database will be used by users with older versions of Access (2000, 2002), ADO 2.1 is OK. If you have only Access 2003 users, you could use ADO 2.8 instead. This is the latest version. Many Access 2000 and 2002 users will also have it if their computers are fully updated, but you cannot be sure of that.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is in table (2003)

    Thanks

    That seems to have sorted out a different problem I was having elsewhere as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •