Results 1 to 11 of 11

Thread: Query Parameter

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have a table in an SQL database containing a bit field with values of 0 or -1. We have a query with the following query parameter on the bit field.

    Like ([Flag (0=No, 1=Yes, Blank=All):]) & "*"

    This works when "0" is entered but not when "1" is entered. How can we make this work so that entering "1" selects records where the bit field contains -1?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    Like "*" & [Flag (0=No, 1=Yes, Blank=All):] & "*"

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that works fine.

    We also want to show query parameters in the header of a report based on the query. We tried putting a text box in the report header, with the query parameter as the data control source.

    Like "*" & ([Mailing Flag (0=No, 1=Yes, Blank=All):]) & "*"

    However, when running the report, this gives an error instead of the query parameter prompt: Syntax error in query expression 'First([Like "*" & ([Mailing Flag (0=No, 1=Yes, Blank=All):]) & "*"])'.

    How can we correct this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    ="Like '*" & [Mailing Flag (0=No, 1=Yes, Blank=All):] & "*'"

    as control source.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that stops the error message. However, if the query parameter is entered as "0" or blank, then it is shown in this field as "Like '**'"; or if it is entered as "1", then it is shown as "Like '*1*'". Is it possible to show just what is entered; i.e., "0", "1" or blank?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Murgatroyd' post='765713' date='17-Mar-2009 00:30']Is it possible to show just what is entered; i.e., "0", "1" or blank?[/quote]
    Of course, simply use

    =[Mailing Flag (0=No, 1=Yes, Blank=All):]

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks, that shows "1" and blank OK but shows blank for "0", even with a "0" format mask.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't understand that, it should display the parameter exactly as entered.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I found that this occurs when there are no records matching the entered parameter; i.e., when the report is empty. It would be ideal if the entered parameter could be shown on a report header even when the report was empty.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of using a parameter prompt, create a form with text box in which the user enters the parameter value.
    In the query and in the control source of the text box on the report, replace [Mailing Flag (0=No, 1=Yes, Blank=All):] with

    [Forms]![NameOfForm]![NameOfTextBox]

    substituting the appropriate names.

    Alternatively, suppress the report if there are no data by setting Cancel = True in the On No Data event of the report.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks.

Posting Permissions

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