Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (2k)

    In one of my queries, I have to specify a parameter for the date to search up to. I am wondering can i display the paramters (criteria) that were used in the query on my form in a label or text box? I can't see anyway to get this information at this point. Is this possible?

    2nd question i just ran into: one of the fields on my form display a period when records have a period in them. This is just a easy way of getting around a null problem for now, but i was trying to make it so the text box would display a blank when records with the period came up. however, my code didn't work, i tried putting it in before update, after update, and change for both the text box and the form, why doesn't this work?:

    if me.txtrev.text = "." then me.txtrev.text = ""
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    In the query , add a column and enter the exact same expression you enter for your parameter.
    sample : if your parameter is [Enter Name] use :
    SomeName : [Enter Name]
    Use the the field SomeName as control source for your textbox.

    For the period use :
    <pre>=iif([txtrev]=".","",[txtrev])</pre>

    as control source
    Francois

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

    Re: Query Criteria (2k)

    1. You can refer to a query parameter on a report, but not on a form, as far as I know. Instead of using a parameter in the query, use a control on a form.
    - Create an unbound form frmDate.
    - Put a text box txtDate on the form, and set its format to a date format.
    - Set the criteria for the date field in the query to [Forms]![frmDate]![txtDate]
    - Put a command button on the form to open the form bound to the query.
    - Put a text box on this form with Control Source =[Forms]![frmDate]![txtDate]

    2. Give the text box a name different from the field. Set its Control Source to
    <code>=IIf([NameOfField]=".","",[NameOfField])</code>
    where NameOfField is the name of the field. There may be more elegant ways of doing this.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    When i use:

    =IIf([ODS]![REV_CD]="."," ",[ODS]![REV_CD])

    I get a "#Name?" in the text box for every record.

    and i'm trying the query suggestions now
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    What is ODS ?
    You should use REV_CD and this would be a field in the recordsource of the form.
    Francois

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    got it francois, i was using the expression builder and it put in the query!field when i selected it. i changed it to just the field.

    as for the criteria date thing you suggested, i can't seem to get it to work. let me clear up the way my column is set up:

    Field: CMND
    Table: Source Data
    Sort: Ascending
    Show: True
    Criteria: <[Enter Through MND (00/00/00):]

    and i want to retrieve the date the person enters. i tried doing this:

    Field: Getdate: [Enter Through MND (00/00/00):] <---- gave me the same value the CMND does, but not the parameter specified
    and
    Field: Getdate: <[Enter Through MND (00/00/00):] <---- syntax error
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    In the query use as field :
    Getdate: "< " & [Enter Through MND (00/00/00):]
    Did this give what you want ?
    Francois

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2k)

    Damn you guys are good. thanks again!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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