Results 1 to 14 of 14
  1. #1
    Devinder
    Guest

    Access97 Reports

    I would like to generate a report based on the value of a field on a form, e.g. Wholesale, Retail, etc. How can I generate a combined report for wholesale and retail customers? The value in the field will be "Wholesale or Retail", but when I put this in the query "Forms!frmCriteria!txtDealerType", I get an error if the txtDealerType is "Wholesale or Retail". What do I need to do?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 Reports

    You'll have to post the SQL for the query. Otherwise, we're just guessing at what you're trying to do and what is wrong with it.
    Charlotte

  3. #3
    Devinder
    Guest

    Re: Access97 Reports

    I have a tblDealers with two fields: Name, DealerType

    I have a frmCriteria with an unbound text field txtDealerType and a button to run the rptDealers.

    I have a rptDealers which prints the Name and the DealerType

    The SQL statement for the record source is:

    SELECT tblDealers.*
    FROM tblDealers
    WHERE (((tblDealers.DealerType)=[Forms]![frmCriteria]![txtDealerType]));


    When I enter "Retail" in the text box, I get the correct report.

    When I enter "Wholesale" in the text box, I get the correct
    report.

    When I enter "Retail or Wholesale" in the text box I get nothing.

    How can I get a report which shows both the wholesale and the retail dealers?

    Thank you in advance for your help.

    Devinder

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Reports

    Either enter an asterisk "*" in the text box, or enter: Retail Or Wholesale.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Reports

    If Retail and Wholesale represent all of the choices for dealer type then you can add a row to your criteria for dealer type in the report's query that will return all records if you leave the form control blank. Just enter the same expression as in the first criteria row followed by Is Null in the second criteria row for the same column. e.g.

    Criteria:[Forms]![frmCriteria]![txtDealerType]
    Or:[Forms]![frmCriteria]![txtDealerType] Is Null

  6. #6
    Devinder
    Guest

    Re: Access97 Reports

    There are other choices: Commercial, Local, John Deere, Other etc.

  7. #7
    Devinder
    Guest

    Re: Access97 Reports

    I don't think that will work. "*" will give me every dealer.
    Dealer Types can be Wholesale, Retail, Local, John Deere, Other etc.

  8. #8
    Devinder
    Guest

    Re: Access97 Reports

    Entering "Wholesale" or "Retail" in the textbox does not work.

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Reports

    In that case the most flexible thing to do would be to build a criteria string which you could use as the wherecondition argument for an openreport method statement. I think a multiselect listbox would give you the most flexibility. Then you could loop through the selected items collection and build your string as you go. Post back if you're interested in using this method and we can help you with the code.

  10. #10
    Devinder
    Guest

    Re: Access97 Reports

    I can get the report if I have two unbound text boxes and enter Wholesale in one and Retail in the other. I could make them combo boxes and link them to tblDealerTypes. That is not an elegant solution.

    I would really appreciate if you could help me with the list box code.

    Thank you very much for your help.

    Devinder.

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Reports

    I'd be happy to help. Perhaps it would be easier if I posted a sample app for you to look at. I'll try to have it up here this afternoon (EDT).

  12. #12
    Devinder
    Guest

    Re: Access97 Reports

    Thank you very much

  13. #13
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Reports

    Here's a sample. It's very basic but should give you an idea.
    Attached Files Attached Files

  14. #14
    Devinder
    Guest

    Re: Access97 Reports

    Thank you very much. This is really good.

    Devinder

Posting Permissions

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