Excel Data Query stops at specific date

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
you should check for error(s)

err.png
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

1600435518666.png


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

1600436243810.png


Definition:

Connection String:

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


Command Text:

JavaScript:
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

1600436026643.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126

ADVERTISEMENT

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

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,492
Members
410,840
Latest member
Kar3ousse
Top