• 1/2 Function (A2k on W98SR1)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 1/2 Function (A2k on W98SR1)

    Author
    Topic
    WSStephenElms
    AskWoody Lounger
    September 11, 2001 at 3:50 pm #360215

    I am trying to concatenate and edit fields in a report based on an SQL Query – the results are that either the report fails to open from design view or that the function returns error.

    Here are 2 examples: TextBoxAddress1 =[Address1] & ” ” & [Address2] This returns #Error when the report is opened;
    this function is to strip out the first part of a reference number =Left([PAYE Coll Ref],1) The report will not open at all!

    I will appreciate all help please.

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WScharlotte
      AskWoody Lounger
      September 11, 2001 at 4:02 pm #541917

      Where are these equations: in code, in the controlsource, or in the underlying query? You haven’t really provided enough information to get an answer.

      Reply | Quote
    • WSStephenElms
      AskWoody Lounger
      September 11, 2001 at 4:19 pm #541919

      Oops – sorry for the lack of info. The functions are placed in text boxes in the report itself. After finding that they, now, do not work I tried the same function in the underlying query. Couldn’t open the query! This appears to happen with text functions rather than arithmetical functions.

      Reply | Quote
      • WScharlotte
        AskWoody Lounger
        September 11, 2001 at 6:59 pm #541936

        [indent]

        Couldn’t open the query!

        [/indent]Post the SQL of the query. Otherwise, we’re just guessing at your problem. Do you get a specific error message, or what? And have you checked your references to make sure something isn’t MISSING there?

        Reply | Quote
        • WSStephenElms
          AskWoody Lounger
          September 11, 2001 at 8:01 pm #541948

          Herewith the underlying SQL for the query. It takes three similar sources and combines them to one listing. The query shows the data perfectly until I try to include a text based funtion to extract and parse text.
          SELECT [CODCode] as [Code] ,[CODName] as [Client Name],[CodClientStatus] as [Status],[CODPayeRef] as [PAYE Ref],[CODPayeCollRef] as [PAYE Coll Ref],[CodAddress1] as [Address1],[CodAddress2] as [Address2],[CodAddress3] as [Address3],[CodAddress4] as [Address4],[CodAddress5] as [Address5],[CodAddress6] as [Address6],[CodPostCode] as [PostCode],[IndClientName] as [Contact]
          FROM [QryCompanyData]
          WHERE [CODPayeReg]=True

          UNION SELECT [SECode] as [Code],[SEName] as [Client Name],[SEBusStatus] as [Status],[SEPayeRef] as [PAYE Ref],[SEPPayeCollRef] as [PAYE Coll Ref],[SEBusinessAddress1] as [Address1],[SEBusinessAddress2] as [Address2],[SEBusinessAddress3] as [Address3],[SEBusinessAddress4] as [Address4],[SEBusinessAddress5] as [Address5],[SEBusinessAddress6] as [Address6],[SEBusinessPostCode] as [PostCode],[IndClientName] as [Contact]
          FROM [QrySelfEmployment]
          WHERE [SEPayeReg]=True

          UNION SELECT [PSHIPClientCode] as [Code],[PSHIPName] as [Client Name],[PshipStatus] as [Status],[PSHIPPayeRef] as [PAYE Ref],[PSHIPCollRef] as [PAYE Coll Ref],[PSHIPAdd1] as [Address1],[PSHIPAdd2] as [Address2],[PSHIPAdd3] as [Address3],[PSHIPAdd4] as [Address4],[PSHIPAdd5] as [Address5],[PSHIPAdd6] as [Address6],[PSHIPAddPoCo] as [PostCode],[IndClientName] as [Contact]
          FROM [QryPartnershipData]
          WHERE [PSHIPPayeReg]=True;

          Reply | Quote
          • WScharlotte
            AskWoody Lounger
            September 11, 2001 at 10:13 pm #541954

            I take it that your union query returns records as you posted it, right? If so, can you add the “function” you tried to use so we can see what exactly you did? I personally can’t guess at it from what you posted before.

            Are you trying to parse the text as an expression or in the criteria or what? As for your controls, did you make sure that none of the controls have the same name as one of the underlying fields you’re referencing? If not, it will error out since you’re creating a circular reference.

            Is there any code behind the form? If so, put in a breakpoint in your Open event and try to figure out exactly where the report is erroring out.

            Reply | Quote
    • WSStephenElms
      AskWoody Lounger
      September 12, 2001 at 12:37 pm #542027

      I think I am getting closer to the problem – although not the solution. I imported data to a table, all ok.
      The query reads the table data perfectly – there are no calculated or coded fields whatsoever.
      Field5 data look like this: JR995906C A
      I want to be able to separate this text to individual characters so I created a new field called Test and used the expression builder thus: Test: = Left ( [Field5] , 1)
      Saved the Query an on trying to open it the message “Undefined function ‘Left’ in expression” appears. This seems to me that something is not right with my Access installation? Help please!

      Reply | Quote
      • WScharlotte
        AskWoody Lounger
        September 12, 2001 at 1:00 pm #542036

        You probably have a broken reference. Go to the VBE and select Tools–>References. If any of the checked items in the list show MISSING, then that’s the reference you have to either remove or locate to make your database start behaving.

        Reply | Quote
        • WSStephenElms
          AskWoody Lounger
          September 13, 2001 at 8:38 am #542213

          Thank you Charlotte, the missing link is hrtbeat.ocx – this I understand to be an active x file. However how this impacts on Access I am completely unclear but, in searching (my installation cd’s W98 and Office 2k) for this I could not any reference to this file on the web. So, if I remove this reference what could be the impact on access please?

          Reply | Quote
          • WScharlotte
            AskWoody Lounger
            September 13, 2001 at 1:25 pm #542272

            If you aren’t using it for anything, it won’t have any effect on Access except to make your errors go away. If you’re using it somewhere, you’ll see an error message when you open the form that used it, and any code that referenced it will come crashing down. Uncheck the broken reference and then compile the code and that will tell you whether there are any code references to the library that you’ll have to comment out.

            Reply | Quote
            • WSStephenElms
              AskWoody Lounger
              September 14, 2001 at 3:38 pm #542482

              Charlotte, many thanks indeed for your cogent advice – the unchecking of the missing file name has worked and I can now manipulate text.

              On a subsidiary note – the text is brought in to a table via a macro using transfertext function and works OK on the local Pc: D:DATABASEPAYE_ImportPSLA.CSV. However on Pc2 with its own copy of Access2K – reading the database from Pc1 we cannot use the path above and so have to substitute: STEPHEND_DataDatabasePAYE_ImportPSLA.CSV. This works but of course when this change is made to the macro we find that it wont work on Pc1! Can you shed some light on this please?

              Reply | Quote
            • WScharlotte
              AskWoody Lounger
              September 14, 2001 at 4:46 pm #542495

              Access uses hard coded paths. Since the mapping from the two PCs is different, whichever one you tweak the path for is going to break the application on the other one. You could use a local table to store the appropriate path from that PC and build a function to lookup that path string. Then convert your TransferText macro to code and substitute the string that your function returns for the hardcoded path.

              Reply | Quote
    Viewing 2 reply threads
    Reply To: 1/2 Function (A2k on W98SR1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.