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;

 

[jarett]

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

Do I put this in a "WHERE" statement in the SQL? I tried in a WHERE statement and got a "data type mismatch error".

 

[jarett]

Just made some progress with the string of SQL in queries run prior to main one (light bulb switched on and remembered we had a consultant convert the time for a different department, went back to his copies and I just had to change some of the expressions/domains, and it worked) , now just figuring out how to merge the two fields and use JonXL "last run query" method. I haven't figured out if everything works yet but I will say sites like these are unbelievable, the amount of information provided and the help provided makes projects like this doable for somewhat non computer literate people. Even though I don't quite understand the "reasoning" in a lot of these answers I can honestly say I feel like I learn more posting questions on here than I learned the 4 years of college (20 years ago/ business degree).

I will post strings once I figure out the merge and last run query.

 

[jarett]

Spoke too soon, my query results are not what they should be, here's how I got where I am at.
Query1 SQL

SELECT PO_PurchaseOrderHeader.PurchaseOrderNo, PO_PurchaseOrderHeader.VendorNo, PO_PurchaseOrderDetail.LineKey, PO_PurchaseOrderDetail.LineSeqNo, PO_PurchaseOrderDetail.ItemCode, PO_PurchaseOrderDetail.QuantityOrdered, PO_PurchaseOrderHeader.DateUpdated, PO_PurchaseOrderHeader.TimeUpdated, Int(Val([PO_PurchaseOrderHeader]![TimeUpdated])) AS [Hour], Int((Val([PO_PurchaseOrderHeader]![TimeUpdated])-Int(Val([PO_PurchaseOrderHeader]![TimeUpdated])))*60) AS [Minute], Int((((Val([PO_PurchaseOrderHeader]![TimeUpdated])-Int(Val([PO_PurchaseOrderHeader]![TimeUpdated])))*60)-(Int((Val([PO_PurchaseOrderHeader]![TimeUpdated])-Int(Val([PO_PurchaseOrderHeader]![TimeUpdated])))*60)))*60) AS Seconds
FROM PO_PurchaseOrderHeader INNER JOIN PO_PurchaseOrderDetail ON PO_PurchaseOrderHeader.PurchaseOrderNo = PO_PurchaseOrderDetail.PurchaseOrderNo
WHERE (((PO_PurchaseOrderHeader.VendorNo)="0001904"))
ORDER BY PO_PurchaseOrderHeader.PurchaseOrderNo DESC;

Query2 SQL

SELECT IIf(IsNull([PO068_MXPPOTierDistribution]![QuantityOrdered]),([po_detail1]![QuantityOrdered]),[PO068_MXPPOTierDistribution]![QuantityOrdered]) AS [Qty Ordered], PO068_MXPPOTierDistribution.LotSerialNo, PO068_MXPPOTierDistribution.ItemCode, po_detail1.PurchaseOrderNo, po_detail1.VendorNo, po_detail1.DateUpdated, po_detail1.TimeUpdated, po_detail1.Hour, TimeSerial([po_detail1]![Hour],[po_detail1]![Minute],[po_detail1]![Seconds]) AS [Order Time]
FROM po_detail1 LEFT JOIN PO068_MXPPOTierDistribution ON (po_detail1.PurchaseOrderNo = PO068_MXPPOTierDistribution.PurchaseOrderNo) AND (po_detail1.LineKey = PO068_MXPPOTierDistribution.LineKey) AND (po_detail1.ItemCode = PO068_MXPPOTierDistribution.ItemCode)
ORDER BY po_detail1.PurchaseOrderNo DESC;

Main query 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.PurchaseOrderNo, po_detail2.[Qty Ordered], po_detail2.DateUpdated, CStr([po_detail2]![Order Time]) AS [Order Time], [DateUpdated] & " " & [Order Time] AS [DateTime]
FROM po_detail2 INNER JOIN IM068_MXPUnivProdCode ON po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo
WHERE ((([DateUpdated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun")))
ORDER BY po_detail2.PurchaseOrderNo DESC;

The results are pulling older records and none of the records entered in the last couple days, the newest record pulled has a DateTime= 4/6/2021 12:18:35 PM and in the tblQueryLastRun the last record is 4/21/2021 9:34:01 AM

 

[JonXL]

You need CDate() in your expression as it appears in the SQL I provided.

 

[jarett]

Finally got a correct result!!!! Removing the confirmation messages is ok? It will not mess with any data correct?

 

[Micron]

Dismissing the warnings with "yes" will complete the action of the query. If you don't want to see them, your macro (if you have one) needs to turn off warnings, or your code that's running the query needs to do that. Doing so in a macro is risky; errors end macros so that setting will remain off. Doing it in code is risky if there is no error handler. Alternatively, use CurrentDb.Execute method, which you can research for details.

 

[jarett]

Files in correct format and exporting correctly, here is my VBA for the macro

Function Macro1()
On Error GoTo Macro1_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "po_detail3", acViewNormal, acEdit
    DoCmd.TransferText acExportDelim, "Po_detail3 Export Specification", "po_detail3", "C:\Users\jarett.AS\Documents\po_detail3.csv", True, ""
    DoCmd.OpenQuery "qrySetQueryLastRun", acViewNormal, acEdit


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function

 

[Micron]

Macro1_Exit:
  DoCmd.SetWarnings True
    Exit Function

If what you're exporting is the same as the query you're opening, I suspect there is no reason to open it first. If it's to look at before deciding to export/import then there's no pause so I think there's no point.