Excel Data Query stops at specific date

[willow1985]

I am not sure if anyone can answer this but I thought I would give it a shot.

I have no experience with data Queries. My IT department set up a data query to get a live feed of data from our ERP system. It would automatically refresh every time the excel book was opened with up to date data. IT did an update to the system but swears it should not have affected the Query, however it stopped automatically refreshing to the current date and stops at a specific date of Aug 20. This happens accross 5 excel spreadsheets and 5 different Queries.

IT claims it is excel thats the problem. Any idea what could cause this?
What information would you need from me to troubleshoot?

Thank you to anyone who could point me in the right direction or even help me explain it to my IT department.

 

[JimM]

I've no experience with this either but first thought would be is it the date that's the issue or does 20th Aug coincide with a specific number of rows of data ie is there a limit on how much data is exported\imported ?

 

[plettieri]

Hello willow1985,

Have you checked for any capacity limitations on the dump itself (ie file size) or the PC memory itself?

Have you tried updating it manually, it that is possible.


plettieri

 

[sandy666]

you should check for error(s)

 

[willow1985]

Not sure if I am going to the right place, there is nothing in Queries but there is something in Connections:

Then the connection properties are (I blanked out what I thought was sensitive information):

Definition:

Connection String:

DRIVER={MySQL ODBC 5.1 Driver};UID=bidb;PWD={N0LFH3rSySiW9bv21KqB};SERVER={NAME-WEB1};DATABASE=location;PORT=5555;FOUND_ROWS=1;

Command Text:

SELECT
  t1.id_primary `W/O Number`,
  (SELECT `name` FROM crm_customer WHERE id_primary = t1.id_customer) AS `Customer Name`,
  (SELECT `name` FROM system_manufacturers WHERE id_primary = t1.id_manufacturer) AS `Manufacturer Name`,

  t1.model_number `Model Number`,

  t1.part_name  AS `Part Name`,
  t1.part_number AS `Part Number`,
  t1.serial_number AS `Serial Number`,

  (SELECT `name` FROM list_workorder_warehouse WHERE id_primary = t1.id_warehouse) AS `Warehouse Name`,

  t4.grand_total `Grand Total`,

  IFNULL(FROM_UNIXTIME(t1.date_received, '%Y-%m-%d'), 0) AS `Date Received`,
  IFNULL(FROM_UNIXTIME(t4.date_quoted, '%Y-%m-%d'), 0) AS `Date Quoted`,
  IFNULL(FROM_UNIXTIME(IF(t1.date_approved = 0, NULL, t1.date_approved), '%Y-%m-%d'), 0) AS `Date Approved`,
  IFNULL(FROM_UNIXTIME(IF(t1.date_promised = 0, NULL, t1.date_promised), '%Y-%m-%d'), 0) AS `Date Promised`,
  IFNULL(FROM_UNIXTIME(IF(t1.date_original = 0, NULL, t1.date_original), '%Y-%m-%d'), 0) AS `Date Original`,
  IFNULL(FROM_UNIXTIME(IF(t1.date_completed = 0, NULL, t1.date_completed), '%Y-%m-%d'), 0) AS `Date Completed`,
  IFNULL(FROM_UNIXTIME(t2.date_created, '%Y-%m-%d'), 0) AS `Date Invoiced`,
  IFNULL(FROM_UNIXTIME(t3.date_shipped, '%Y-%m-%d'), 0) AS `Date Shipped`,

  (t4.grand_total - t4.tax_total) `Sub Total`,

  (SELECT `name` FROM system_stations WHERE id_primary = t1.id_station) AS station_name,

  IFNULL(FROM_UNIXTIME(IF(t5.date_created = 0, NULL, t5.date_created), '%Y-%m-%d'), 0) AS `Date Exchanged`,


  #IFNULL(t5.date_created, t1.date_received) AS `Date Start One`,

  (
    SELECT
         SUM(time_resumed - time_paused) / 86400
       FROM
         erp_workorder_inactive
       WHERE
         id_workorder = t1.id_primary AND time_resumed > time_paused
            AND
        time_paused >= (IFNULL(t5.date_created, t1.date_received)) AND time_resumed <= t4.date_quoted
  ) AS `Paused Interval One`,

  (
    SELECT
      SUM(time_resumed - time_paused) / 86400
    FROM
      erp_workorder_inactive
     WHERE
      id_workorder = t1.id_primary AND time_resumed > time_paused
          AND
        time_paused >= t4.date_quoted AND time_resumed <= t1.date_approved
  ) AS `Paused Interval Two`,

  (
    SELECT
      SUM(time_resumed - time_paused) / 86400
    FROM
      erp_workorder_inactive
    WHERE
      id_workorder = t1.id_primary AND time_resumed > time_paused
        AND
      time_paused >= (IFNULL(t5.date_created, t1.date_received)) AND time_resumed <= t3.date_shipped
  ) AS `Paused Interval Three`,

  ROUND((SELECT SUM(IF(time_resumed = 0, UNIX_TIMESTAMP(), time_resumed) - time_paused) / 86400 FROM erp_workorder_inactive WHERE id_workorder = t1.id_primary))  AS `Paused Days (Total)`
FROM
  erp_workorder AS t1
LEFT JOIN
  erp_shipping AS t3
ON
  t1.id_primary = t3.id_workorder
LEFT JOIN
  erp_invoicing AS t2
ON
  t1.id_invoicing = t2.id_primary
LEFT JOIN
  erp_quoting AS t4
ON
  t1.id_primary = t4.id_workorder
LEFT JOIN
  erp_exchange_items AS t5
ON
  t1.id_primary = t5.id_workorder

WHERE
  (t1.date_received >= UNIX_TIMESTAMP() - 157680000)

GROUP BY
  t1.id_primary

ORDER BY
  t1.id_primary DESC

 

[sandy666]

so
Queries = blank
Connections = Query from
Number of records is not limited
After Refresh All it stops in the same place?
I see nothing wrong in the code but I can't check because it's hard to be there
maybe this is the end of data?
Does the same thing happen on another machine?
call IT to come and say: it has to work, they take the money for it

 

[sandy666]

this is local or network machine?
if local check for User Temp folder and Windows Temp folder then clean it
Has this problem happened before or is it the first time?

 

[willow1985]

This happens on all computers. It is definitely not the end of the data. It was working up until Sept 10 and then suddenly all Sept data disappeared and it only went to Aug 20. coincidentally it happened right after an update to the source of the data but IT insists it is an excel problem so I wanted to see what I could discover on my own or at least verify if it is an excel problem or not

 

[sandy666]

IMHO, the fault clearly lies with IT
they have to say that it is the user's fault because IT cannot be to blame (irony)

 

[willow1985]

Well Thank you for confirming the Query is ok. At least I can say that and hope they fix it.
Thank you for verifying it