Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    1/2 Function (A2k on W98SR1)

    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.
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    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.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1/2 Function (A2k on W98SR1)

    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.
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    <hr>Couldn't open the query! <hr>
    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?
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1/2 Function (A2k on W98SR1)

    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;
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    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.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1/2 Function (A2k on W98SR1)

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    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.
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1/2 Function (A2k on W98SR1)

    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?
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    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.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1/2 Function (A2k on W98SR1)

    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: 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?
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: 1/2 Function (A2k on W98SR1)

    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.
    Charlotte

Posting Permissions

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