New Records in Query

[jarett]

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

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]

Can you show the SQL?

 

[jarett]

Can you show the SQL?

I added the "tblQueryLastRun" just to see if that helped.

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]

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)

 

[JonXL]

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]

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]

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]

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]

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]

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]

A little strange, but try this (maybe there's a better way?):

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")