Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: search string not coming up with desired results

    hi again

    i'm trying to create a string which gets passed to the DoCmd.OpenReport criteria and it looks ok, but when the report opens there are no records :-(

    In the message box it displays the following pop up message which looks ok.

    [Ref_ID]='10006/a' & [Category]='Systems'





    stLinkCriteria = "[Ref_ID]=" & "'" & Me![txtbox_SubItems_Ref_ID] & "'"

    MsgBox (stLinkCriteria)

    stLinkCriteria = stLinkCriteria & " & [Category]= " & "'" & Me![txtCurrent_Category] & "'"

    MsgBox (stLinkCriteria)

    DoCmd.OpenReport st


    DocName, acPreview, , stLinkCriteria


    can anyone see what im doing wrong?
    thanks
    marcus

    p.s i must admit my mind get bent trying to work out the number of "" and ' and ands ....oh brother

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by marcusmacman View Post
    hi again
    p.s i must admit my mind get bent trying to work out the number of "" and ' and ands ....oh brother
    if thats an issue use the CHR function to return the symbnol you want
    from:- http://stackoverflow.org/images/ascii1.gif
    " is chr(34)
    ' is chr(39)
    it may be longwidned but it can make it easier to write mixed SQL and VBA..


    eg:-
    Code:
    stLinkCriteria = "[Ref_ID]=" & chr(39) & Me![txtbox_SubItems_Ref_ID] & chr(39)
    whenever I see a column name with ID in it I expect that column to be numeric... if it is numeric then the value should NOT be delimited
    what is the value of strDocname?
    what is the value of strCriteria?

    however I expect the root cause to your problem is confusion between SQL and VBA
    Code:
    stLinkCriteria = stLinkCriteria & " & [Category]= " & "'" & Me![txtCurrent_Category] & "'"
    use & as a VBA string concatenation symbol, use the boolean AND in a where clause
    so that should read:-
    Code:
    stLinkCriteria = stLinkCriteria & " AND [Category]= " & "'" & Me![txtCurrent_Category] & "'"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks again healdem, code worked first time. I like the idea of using the chr(34) function.

    Just for completeness Ref_ID contains incremental numbers for each record 10001/a 10002/a etc and also revisions of the same number so 10001/b or 10002/c

    stDocName = "Product Sale Agreement"

    stLinkCriteria = "[Ref_ID]=" & "'" & Me![txtbox_SubItems_Ref_ID] & "'"


    I tried to put the string together in stages to help me diagnose any problems.
    thanks again for your help
    marcus
    Last edited by marcusmacman; 10-17-14 at 12:13.

Posting Permissions

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