New Records in Query

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am pulling data from 4 tables in a combination of 3 queries. All 3 queries contain one field that is common "PurchaseOrderNo", the final query produces some filter data and only the information needed. I am trying to figure out how to specify the query to only produce new data results since the query was last run, if that makes sense. this is my SQL
SQL:
SELECT po_detail2.PurchaseOrderNo, po_detail2.VendorNo, po_detail2.ItemCode, po_detail2.LotSerialNo, IM068_MXPUnivProdCode.UDF_UNIQUE_KEY, Right([UDF_UNIQUE_KEY],1) AS SIZE_INDEX, Left([UDF_UNIQUE_KEY],Len([UDF_UNIQUE_KEY])-1) AS INVENTORY_KEY
FROM po_detail2 LEFT JOIN IM068_MXPUnivProdCode ON po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo
WHERE (((po_detail2.PurchaseOrderNo)="0056334" Or (po_detail2.PurchaseOrderNo)>"0056334") AND ((po_detail2.ItemCode)="K500" Or (po_detail2.ItemCode)="PC55"))
ORDER BY po_detail2.PurchaseOrderNo DESC;
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you show the SQL?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you show the SQL?
I added the "tblQueryLastRun" just to see if that helped.

SQL:
SELECT IM068_MXPUnivProdCode.UDF_UNIQUE_KEY, Right([UDF_UNIQUE_KEY],1) AS SIZE_INDEX, Left([UDF_UNIQUE_KEY],Len([UDF_UNIQUE_KEY])-1) AS INVENTORY_KEY, po_detail2.VendorNo, po_detail2.ItemCode, po_detail2.LotSerialNo, po_detail2.DateUpdated, po_detail2.TimeUpdated, po_detail2.PurchaseOrderNo, po_detail2.[Qty Ordered]
FROM tblQueryLastRun, po_detail2 INNER JOIN IM068_MXPUnivProdCode ON po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo
WHERE (((CDate([TimeUpdated]))>DMax("dtmQueryLastRun","tblQueryLastRun")))
ORDER BY po_detail2.PurchaseOrderNo DESC;
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I took these two screen shots if it helps. One is from the table with the records, the highlight line is the record entered between the last two times the query ran (2nd screenshot)
db.png

tbl.png
 

Attachments

  • tbl.png
    tbl.png
    15.5 KB · Views: 0

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Oh!

You have two separate fields for the date and time of last update.

And what does the [TimeUpdated] field even mean? That doesn't look like a time to me...
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Oh!

You have two separate fields for the date and time of last update.

And what does the [TimeUpdated] field even mean? That doesn't look like a time to me...
The "TimeUpdated" is how the time data is coming over from the source, I know it is not in the correct format but I can't change the format of the source (at least I don't know how).
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The "TimeUpdated" is how the time data is coming over from the source, I know it is not in the correct format but I can't change the format of the source (at least I don't know how).
It's basically the same as the time that the record is created.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

But what does it mean? What, say, is represented by 10.43? Ten point four three seconds after midnight? Ten point four three hours? Forty-three minutes after 10 'o'clock?

If there is some consistent encoding, then we can write a formula to convert that into a time that, with the date, provides a complete and valid date-time for the comparison.
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
But what does it mean? What, say, is represented by 10.43? Ten point four three seconds after midnight? Ten point four three hours? Forty-three minutes after 10 'o'clock?

If there is some consistent encoding, then we can write a formula to convert that into a time that, with the date, provides a complete and valid date-time for the comparison.
It is definintly based off of a 24hr time conversion. I just entered a record at 2:07 pm and a couple seconds and the TimeUpdated value is 14.1178. But what is weird some values go to 5 decimal places and others go to 4, maybe the 4 decimal values are just leaving off the trailing "0". If my math is correct then every ".01667= 1 second" and that conversion looks correct for the 14.1178- 2:07pm
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It is definintly based off of a 24hr time conversion. I just entered a record at 2:07 pm and a couple seconds and the TimeUpdated value is 14.1178. But what is weird some values go to 5 decimal places and others go to 4, maybe the 4 decimal values are just leaving off the trailing "0". If my math is correct then every ".01667= 1 second" and that conversion looks correct for the 14.1178- 2:07pm
Mistake earlier .01667=1 minute
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
A little strange, but try this (maybe there's a better way?):

SQL:
CDate([DateUpdated] & " " & (CInt([TimeUpdated]) & ":" & Format(CInt(60 * (CDbl([TimeUpdated]) - CInt([TimeUpdated]))), "00") & ":" & Format(CInt(60 * (CDbl((60 * (CDbl([TimeUpdated]) - CInt([TimeUpdated])))) - CInt(60 * (CDbl([TimeUpdated]) - CInt([TimeUpdated]))))), "00"))) > DMax("dtmQueryLastRun","tblQueryLastRun")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,943
Messages
5,639,116
Members
417,073
Latest member
CBG_Christian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top