Search results

Hello isladogs I imported the objects into my database and ran the "View New IMEX Data Tasks". The Report shows the import path as "C:\Users\<UserName>\Documents\<Folder>\export.XLSX" Is it possible hard code the front end import path to a common path and then delete your objects from my...

Thank you all very much. That worked very well. Just had to make a couple of adjustments as the fields are all text fields. DCount("*","qryDuplicateSNs","[Serial Number]=" & Chr(34) & [Serial Number] & Chr(34))>1 As to the why they are all text fields? This is a spreadsheet that is imported...

I'm trying to use conditional formatting to highlight duplicate cells but only when a corresponding cell in the same record is different than the 1st one. Here's a picture of what I'm trying to do. Notice that the SN field gets highlighted for duplicate SNs, but ONLY when the corresponding...

That's interesting. I can certainly do that on my computer, but I don't feel comfortable editing the client's spreadsheet without them knowing it. Plus I'm not even sure how I would go about building that sort of automation... It's hard to believe Microsoft hasn't found a way to fix this...

WOW - that's a pretty nice module. Very powerful. But I wouldn't be able to use it to distribute the Front End to everyone as they all have the data stored in a different paths. The excel spreadsheet is downloaded from the company system and stored locally on the clients personal folder. I'm...

Well, after trying a few ways to get the ACimport to work, I just gave up and went with your suggestion. DoCmd.RunSavedImportExport "SavedImportExportName " Too bad I couldn't get the AC import method to work - kid of feel like this method is a copout since I can only to it on my own machine...

I think I may have found the problem. Not sure if this is causing the truncation, but there are two columns in the excel table with the same column names. Maybe that's what's throwing off the TransferSpreadsheet VBA? The Access import menu dialog seems to fix this somehow on it's own.

I'm trying to import an excel spreadsheet that has one field with more than 255 characters sometimes. So far, I've tried linking to the spreadsheet and using this ACImport VBA and both have continued to truncate this field to 255 characters. DoCmd.TransferSpreadsheet acImport...

Thanks for pointing me in the right direction. I managed to get this to work using a correlated subquery as explained here: https://www.geeksforgeeks.org/sql-correlated-subqueries/

Trying to create a recordset via a query where all records shown with the lowest item number record. I've tried this, but it's not working. DMin("ItemNo","Findings",[Findings].[ItemNo]) So for example, I have this in the table Any ideas how to fix it?

Thanks for the suggestions. I wasn't able to use either methods to be able to modify the original table. So I decided to convert the aggregate query into another make table query. Not the cleanest solution, but am able to create a new editable table with exactly what is needed. Thanks...

Yes this works, but I was trying to avoid an aggregate query in order to keep the table editable.

Trying to use the query criteria to show only a single row in a batch of records with the lowest item. Something like this Batch Item 001 001 001 01 02 03 002 002 002 02 03 04 003 003 003 02 04 05 So I'd like the results to show this Batch Item 001 01 002 02 003 02 Tried...

Yes I tried it. Just changed the SQL from UPDATE to UPDATE DISTINCTROW. But same thing happened. As for bloat, yes this is true. But a quick database compact fixes that. Plus the original data is from linked excel tables. I'll try to see why changing the SQL isn't working. But for now, at...

This is another case where the original data to be analyzed comes from a system driven Excel Spreadsheet. The data has all the relevant data, but it's filled with duplicate data. The output needs to be to another spreadsheet that uses pivot tables, charts, etc. It can all be done in Excel...

The Make Table query turns exactly what I'm trying to do. The only shortcoming is that it deletes the fields as well, not just the data from the fields. I have a few other fields in the temp table that I'd like to keep. Not a problem though as I can just add the empty fields to the query...

I have a table that has multiple duplicates. I'd like to run a "Group by" Totals query and then use an update query to update a temporary table with those results. But I get an error message, "Operation must use an updateable query". Is there a workaround for this or another way to do what...

I picked an ID field that repeats itself for simplicity. But it could very well be a PO number or a Received Date field. There will be many records of a SN that came in on a Received date. So I don't need to highlight any of those SNs. However, it that SN is repeated on a different received...

Yes this is the problem. I can't figure out how Access can differentiate these fields. Basically, it should look at all SNs that are identical and then only highlight SNs that have different IDs.

I'm trying to highlight a field with duplicate values in a continuous form but only when that field and another field are different. I've tried various combinations of the following code, but can't get it work DCount("*","TableName","[SN]='" & [txtSN] & "' AND [ID]<>" & [ID])>0 This is what...