Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New database query (2003)

    I want to create a New Database Query in Excel which pulls in data from an Access query. The problem is that because the Access query has a calculated field the Excel import wizard won't allow the link as there is an 'unrecognised function' in the query. What is the best way to get around this, preferably with out removing the calculated field in the Access query

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: New database query (2003)

    What is in the calculated field expression?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New database query (2003)

    It is a dateaddw function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    If you always need to have up-to-date results in Excel, you'll have to remove the calculated field from the query, and either replace it by something the ODBC driver understands, or perform the calculations in Excel.
    If it's OK if the results are not always up-to-date, you could change the query to a make-table query and run that from time to time. You can then pull the data from the table into Excel. Or export the results of the query from Access to Excel.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    What is dateaddw? Do you mean dateadd?

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New database query (2003)

    Dateaddw is a user defined 'add working days' function. I guess I will have to do the calculation in Excel. It is just that the Access query is a union query and there a number of different conditions which will mean adding different number of days to each record. But I think that is the only way forward as the data needs to be a 'live' link.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    The ODBC driver cannot handle user-defined functions, so you'll have to take the calculated field out of the query, alas.

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New database query (2003)

    I have now removed the calculated field but am now getting another error message - "Too few parameters - expected 8". Any ideas?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    Does the query have parameters, or refer to controls on forms?

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New database query (2003)

    I can't spot any

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    I'd have to have more information - could you post the SQL of the problem query, or post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New database query (2003)

    Hans the SQL is below:

    SELECT tblCostings.ID, tblCostings.[Costing Type], tblCostings.[C&W Ref], tblCostings.DateIn, tblCostings.Status,tblStaff.Name, tblCostings.[Requestor Name], tblCostings.[Customer Name], tblCostings.[Customer Address], tblCostings.Postcode
    FROM tblStaff INNER JOIN tblCostings ON tblStaff.StaffID = tblCostings.Staff
    WHERE (((tblCostings.Status)="WIP"))
    UNION ALL SELECT DivCostings.AtkRef, DivCostings.Type, DivCostings.ClientRef, DivCostings.DateReceivedClient, DivCostings.Status, iif(DivCostings.Planner is not null, DivCostings.Planner, DivCostings.FOMResponsible), DivCostings.ClientPM, DivCostings.ClientCustomer, DivCostings.Address, DivCostings.PostCode
    FROM DivCostings
    WHERE (((DivCostings.Status) Is Null) AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="in progress") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="on hold") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="converted to c4") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="converted to build") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="In Projects") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="In OSM") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null)) OR (((DivCostings.Status)="In Field") AND ((DivCostings.DateReceivedClient) Is Not Null) AND ((DivCostings.DateIssueClient) Is Null));

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New database query (2003)

    I'm afraid that nothing jumps out at me immediately, so I'd have to see (a stripped down copy of) the database.

Posting Permissions

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