Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Action query from VB

    I'm trying to run the following Action query:

    Dim max As Long, db As Database, qdf As QueryDef, strSQL As String

    max = DMax("Order_Number", "order_det_update")

    strSQL = "INSERT INTO order_det_update ( ORDER_NUMBER, ITEMNO, FirstOfITEM_DESC, 0, 2, 3, 4, 5, 6, 7, 8, 9 )"
    strSQL = strSQL & "SELECT [ord det conv].ORDER_NUMBER,.......(detail omitted)
    strSQL = strSQL & "FROM [ord det conv]"
    strSQL = strSQL & "WHERE ([ord det conv].ORDER_NUMBER > 'max')"
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = strSQL

    When I run this I get a data type mismatch error. I think the problem is that the "WHERE" statement is not able to read the "max" variable that I'm trying to insert after the > . The max definition (Dmax) at the top is working properly b/c it returns the correct number. If I remove the apostrophe's it gives me another error. Am I missing something here?
    Thanks,
    Bill

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Bill

    Yep - your SQL where is condition is saying where the ORDER_NUMBER is greater than 'Max' (i.e. the text Max). You want:

    strSQL = "INSERT INTO order_det_update ( ORDER_NUMBER, ITEMNO, FirstOfITEM_DESC, 0, 2, 3, 4, 5, 6, 7, 8, 9 )"
    strSQL = strSQL & "SELECT [ord det conv].ORDER_NUMBER,.......(detail omitted)
    strSQL = strSQL & "FROM [ord det conv]"
    strSQL = strSQL & "WHERE ([ord det conv].ORDER_NUMBER > " & max & ")"

    Now it saying where the ORDER_NUMBER is greater than the value of the variable Max. No apostrophe as it is a numeric value.

    BTW, how did the Subform Working like a Subdatasheet thing go?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    139
    Pootle,

    Thanks for correction on the variable, it worked great!

    On the subform, there were good suggestions but they didn't quite fit my situation. Problem is each record in my subform has two rows so I don't think datasheets will work. I'm thinking about a flexgrid type activeX solution, but that looks time consuming. Think that it's going to stay the way it is for a while.
    Thanks,
    Bill

Posting Permissions

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