Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Recordsets (Acess 2000)

    HELP!!
    I am still having trouble with this - I just can't get this to work!
    Dim recno, X As Integer
    Dim qry As QueryDef
    Dim strSQL As String
    Dim db As Database
    Dim rec As Recordset
    Dim Manager As String
    Set db = CurrentDb
    strSQL = "SELECT DISTINCTROW CostCentreReportStructure.CostCentre, CostCentreReportStructure.Name, CostCentreReportStructure.Report2 FROM CostCentreReportStructure WHERE (((CostCentreReportStructure.Report2) Like "Fred Bloggs"));"
    Set qry = db.CreateQueryDef("")
    qry.SQL = strSQL
    Set rec = qry.OpenRecordset()

    I get a 'Type Mismatch' error message


    Thanks

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

    Re: Open Recordsets (Acess 2000)

    You're using the string "Fred Bloggs" within another string strSQL. This causes confusion over the quotes. VBA probably ends strSQL at Like ". To avoid the confusion, either enclose the inner string in single quotes: 'Fred Bloggs', or double the double quotes: ""Fred Bloggs"".

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Recordsets (Acess 2000)

    The string "Fred Bloggs" is actually within another set of quotes - I just altered it to make it easier to read. The query works - it falls over at the last line

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

    Re: Open Recordsets (Acess 2000)

    Some suggestions:
    1. <LI>Try inserting a statement
      MsgBox strSQL
      before opening the recordset. See if there is anything unusual in the displayed string.
      <LI>You're using a WHERE expression with LIKE without using a wildcard. Try including a ? or * in the WHERE expression.
      <LI>Check the data type of the Report2 field.
      <LI>One of your fields is named Name. This is dangerous, because Name is also a property of most objects. If all else fails, rename the field in the table and query.
    Maybe one of these will help.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Recordsets (Acess 2000)

    I've now altered the SQL string to read
    strSQL = "SELECT CostCentreReportStructure.CostCentre FROM CostCentreReportStructure WHERE (((CostCentreReportStructure.Report2) Like """""Fred Bloggs"""""));"
    and am still getting the 'Type Mismatch' message. The create query bit works - if use it to create a query, that's OK - but then the query won't open in code - although it's fine when opened in Access
    Allison

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

    Re: Open Recordsets (Acess 2000)

    I think you've gone overboard with the quotes here. Try

    strSQL = "SELECT CostCentreReportStructure.CostCentre FROM CostCentreReportStructure WHERE (((CostCentreReportStructure.Report2) Like ""Fred Bloggs""));"

    or

    strSQL = "SELECT CostCentreReportStructure.CostCentre FROM CostCentreReportStructure WHERE (((CostCentreReportStructure.Report2) Like 'Fred Bloggs'));"

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Recordsets (Acess 2000)

    I'd recommend ditching all the double-quotes and superfluous qualifiers & parentheses and simplify SQL to one of the following (depending on whether you want Like or Equal to):

    <pre>strSQL = "SELECT CostCentre FROM CostCentreReportStructure " & _
    "WHERE Report2 Like 'Fred Bloggs*';"</pre>

    <pre>strSQL = "SELECT CostCentre FROM CostCentreReportStructure " & _
    "WHERE Report2 ='Fred Bloggs';"</pre>


    If you still get error, as previously recommended you need to look at data type conflict causing Type Mismatch error. Did not note anything wrong with syntax used to open recordset.

    HTH

  8. #8
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Recordsets (Acess 2000)

    Sorry - still getting the Type Mismatch error - the query created from my statement (i.e. if I type Set qry = db.CreateQueryDef("temp")) runs and gives me what I want - but I then can't get the recordset open - I want to append all the Cost Centres to an array.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Recordsets (Acess 2000)

    In further reply I think it's not your SQL after all (quotes or no quotes), it's your object variable declarations. Recommend explicitly declaring these as DAO Database, etc. Example:
    <pre>Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qry As DAO.QueryDef</pre>

    I did quick test with a similar sub, removed DAO reference, and got same error you got, type mismatch. Since ADO also has a Recordset object, apparently there is a conflict of interest if you don't explicitly declare which type of Recordset object your variable refers to.

    HTH

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Open Recordsets (Acess 2000)

    Why use CreateQueryDef, it could all be done by a recordset.
    eg.
    Dim recno, X As Integer
    Dim qry As QueryDef
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim Manager As String
    Set db = CurrentDb
    strSQL = "SELECT DISTINCTROW CostCentreReportStructure.CostCentre, CostCentreReportStructure.Name, CostCentreReportStructure.Report2
    FROM CostCentreReportStructure WHERE (((CostCentreReportStructure.Report2) Like 'Fred Bloggs*'));"
    Set rec = db.OpenRecordset(strSQL)

    What Mark says about the DAO is right on the money.
    HTH
    Pat

  11. #11
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Recordsets (Acess 2000)

    Hallelulah! - it works - thanks very much

  12. #12
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Recordsets (Acess 2000)

    That did it! - Thanks VERY much
    Allison

Posting Permissions

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