Results 1 to 3 of 3
  1. #1
    natalie is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    17

    Error 3075 when trying to preview report

    Hi,


    I'll try to explain what I have to get and what i did.
    I have a system in Access, which uses SQL Server as a back end.
    There are clients in database with orders from inventory, billing report to see charges and orders.
    I was asked to add an additional value to show on the report. I added this column in the tables where needed, i can see the label with amount, but it uses "RegionSort" to sort by region and show an additional region i added, here I have a problem.

    I added some code in the sourse for this report:
    originally it was:
    Me.RecordSource = "SELECT JobOrder.*, IIf([ProvinceState].[Region]=""E"",1,IIf([ProvinceState].[Region]=""O"",2,IIf([ProvinceState].[Region]=""U"",3,4))) AS RegionSort,

    I changes it to:
    Me.RecordSource = "SELECT JobOrder.*, IIf([ProvinceState].[Region]=""E"",1,IIf([ProvinceState].[Region]=""O"",IIf([ProvinceState].[Region]=""N"",3,IIf([ProvinceState].[Region]=""U"",4,5))) AS RegionSort,

    In report'sdesign view, i changed data tab for "RegionSort" as well to:
    =IIf([RegionSort]=1,"Alberta,Manitoba,Northwest Territories,Prince Edward Island,Quebec,Saskatchewan,Yukon",IIf([RegionSort]=2,"British Columbia,New Brunswick,Newfoundland,Ontario",IIf([RegionSort]=3,"Nova Scotia",IIf([RegionSort]=4,"USA","International"))))

    when trying to "preview" this report got Error 3075, then when tried to change things Error 3720, then 3075.

    Spent so long already trying to figure out the problem (checked all the tables, source), but no luck.

    Will appreciate your help

    Thanks

    Tali

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I might create a lookup type table which has the Region field and another field on what it represents. You can then link this table (ie. on the Region field) to your main table in the recordsource query and just add the fields from the lookup type table to produce the values you want displayed. This will save you from having a bunch of IIF statements in the query itself which is most likely causing you problems.

  3. #3
    natalie is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    17
    Ths for your reply pkstormy,
    The problem that this system already has many tables, there is a table with ProvinceRegion, where the values stored. It has many reports and forms, if to add another table, i don't believe it will work at all.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report Preview
    By ColPat in forum Programming
    Replies: 2
    Last Post: 06-25-2010, 02:44 PM
  2. Can't see sub report in print preview
    By LoggyDoggy in forum Access
    Replies: 1
    Last Post: 04-20-2010, 05:38 PM
  3. Runtime 3075 error
    By whm1 in forum Programming
    Replies: 4
    Last Post: 03-24-2010, 02:50 PM
  4. Error 3075 Missing Operator
    By KLynch0803 in forum Queries
    Replies: 5
    Last Post: 02-11-2010, 01:13 PM
  5. Report preview from sub form
    By Hillary in forum Forms
    Replies: 2
    Last Post: 01-31-2010, 07:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums