Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: 2007: QueryDef textboxes runquery

    Hi All – removed previous post on this……I went through the Northwind example to get a better feel for the differences in the sample and my database.
    I am the one with some experience with querydef, but it was using a list box…I am now attempting to write query def for 8 textboxes from a form with recordsource (query) sfPP
    1.) I realized I have a 2000 example, and I am using 2007; but with my DAO reference intact I’m hoping it will take
    2.) I had trouble determining in the where clauses what goes first – the form field name or the query name? below is query first, then form field after Me!
    3.) My Select needs to call to a query not a table. I get this string from the example – I don’t need the Mid finction or the Msg box and I don’t know how to change the bracketing - select from query sfPP where
    ' Remove the following MsgBox line if you do not want to display the
    ' SQL statement.
    ' NOTE: The Mid function is used in the following MsgBox function to
    ' remove the word AND that follows the first Where clause. If you do
    ' not use the Mid function, the SQL statement contains the word AND
    ' at the beginning of the WHERE clause, for example:
    '
    ' Select * from Orders where AND [CustomerID] = 'CACTU'

    MsgBox "Select * from Orders " & (" where " + Mid(where, 6) & ";")
    Set QD = db.CreateQueryDef("Dynamic_Query", _
    "Select * from orders " & (" where " + Mid(where, 6) & ";"))
    DoCmd.OpenQuery "Dynamic_Query"
    End Sub
    4.) I need to change from the ship city example the text box with wildcard – my qry field NENO records all begin with 340 and some of them can have a -00 (or other 2 digits after the dash) at the end…..it is text. I want to not have to type in the 340 or the characters after the dash…so that is 3 left and 3 right to ignore..so for the 34011920 series (34011920, 34011920-01, 34011920-02 etc, etc) my textbox txtNENO will have 11920 in it.
    ' The following section evaluates the ShipCity criteria you enter.
    ' If the first or last character of the criteria is the wildcard
    ' character (*), then the function uses the "LIKE" operator in the
    ' SQL statement instead of "=". Also note the single quotation
    ' marks surrounding the text field [ShipCity].
    If Left(Me![Ship City],1) = "*" Or Right(Me![Ship City],1) = "*" Then
    where = where & " AND [ShipCity] like '" + Me![Ship City] + "'"
    Else
    where = where & " AND [ShipCity] = '" + Me![Ship City] + "'"
    End If

    Here’s what I built so far……….

    Private Sub cmdsfPP_Click()
    Dim db As DAO.Database
    Dim QD As QueryDef
    Dim where As Variant

    Set db = CurrentDb()

    On Error Resume Next
    db.QueryDefs.Delete ("Customers_History")
    On Error GoTo 0


    where = Null
    where = where & " AND [CUSTPO] = '" + Me![txtCUSTPO] + "'"
    where = where & " AND [FRT] = " + Me![txtFrt]
    where = where & " AND [TAX] = " + Me![txtTax]
    If Left(Me![NENO], 3) = "*" Or Right(Me![NENO], 3) = "*" Then
    where = where & " AND [NENO] like '" + Me![txtNENO] + "'"
    Else
    where = where & " AND [NENO] = '" + Me![txtNENO] + "'"
    End If
    If Not IsNull(Me![txtInvStart]) Then
    where = where & " AND [INVDATE] between #" + Me![txtInvStart] + "# AND #" & Me![txtInvoiceEnd] & "#"
    Else
    where = where & " AND [INVDATE] >+ #" + Me![txtInvStart] + " #"
    End If
    If Not IsNull(Me![txtPaidStart]) Then
    where = where & " AND [LastofPDATE] between #" + Me![txtPaidStart] + "# AND #" & Me![txtPaidEnd] & "#"
    Else
    where = where & " AND [LastofPDATE] >+ #" + Me![txtPaidStart] + " #"
    End If

    Set QD = db.CreateQueryDef("Customers_History", "Select * from ("sfPP") " & (" where ")& ";"))
    DoCmd.OpenQuery "Customers_History"

    End Sub
    Last edited by Foskbou; 09-13-12 at 12:14.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not clear on all the questions, but we'll dive in:

    1) DAO works fine in 2007
    2) The convention is field then form, so: WHERE FieldName = FormReference

    In Access "+" is normally not used as a concatenation operator; use "&" instead. In limited situations you can use "+"; it propagates Nulls whereas "&" does not. If you're selecting from a query, just use the query name instead of a table name. Don't concatenate it in, which you've done incorrectly anyway.
    Paul

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Thanks!

    Made change to this -
    Set QD = db.CreateQueryDef("Customers_History", "Select * from sfPP " & (" where " & Mid(where, 8) & ";"))

    Should I remove from all the code "+" and put in "&" ?

    I'm now getting a run-time error 13 type mismatch on
    where = where & " AND [INVDATE] between #" + Me![txtInvStart] + "# AND #" & Me![txtInvoiceEnd] & "#"

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can leave the "+", but be aware that it will propagate Nulls.

    You get an error on that line, or in the SQL if that line gets executed? This is an invaluable debugging method:

    Debugging

    What is the data type of INVDATE in the table?
    Paul

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    the error is on the line - it stops cold with a debug

    INVDATE is Date/Time

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I see nothing wrong, other than the "+" which it may be misinterpreting. When it's in debug, hover over the form controls and see what they contain. You might also consider declaring that variable as String and deleting the line setting it to Null.
    Paul

  7. #7
    Join Date
    Jun 2010
    Posts
    186
    I'm still playing around with this.......feel like I am close :-)

  8. #8
    Join Date
    Jun 2010
    Posts
    186
    Your suggestion-"You might also consider declaring that variable as String and deleting the line setting it to Null" - I'm not completely sure but I think I would add
    Dim strSQL As String
    and
    change both of my date lines like:

    strSQL = "SELECT * from sfPP " & "WHERE [INVDATE] between #" + Me![txtInvStart] + "# AND #" & Me![txtInvoiceEnd] & "#"
    Else
    strSQL = "SELECT * from sfPP " & "WHERE [INVDATE] >+ #" + Me![txtInvStart] + " #"
    End If
    If Not IsNull(Me![txtPaidStart]) Then
    strSQL = "SELECT * from sfPP " & "WHERE [LastofPDATE] between #" + Me![txtPaidStart] + "# AND #" & Me![txtPaidEnd] & "#"
    Else
    STRsql = "SELECT * from sfPP " & "WHERE [LastofPDATE] >+ #" + Me![txtPaidStart] + " #"
    End If

    then
    remove:where = Null

    I've put together a visual reference showing the error and debugs, attached

    This troubleshoot has been my most difficult, and the first thing that catches my attention is inconsistencies with the error message punctuations, the code punctuations and the debug punctuations
    Not sure what to do with it...but it seems that's a problem in the way it is written?

    The next thing is the QD = Nothing, and I'm not finding a missing loop there, or know of another way to reference it
    Attached Files Attached Files

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Foskbou View Post
    Your suggestion-"You might also consider declaring that variable as String and deleting the line setting it to Null" - I'm not completely sure but I think I would add
    Dim strSQL As String
    [B][I]and
    change both of my date lines like
    Different way of accomplishing the same goal. I was working within what you already had. The first couple of errors would seem to imply that the trailing ' is getting trimmed off somehow. Also, why is the Mid() function using 8? I suspect it should be 5, as you only have " AND " at the beginning, and you can tell from the error that the first characters are getting stripped off.

    What is the data type of tblCust31212_ACCT? I think I've found that >= works better than =>. I don't suppose you can post the db here?
    Paul

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    several questions/remarks:

    1. Why do you declare where as a Variant while you obviously intend to use it as a String (the SQL property of a DAO.Querydef object is a String)?
    Code:
    Dim where As Variant
    2. Why do you initialize it to Null?
    Code:
    where = Null
    3. Why do you mix the concatenation operator (&) with the addition/concatenation operator (+) in the same expression?
    Code:
    where = where & " AND [tblCust31212_ACCT] = " + Me![txtCustID]
    4. Why do you include an initial AND operator when building the WHERE part of the SQL statement?
    Code:
    where = Null
    where = where & " AND [tblCust31212_ACCT] = " + Me![txtCustID]
    5. What's the Mid function supposed to do to the WHERE expression and why do you use an extra set of parentheses when building it?
    Note: There also seems to be an unmatched closing parenthese in the expression.
    Code:
    "Select * from sfPP " & (" where " & Mid(where, 8) & ";"))
    6. What does the where Variant/String variable contain when it's used to create the querydef?
    Note: You can use:
    Code:
    Debug.print  "Select * from sfPP " & (" where " & Mid(where, 8) & ";"))
    Stop
    and see what's in the Debug (immediate) window when the execution of the code stops.

    More over this:

    The where variable, as it is built, begins with: " AND [tblCust31212_ACCT]=341369"

    With the expression:
    Code:
    Mid(where, 8)
    it becomes: "blCust31212_ACCT]=341369"
    Last edited by Sinndho; 09-14-12 at 18:03. Reason: Problem with the [I]Mid[/I] expression identified.
    Have a nice day!

  11. #11
    Join Date
    Jun 2010
    Posts
    186
    I've made some progress with changes I made from your suggestions Sinndho and pbaldy! The query ran on the first textbox - data text - NENO
    It loaded this SQL from my code:
    SELECT *
    FROM sfPP
    WHERE [tblCust31212_ACCT] = '341369' AND [NENO] = '99170';

    The query was blank because there is no [NENO] that reads 99170 exactly, the records I need are 34099170-11, 34099170-12, 34099170-13 etc and I don't think my where is written correctly to strip left and right?
    If Left(Me![NENO], 3) = "*" Or Right(Me![NENO], 3) = "*" Then
    where = where & " AND [NENO] like '" + Me![txtNENO] & "'"
    Else
    where = where & " AND [NENO] = '" + Me![txtNENO] & "'"
    End If





    Private Sub cmdsfPP_Click()
    Dim db As DAO.Database
    Dim QD As QueryDef
    Dim where As Variant
    Dim strSQL As String

    Set db = CurrentDb()

    On Error Resume Next
    db.QueryDefs.Delete ("Customers_History")
    On Error GoTo 0


    where = where & " AND [tblCust31212_ACCT] = '" + Me![txtCustID] + "'"
    where = where & " AND [CUSTPO] = '" + Me![txtCUSTPO] + "'"
    where = where & " AND [FRT] = " + Me![txtFrt]
    where = where & " AND [TAX] = " + Me![txtTax]
    If Left(Me![NENO], 3) = "*" Or Right(Me![NENO], 3) = "*" Then
    where = where & " AND [NENO] like '" + Me![txtNENO] & "'"
    Else
    where = where & " AND [NENO] = '" + Me![txtNENO] & "'"
    End If
    If Not IsNull(Me![txtInvStart]) Then
    strSQL = "SELECT * from sfPP " & "WHERE [INVDATE] between #" + Me![txtInvStart] + "# AND #" & Me![txtInvoiceEnd] & "#"
    Else
    strSQL = "SELECT * from sfPP " & "WHERE [INVDATE] >+ #" + Me![txtInvStart] + " #"
    End If
    If Not IsNull(Me![txtPaidStart]) Then
    strSQL = "SELECT * from sfPP " & "WHERE [LastofPDATE] between #" + Me![txtPaidStart] + "# AND #" & Me![txtPaidEnd] & "#"
    Else
    strSQL = "SELECT * from sfPP " & "WHERE [LastofPDATE] >+ #" + Me![txtPaidStart] + " #"
    End If

    Set QD = db.CreateQueryDef("Customers_History", "Select * from sfPP " & (" where " & Mid(where, 5) & ";"))
    DoCmd.OpenQuery "Customers_History"

    End Sub

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You want to end up with:

    WHERE [tblCust31212_ACCT] = '341369' AND [NENO] Like '*99170*'

    See if you can make the necessary changes.
    Paul

  13. #13
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by Sinndho View Post
    several questions/remarks:

    1. Why do you declare where as a Variant while you obviously intend to use it as a String (the SQL property of a DAO.Querydef object is a String)?
    Code:
    Dim where As Variant
    I tested from an example......figured I would pick it apart from there

    2. Why do you initialize it to Null?
    Code:
    where = Null

    3. Why do you mix the concatenation operator (&) with the addition/concatenation operator (+) in the same expression?
    Code:
    where = where & " AND [tblCust31212_ACCT] = " + Me![txtCustID]
    4. Why do you include an initial AND operator when building the WHERE part of the SQL statement?
    Code:
    where = Null
    where = where & " AND [tblCust31212_ACCT] = " + Me![txtCustID]
    5. What's the Mid function supposed to do to the WHERE expression and why do you use an extra set of parentheses when building it?
    Note: There also seems to be an unmatched closing parenthese in the expression.
    Code:
    "Select * from sfPP " & (" where " & Mid(where, 8) & ";"))
    This is also from the example - the Mid is incorrect at 8 = it only needs to be 5 and it take the _AND_ out

    6. What does the where Variant/String variable contain when it's used to create the querydef?
    Note: You can use:
    Code:
    Debug.print  "Select * from sfPP " & (" where " & Mid(where, 8) & ";"))
    Stop
    and see what's in the Debug (immediate) window when the execution of the code stops.

    More over this:

    The where variable, as it is built, begins with: " AND [tblCust31212_ACCT]=341369"

    With the expression:
    Code:
    Mid(where, 8)
    it becomes: "blCust31212_ACCT]=341369"
    I've had many syntax errors working with the Set QD because I am a novice.........If I take those parentheses out I get Compile Error: Expected: List Separator or )

Posting Permissions

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