Results 1 to 9 of 9

Thread: Report Question

  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Report Question

    Hi,

    I have a query for a report to put in certain criteria and it pulls that data and puts it on a report. Well, I have the query set to allow me to pull a specific date range, company name, and region. Well, there is NOT always a region. What do I type in the region prompt in order to pull the records without a region. Right now, if I leave it blank, it gives me an error and displays no records on the report.

    I hope this makes sense...Thanks!

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Try this....

    If you are using the query design mode and making the user put in the parameter ([Region]), you can just add a null ([Region] or is Null) and that should resolve the issue of region being null. This assumes that you would want all of the records that matched the other criteria but had a null region.

    Hope this helps, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    Well, basically, I want them to either be able to put a region in (and it pull and display the regions on the form) or if the company does not have regions I want to be able to bypass the region part without it displaying a null value.

    For instance report 1 would be as follows:

    Company A
    09/12/2008 to 09/20/2008
    Region: east

    report 2

    Company B
    09/12/2008 to 09/20/2008
    Region:

    (Company B has no regions so I just want the record to display all records for the company b without displaying an error)

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    How are you making the user enter the region? Do you have something like [Region] on the parameter line of the query? If so, change it to [Region] or is null.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Oct 2007
    Posts
    214
    Thanks for your reply. I did that, and it displays ALL records with the company name regardless of if I enter a region or not. If I enter the region (because company A has different regions) it just displays all records associated with that company and does not display the region i entered. So basically, it is not recognizing if I enter a region or if I don't it just displays all records associated with the company name...even if the company has a region and i type that in the region parameter.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Like "*" & [Enter a Region:] & "*"

    But I would actually recommend a custom dialog box in place of a query parameter.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Oct 2007
    Posts
    214
    Still no go. It only allows me to display the company that has regions. For the company that has no regions, I can't seem to leave the region parameter blank or it displays an error on the report, however for this company, there are no regions to enter. I was hoping to be able to run companies with regions and companies without regions from one report query.

    For instance. I enter company A, my date range, and a region and it displays the proper data (because company A has regions)

    I enter in company b, my date range, and leave the region blank (because company b has no regions) and it displays nothing on the report but error marks.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    A custom dialog box would be better then. If the region box on the custom dialog box is empty, then the code does nothing about the region. If the region box has something in it, then the code can add "Region = " to any SQL.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    I guess I didn't explain it too well before. Are you putting a parameter in the Criteria line? Something to make the user enter a region? Something like [Enter Region:]? This is the only way I think you could be doing this. If that is correct, you have to add the or is null so the query will grab all of the records that match the user's region or are null. So in place of just [Enter Region:] you need to have [Enter Region:] or is Null. This works I've just tried it about 20 times.
    --If its free, take it for what its worth!

Posting Permissions

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