Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3 Jet

    I

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

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    FindGapsText will only work with text fields that contain numeric values as text. Text values will cause the code to fail. Do you have a particular reason for storing numeric values in a text field?

    Other remarks:
    - I recommend declaring DAO variables explicitly as DAO.Database, DAO.Recordset etc.
    - Instead of
    Set mydb = DBEngine.Workspaces(0).Databases(0)
    use
    Set mydb = CurrentDb

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    Hi Hans

    This was an inherited system design and this is what I

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

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    How about

    Set mytbl = mydb.OpenRecordset("qry_testfileText")

    The first argument to OpenRecordset can be:
    - the name of a table
    - the name of a query
    - an SQL string

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    Sorry Hans

    Forgot to mention my query looks like this, and giving me run-time error

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

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    DAO doesn't handle parameter queries of this type, or rather, you'd have to write special code. It's easier to assemble the correct SQL in code:

    Dim strStart As String
    Dim strEnd As String
    Dim strSQL As String

    strSQL = "SELECT custno FROM testfileText"

    strStart = InputBox("Enter start number, leave blank for none")
    strEnd = InputBox("Enter end number, leave blank for none")

    If strStart = "" And strEnd = "" Then
    ' No condition
    ElseIf strStart = "" Then
    ' Only strEnd filled in
    strSQL = strSQL & " WHERE custno <= " & strEnd
    ElseIf strEnd = "" Then
    ' Only strStart filled in
    strSQL = strSQL & " WHERE custno >= " & strStart
    Else
    ' Both filled in
    strSQL = strSQL & " WHERE custno Between " & strStart & " And " & strEnd
    End If

    Set mytbl = mydb.OpenRecordset(strSQL)

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    Hans, Thanks for your help

    Point of interest for others following this thread

    I had to use the Chr(34) wrappers in my example

    John

    <pre> If strStart = "" And strEnd = "" Then
    ' No condition
    ElseIf strStart = "" Then
    ' Only strEnd filled in
    strSQL = strSQL & " WHERE custno <= " & Chr(34) & strEnd & Chr(34)
    ElseIf strEnd = "" Then
    ' Only strStart filled in
    strSQL = strSQL & " WHERE custno >= " & Chr(34) & strStart & Chr(34)
    Else
    ' Both filled in
    strSQL = strSQL & " WHERE custno Between " & Chr(34) & strStart & Chr(34) & _
    " And " & Chr(34) & strEnd & Chr(34)
    End If
    </pre>


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

    Re: Proper to Change GapFinder From Numeric to Text? (a2k (9.0.6926) SP-3

    Thanks for pointing that out; I should have realized that the Chr(34)s were needed for a text field.

Posting Permissions

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