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;
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
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")
Do I put this in a "WHERE" statement in the SQL? I tried in a WHERE statement and got a "data type mismatch error".
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jarett

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Spoke too soon, my query results are not what they should be, here's how I got where I am at.
Query1 SQL
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
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
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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You need CDate() in your expression as it appears in the SQL I provided.
 
Solution

jarett

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

ADVERTISEMENT

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

appendrow.png
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,092
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
68
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Files in correct format and exporting correctly, here is my VBA for the macro
VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,092
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,108
Members
417,072
Latest member
JaimeDee

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