Search results

Oh, sorry about that. I just saw that the topic involved creating new versions of Northwind template so I thought new features would have been introduced 🤷🏻‍♂️

What's the likely hood of getting the functionality of Access linking to remote files? For example, an Excel file or another Access Database on a shared Onedrive folder?

Never and I mean NEVER leave date fields null in queries. That causes so many problems use a date placeholder as suggested by @The_Doc_Man Alternatively, you can introduce a helper column IIF(ISNULL(Shipping), Delivery, Shipping) and run a subquery on that field.

Still getting Duplicates @arnelgp @sonic8 I think your method is working.

Are your trying to connect to the access table or the query? BI should see all tables. If your query has "Nz" in it BI will not see or process it. There are a few Ms Access functions not compatible with BI or Power Query, not too familiar with all of them but I know for sure Nz is one of...

Normally yes but in this instance the data entry is much easier entering the quantity and the total cost for that amount. It is because of that I want to get the last cost of the item. That is the date the record is entered into the database.

I'm trying to get the last/most recent cost of an item using this query. SELECT Product.UPC, Product.PNAME, PurDetail.PurDate, PurDetail.CPU FROM Product LEFT JOIN PurDetail ON Product.UPC = PurDetail.UPC WHERE (((PurDetail.PurDate)=(SELECT MAX(PurDate) FROM PurDetail As P2 WHERE P2.UPC =...

From what I know, Access cannot link to an external (non-unc path) Excel file. The file needs to be in the XML format. Once the Excel file is on your local PC you can link to it, no problem. Is the file being shared through a SharePoint path or are the users opening the file on the LAN? These...

There are 3 things that are imperative to a perfect database design. 1. Normalization 2. Normalization and 3. Normalization If you do not understand the data well enough to classify it you will end up in problems. @I am alive forget what it is and think about what it should be.

Yeah, used a different online sql to vba converter. The extra quotes and brackets were the problem. Thanks "WHERE (((EmpTimes.CHECKDATE) Between #4/23/2023# AND #4/29/2023#) " & _ "AND ((TimeSheet.WDate) Is Null))"

The query runs fine using the GUI but if I reference it like this Set BioClockRS = db.OpenRecordset("qryBioClock", dbOpenSnapshot) Error 3061

SELECT EmpTimes.USERID ,EmpTimes.EID ,EmpTimes.CHECKDATE ,Format([CHECKDATE],'ddd') AS DOW ,EmpTimes.INTIME ,EmpTimes.OUTTIME ,EmpTimes.TIMEWORKED ,EmpTimes.TIMEDURATION ,TimeSheet.WDate FROM EmpTimes LEFT JOIN TimeSheet ON (EmpTimes.CHECKDATE = TimeSheet.WDate) AND (EmpTimes.EID =...

Hi all, This is the statement I'm trying to pass through VBA BioClockSql = "SELECT EmpTimes.USERID " & _ ",EmpTimes.EID " & _ ",EmpTimes.CHECKDATE " & _ ",Format([CHECKDATE],'ddd') AS DOW " & _ ",EmpTimes.INTIME " & _...

@MajP I notice that you created a whole new recordset instead of using .Filter method. Any reason why?

@GPGeorge knows his s###. Top notched developer (y), congrats.

Just to be clear. tblITEMS is not related to anything in my db. I did use a 'Make Table' query initially to create it from the Items query only because I was unable to do a Crosstab Query and now that it was created I decided to keep pushing data into it yearly. So query Items group by year...

You mean just use the 'Make Table' action query? Isn't that what my code is doing? Can you explain a little more please. Can I change the title to include "UPSERTING using VBA". Is that ok with the admins?

A couple of reasons. 1. The database has 10,000+ records. I am aggregating for a 2-year comparison each year has about 6000 records and hundreds of Item variations. AVGITEMCOST takes really long time to load sometimes even crashing the db. Putting the results in a table makes it easier to...

I've attached the db. I want to be able to take PIN, AVGCOST and ITEMWGHT from query Items and append to the table tblITEM if it does not exist If PIN exist in tblITEM.PRICEID update tblITEM.AVGCOST and tblITEM.IWGTTNS with Items.AVGCOST and Items.ITEMWGHT The query Items is an aggregate...

I am trying to add the update portion to the code but it is updating the 1st record in tblITEM with the 1st record in ItemsRS. The both recordsets are not sorted so the wrong values are being populated in tblITEM. Dim db As DAO.Database Dim PrimaryTRS As DAO.Recordset Dim ItemsRS As...