Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: SQL Statement That

    Can someone please help me with my query below. I am simply trying to create a query for a report who's value ends with the parameter listed in the second column of a combo box.

    SELECT report.column_1
    FROM report
    WHERE report.column_1 like '%' & '-' & [forms]![frmTrip_Report]![cboLocation]![column(1)]));


    example values of column_1
    1234-AA
    1123-BB
    2221-CC
    .
    .
    .


    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think there are 2 things wrong. You need an additional closing quote,
    and you should be using . not ! for referring to the column property

    WHERE report.column_1 like '%' & '-' & [forms]![frmTrip_Report]![cboLocation].column(1) & '));
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If this is within the query designer, you need the Eval function

    Eval("forms!frmTrip_Report!cboLocation.column(1)")

    Also, unless you've chosen to use ANSI 92 SQL mode, the ordinary Access wildcard is * not %
    Roy-Vidar

  4. #4
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the help guys but I am still having trouble. Below are a few where clauses that I have tried but still no luck.

    WHERE (report.column_1 LIKE '%' & '-' & [forms]![frmTrip_report]![cboLocation].column(1));

    WHERE (report.column_1 LIKE '*' & '-' & [forms]![frmTrip_report]![cboLocation].column(1));

    WHERE (report.column_1 LIKE '*' & '-' & [forms]![frmTrip_report]![cboLocation].column(1) & ');

    WHERE (report.column_1 LIKE '%' & '-' & [forms]![frmTrip_report]![cboLocation].column(1) & ');

    WHERE (report.column_1 LIKE '*' & '-' & EVAL"forms!frmTrip_report!cboLocation.column(1) &");

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    WHERE (report.column_1 LIKE '*' & '-' & EVAL("forms!frmTrip_report!cboLocation.column(1)") );
    Inspiration Through Fermentation

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    WHERE report.column_1 LIKE '*-' & EVAL("forms!frmTrip_report!cboLocation.column(1)")
    Roy-Vidar

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Now that we're reducing the statement...

    Is the - really necessary? Are you really looking only for fields that contain a - and the value contained in the combo box?
    Inspiration Through Fermentation

  8. #8
    Join Date
    Nov 2005
    Posts
    91
    Thanks guys but I don't know what I am missing because I am still having problems. It's something extremely minor I am sure. I'll play around with the syntax a bit more to see if I can figure it out.

    Yes the '-' is necessary because there is a '-' before the value queried in the combo box (eg 1234-AA). I could add the '-' to the value listed in the combo box but it would look funny.

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So what kind of error message are you getting?
    Inspiration Through Fermentation

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How about:

    WHERE Right(column_1, 2) = "AA"

    Substituting the form reference of course.
    Paul

  11. #11
    Join Date
    Nov 2005
    Posts
    91
    I copied the examples that you posted as is and access gives me a popup that says nothing more than 'Unknown'.

  12. #12
    Join Date
    Nov 2005
    Posts
    91
    Thanks pbaldy but I need to retrieve the value from the second column of a combo box.

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    WHERE Right(column_1, 2) = forms!frmTrip_Report!cboLocation.column(1)
    Paul

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, I just remembered the column bit won't work in the QBE, as Roy mentioned. His solution should work, plus I thought the Right function was simpler than concatenating the wildcard plus the "-".
    Paul

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Of course there is always the function that could be written that will return a clean compare string. WHERE Right(column_1,2) = fncLocationCode("frmTrip_Report", "cboLocation", "1"). And here is the function:
    Code:
    Function fncLocationCode(frmName, ctlName, ColumnNum) as String
      fncLocationCode = Forms(frmName).Controls(ctlName).Column(Val(ColumnNum))
    End Function
    Last edited by GolferGuy; 04-09-07 at 17:27.

Posting Permissions

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