Results 1 to 6 of 6

Thread: syntax problem

  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: syntax problem

    hi
    why this code do,nt work
    Code:
    If Nz(Me.StartDateTmp) <> "" And Nz(Me.EndDateTmp) <> "" Then
          StrFilt = StrFilt & "[RequestDate] Between [Forms]![FrmSearch]![RequestDate] And [Forms]![FrmSearch]![RequestDate] AND"
    End If
    problem in Syntax?

    Very very thx
    Last edited by hsr; 07-05-11 at 02:50.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what are startdatetmp and enddatetmp
    are they columns or are they controils or are they variable
    the structure of the if statement looks iffy to me
    I'd expect
    if (nz(StartDateTmp) AND StartDateTmp<>"") AND.....
    if they are date values then it may make sense to test for a date rather than ""
    if (nz(StartDateTmp) AND isdate(StartDateTmp)) AND.....

    setting a between clause with the same value is pointless, you'd be better off testing for equality
    StrFilt = StrFilt & "[RequestDate] = [Forms]![FrmSearch]![RequestDate]

    incidentally there is a dodge to simplify code for such sub clauses

    strfilter = "where 1=1"
    if (isdate(mystartdate)) then
    strfilter = strfilter & " AND tablename.StartDate >= myStartDate"
    if (isdate(myenddate)) then
    strfilter = strfilter & " AND tablename.EndDate <= myendDate"
    ...and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Nz() without its optional second parameters will never return what you're expecting (an empty string). Consider the following alternatives:
    Code:
        x = Nz(Me.Text0.Value)      ' --> Variant Empty
        x = Nz(Me.Text0.Value, "")  ' --> "" (zero-length string)
        x = Nz(Me.Text0.Value, 0)   ' --> 0 (numeric)
    So if you want to test against a zero-length string, your code should be:
    Code:
    If Nz(Me.StartDateTmp, "") <> "" And Nz(Me.EndDateTmp, "") <> "" Then
    2. In
    Code:
    StrFilt = StrFilt & "[RequestDate] Between [Forms]![FrmSearch]![RequestDate]
    If you're dealing with date/time values, they must be enclosed with '#' and be in the US format (mm/dd/yyyy)

    3. If you try to concatenate a litteral string with the value of a control on a form, the reference to this control should not be enclosed in quotation marks.

    The line then becomes:
    Code:
    StrFilt = StrFilt & "[RequestDate] Between #" & Format([Forms]![FrmSearch]![RequestDate],"mm/dd/yyyy") & "# And #" & Format([Forms]![FrmSearch]![RequestDate], "mm/dd/yyyy") & "# AND"
    4. Notice that the previous line use the same value (Forms]![FrmSearch]![RequestDate]) for both members of the Between operator. This is the same as writing:
    Code:
    If A Between 1 And 1
    Is it really what you want ?
    Have a nice day!

  4. #4
    Join Date
    Jul 2011
    Posts
    3

    +++

    very thx
    StartDateTmp and EndDateTmp and many other same that are textboxs inmy serch form
    and I build a string that filter my form to show search result

    this code is true :
    Code:
     StrFilt = StrFilt & "RequestDate  Between  [StartDateTmp]  And  [EndDateTmp]" & "  AND"

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by hsr View Post
    this code is true :
    Code:
     StrFilt = StrFilt & "RequestDate  Between  [StartDateTmp]  And  [EndDateTmp]" & "  AND"
    As Sinndho was saying before, the correct syntax would be
    Code:
     StrFilt = StrFilt & " (RequestDate Between #" & Me.StartDateTmp & "# And #" & Me.EndDateTmp & "#) AND"
    Notice I put a space and parentheses () into the code. Let me explain.

    I assume that SrFilt ends with a character other than a space. In that case, the first character of the new string must be a space so it won't be a run-on word. This is a common problem and must always be addressed. Let's say StrFilt = "Cntl = 5". With your code, the final executable line will read "Cntl = 5RequestDate...", which is not syntactically correct.

    As for the (), look at the new code you're concatenating. It has the 'And' operator twice. Even though they are inside quotes, when the VBA interpreter soves for your variables and turns the code into an executable statement, the quotes go away, and the interpreter will have a hard time figuring out which clause goes with which 'And' operator. In order to tell the interpreter how you want the code to work, you must use () to combine some clauses and seperate others.

    Sam

    PS The above assumes that the variable RequestDate is a field in a recordset, and StartDateTmp and EndDateTmp are controls on a form/report. If this is not true, the line of code will not work.
    Last edited by Sam Landy; 07-06-11 at 19:29. Reason: Needed to clarify the statement

  6. #6
    Join Date
    Jul 2011
    Posts
    3

    You're right and your code is true
    Thanks again for the help
    Last edited by hsr; 07-07-11 at 07:11.

Posting Permissions

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