User Profile

Collapse

Profile Sidebar

Collapse
allin1joe
allin1joe
Last Activity: Oct 29 '12, 01:01 AM
Joined: Oct 6 '12
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • allin1joe
    replied to Left Join Returning #Error
    in Access
    Well, I solved my own issue, but it's not the solution I wanted. I created a query with the following SQL:

    Code:
    SELECT Project.UCMG_CD, Project.SPRF, Project.PROJECT_NAME, IIF(ISNULL(BudgetImport.F7),0,CCur(BudgetImport.F7)) AS Current_Prompt_Estimate, IIF(ISNULL(BudgetImport.F9),0,CCur(IIf(BudgetImport.F9='$-',0,BudgetImport.F9))) AS Hist_Spend, IIF(ISNULL(BudgetImport.F10),0,CCur(BudgetImport.F10)) AS CY_UHGIT_Spend, IIF(ISNULL(BudgetImport.F12),0,CCur(IIf(BudgetImport.F12='$-',0,BudgetImport.F12)))
    ...
    See more | Go to post

      Leave a comment:

    • allin1joe
      replied to Left Join Returning #Error
      in Access
      Sorry for the double post, but I just found some interesting information. Sounds like this is caused because I'm left joining with a query. With this being the case, here's the SQL behind the query:

      Code:
      SELECT IIf(Len(BudgetImport.F1)>9,
BudgetImport.F1,Null) AS UCMG_ID, 
  IIf(Len(BudgetImport.F1)=9,
BudgetImport.F1,Null) AS SPRF, 
  ccur(nz(BudgetImport.F4,0)) AS CY_UHG_IT_Alloc, 
  ccur(BudgetImport.F7)
      ...
      See more | Go to post

        Leave a comment:

      • allin1joe
        started a topic Left Join Returning #Error
        in Access

        Left Join Returning #Error

        I have a query that I'm trying to return all Projects and the budget for any of those projects that exist. If a budget doesn't exist, I want to show 0s. For some reason, I can't get rid of #Error for these fields when the left join doesn't return something. Below is my query with all of the different attempts of handling this issue:

        Code:
        SELECT Project.UCMG_CD, 
    Project.SPRF, 
    Project.PROJECT_NAME,
        ...
        See more | Go to post
        Last edited by TheSmileyCoder; Oct 13 '12, 01:44 PM. Reason: Edit to make image visible.
        • allin1joe
          replied to Access 2007 locks up when exporting to SharePoint
          in Access
          "In this case, from what I gather from what little you've posted: "

          I don't know what else to post. I posted all of my code and the procedures that I'm using to try to export that data to SharePoint.

          Are you suggesting that I create a temporary table through VBA with the data and try to send it to SharePoint that way? I'll look into the make table function tonight.

          Thanks!

          Joe
          See more | Go to post

            Leave a comment:

          • allin1joe
            replied to Access 2007 locks up when exporting to SharePoint
            in Access
            I'm new to Access and VBA, but have been doing database programming for 20 years. Everything I've done is "borrowed from somewhere" :), but personalized to do what I needed to do. I wrote the query at first to be the source of a report. The need then arose to use it in SharePoint, so I tried the built in SharePoint export functionality which hangs. That's when I copy/pasted the query into an actual query, which again works in Access,...
            See more | Go to post

              Leave a comment:

            • allin1joe
              replied to Access 2007 locks up when exporting to SharePoint
              in Access
              I posted the SQL that calls the function in post #4. Basically, it's a call to the VBA function in the select statement. The query works fine whether I run the query independent or as the source of a report. The issue I run into when trying to export the result to a SharePoint list.

              Returning a comma delimited list using pure SQL isn't possible in Access. That's why I'm using the VBA function. If you have other ideas to return what...
              See more | Go to post

                Leave a comment:

              • allin1joe
                replied to Access 2007 locks up when exporting to SharePoint
                in Access
                You are correct. The data looks something like this:

                SPRF/Project Name/Program List
                1234/Project 1/Program 1, Program 2
                2345/Project 2/Program 1, Program 3, Program 4

                Basically, I'm looping through the child tables and returning all of the values for this project in a comma delimited list so I can show them in one field.

                I can't post a screen shot of my actual data as it's sensitive information....
                See more | Go to post

                  Leave a comment:

                • allin1joe
                  replied to Access 2007 locks up when exporting to SharePoint
                  in Access
                  I put the stop in the function right after the last DIM statement. I right clicked on the query and chose Export to SharePoint list. I didn't get any debug messages. It went through a couple of different windows, and crashed when it got to "Copying Data to SharePoint List for Pipeline_Test (33%)".
                  See more | Go to post

                    Leave a comment:

                  • allin1joe
                    replied to Access 2007 locks up when exporting to SharePoint
                    in Access
                    Yeah, I do have it narrowed down to the function call. I have removed the function call and replaced with a hard coded comma delimited string and it works.

                    To the other poster, here is my query:

                    Code:
                    SELECT Project.SPRF, 
     Project.PROJECT_NAME, 
     Multi_Rec_List(Project.Project_ID,"PROGRAM") AS Program_list, 
     Multi_Rec_List(Project.Project_ID,"INITIATIVE") AS
                    ...
                    See more | Go to post

                      Leave a comment:

                    • allin1joe
                      started a topic Access 2007 locks up when exporting to SharePoint
                      in Access

                      Access 2007 locks up when exporting to SharePoint

                      I have a query that has a VBA function in the select statement (creates a comma delimited list from a child table). Whenever I attempt to export the results of that query to a SharePoint list, Access crashes. I get the "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience. When I take this function out of the select statement, the export works. The function works fine if I just return the result...
                      See more | Go to post
                      No activity results to display
                      Show More
                      Working...