Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DatePart or Format?? (Access2K/Win2K)

    I have a monthly report.

    I'd like to open it from a Reports switchboard, via a parameter form, where i choose the month. I have built the switchboard, and the parameter form, and a combobox looks up the values of the order date in the recordsource. All ok so far.

    What i'd like is for the order date in the combobox just to show the month and year, so instead of 20 or 30 orders showing dd-mm-yy, just 1 entry in the combobox showing the mmm-yy for those orders, and the same for all the other orders...

    Is this a simple case of formatting, or is there some vba required??

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

    Re: DatePart or Format?? (Access2K/Win2K)

    Using the date field itself and setting the Format propety of the combo box to mmm-yy would not suppress the duplicates; you will have to set the Row Source of the combo box to an SQL statement or a query that selects distinct months:

    SELECT DISTINCT Format([DateField], "mmm-yy") FROM tblSomething

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DatePart or Format?? (Access2K/Win2K)

    And it does! Thank you!

    I am fairly sure this is an unrelated problem, as your suggestion works in a small test qry.

    When i try to use the form/cbobox as the criteria in the report's query, it tells me it does not recognise the criteria as a valid source. HOWEVER in a test query with very many fewer fields it has no problem.

    Is it possible that on a complex query (several tables and a subcrosstabquery), it reports an error on the criteria, yet on a simple query it does not???

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

    Re: DatePart or Format?? (Access2K/Win2K)

    In complicated queries, in particular in crosstab queries, you have to declare parameters explicitly. You will have to do this in the query that refers to the combo box in its criteria, and in queries built on it:
    - Open the query in design view.
    - Copy the criteria to the clipboard.
    - Select Query | Parameters...
    - Paste the criteria (or type them exactly as they are in the criteria row) into the Parameter column.
    - Select the appropriate data type in the Data Type column.
    - Click OK.

Posting Permissions

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