MS Access Handling Nulls In Query Formula
Hi all, I have this formula in Access: IIf([Init_Static_P] < 0 And (Nz([Adj_Static_P], -9999) < 0), "Resolved", "Not resolved" which keeps outputting an #Error when Adj_Static_P is null which will happen. I need the nulls in my dataset. Whenever Init_Static_P is < 0 and or Adj_Static_P is < 0, it works as it should outputting "Resolved" and vicer versa, however it does not work whenever there is any instance of Adj_Static_P as a blank value. Does anyone know how I can handle the nulls so that if the Init_Static_P is >0 and Adj_Static_P is null, then it would output "Unresolved", and if Init_Static_P is <0 and Adj_Static_P is null then it would output "Resolved" instead of #Error? Output should be as follows: Init_Static_P Adj_Static_P Result >0 Null "Not resolved" >0 >0 "Not resolved" <0 Null "Resolved" <0 < 0 "Resolved" Thanks in advance! <3
Removing duplicates from Access query
I have an Access database to catalog a collection. In the table "Sets", there is a column that "Material Type" that links to another table (Materials.ID). Material Type allows multi-selection to support cases where a set is made of two or more materials. The problem is that when an item has multiple Material Types, the item shows up multiple times in reports. For example: In the table Sets, item 292 has two Material Types (Metal and Recycled) When querying, item 292 shows up twice. This would make some sense if each result row shows a separate Material Type field (Metal in one, an Recycled in the other), but each result shows both values. Then, when I have a form based on the query, I get two duplicate items of 292: So, my question is: How do I get rid of the duplicate rows in the query? If the query only had one row, the form would only have one row. I've never been able to find any help or support articles that address this issue. Thanks for any assistance you can offer. Phil GardingMicrosoft Office 2019 Now Available – Comparing 2019 vs 2016 vs 365, New Features in Access & Excel
Microsoft Office 2019 is out! Microsoft started the roll-out today of Microsoft Office 2019 for Windows & Mac – with major updates to Access, Excel, Word, PowerPoint, Outlook, Project, Visio, and Publisher – to commercial volume license customers. Microsoft is following up with Office 2019 releases to consumers and other business customers, as well as SharePoint / Exchange / Skype / Project Server 2019 releases, in the coming weeks. Office 2019 provides a subset of features Microsoft has added to Office 365 over the past three years. As Office 2019 is a one-time release, Office 365 is still the better choice with not only far more features (Co-Authoring, etc) unavailable in Office 2019, but also far earlier access to them than on-premises, non-subscription Office 2019, etc. editions. Speculation has been that Office 2019 may be the last perpetual license (on-premises / non-subscription) release of Office, so that Microsoft can focus in on its Office 365 subscription offerings. However, Microsoft has responded in one case that there is likely to be one more perpetual license release after this one. Either way, Microsoft Office 2019 product pages even describe Office 2019 as a "one-time release" with Office 365 being needed to gain access to new features after that. It may also be that there are fewer editions available for Office 2019 than for Office 2016. Whether you move to Office 2019 or 365, it's suggested you don't delay doing so, as Office 2016 cloud support will be dropped in 2020, with Office 2016 installs barred from connecting to Microsoft's cloud-based services, including hosted email (Exchange) and online storage (OneDrive for Business), after Oct. 13, 2020. New in Office 2019 Word– text-to-speech, improved inking & accessibility, focus mode, translator, Learning tools (captions & audio descriptions), @ Mentions PowerPoint – Morph transitions, Zoom, SVG, 3D model, play in-click sequence, 4k video, @ Mentions Excel – Power Query (Get & Transform) enhancements, Power Pivot included with all editions, new functions & connectors, publish to Power BI, AI-driven Excel Insights for chart suggestions, new charts, @ Mentions Excludes Co-Authoring, new Data Types like Stocks, and some other new features only available in Office 365 Outlook –@ Mentions, Office 365 Groups OneNote – OneNote for Windows 10 (Modern App included with Windows) has replaced OneNote desktop app (though OneNote 2016 will be available via Volume License Install tool) All Office apps – Ribbon customizations and roaming pencil case Microsoft Access - including the many updates we've seen recently such as: Modern Charts New Linked Table Manager Dark theme Big Int Salesforce & Dynamics connectors Other Recent Developments with Microsoft Access It's especially exciting to see all the new features, growing user base and communities, new integrations, and development team responsiveness seen with Microsoft Access of late. Inclusion on the Office templates page Which I hope will become permanent soon SQL Server Migration Assistant (SSMA) updates ODBC and OLE DB driver updates – for optimized use and new feature support for SQL Server, Azure SQL and other back-ends databases Power BI support (via On-Premises Data Gateway) Considering On-Premises Data Gateway is shared with PowerApps, hopefully that means we may see PowerApps support too in the future New & growing Access conferences and user communities: New Access Developers' Day in Amsterdam DevCon in Vienna, AEK in Germany, UKAUG in UK, PAUG in Portland, Access Day in Redmond, Access Madrid in Spain Presence at Microsoft Ignite and other conferences Access User Groups (AUG) webinars and local chapters (Chicago, Denver, Madrid, Hertfordshire, etc.) Access now included in most Office editions Included in nearly all (besides Online-only) editions Access in Office 365 Home, Personal, Business, Business Premium, ProPlus, E3, and E5 editions Access in Office 2016 Professional and ProPlus editions With MS Access having been added to most Office editions, presumably it will likewise be available with most Office 2019 editions now too. It's great to see these features available to Office 365 subscribers (or even sooner if opt-in for Insiders program) now being made available to others with Office 2019, and I look forward to the many more new advancements with Microsoft Access and Office to come. Links to More Info about Office 2019 You can find out more about Office 2019 with the following articles, FAQs and product pages: Office 365 vs. 2019 Editions Office 2019 Commercial FAQ Microsoft's Office 2019 Announcement Office 2019 and Discontinuing of Office 2016 The Verge release article ZDNet release article Endgadget release article VentureBeat release article TechSpot release article -- Dan Moorehead Founder & Chief Software Architect PowerAccess (www.PowerAccess.net) "Empower Microsoft Access – with new Tools | VBA Framework | PowerGit | Power Query-like PowerSQL | VSTO-like .NET API | CodeGen | Excel Formulas & Functions | Consulting | Excel ➜ Access ➜ SQL Conversion Tools"
Project Management Access Fix?
Hi I have just begun using the template by Microsoft access that is for project management. I have posted below a screen shot of what I'm encountering. My access is showing a completed job under my project task tab. I only want this to show jobs that are active. Is there a way to fix this or filter it?
Validating that data was entered into a text Box
I have a Signin form (frmSignIn) with two Text Boxes asking for a UserName (txtName) and Password (txtPassword). The form also has two command buttons Enter (cmdEnter) and Add User (cmdAddUser). I would like to validate that data has been entered into the text boxes before processing the form. I have tried the following VBA Code in the BeforeUpdate event for the text Box. Option Compare Database Option Explicit Private Sub txtName_BeforeUpdate(Cancel As Integer) If txtName = "" Or IsNull(txtValue) Then MsgBox "You are a Dummy" Cancel = True End If End Sub This only works if I enter a value in the txtName Box and then backspace to erase the value. What am I doing wrong?
Microsoft Access database engine 2016 driver
Greetings! I'm Nicolas Fischer, I'm working with Delphi and Microsoft Access Database 2016. Today, to connect the application written in Delphi with the database, we need to install the Microsoft Access database engine 2016 driver. However, when installing the driver, it ends up conflicting with older versions of Microsoft Office, just as installing older versions of Microsoft Office ends up conflicting with the 2016 driver. Is there any way to use the Access database without installing the driver? Maybe with some specific DLL or component for the connection? Thank you.
A Problem with attached images in Access, Windows 10, Office 365
I have built an ACCDB database for my comics. I haven't yet made the DB relational. I was mostly working on getting my records into the database, and the basic record, which is long but almost all related to individual issues, consists of Publisher Code, Issue Name, Volume Number, Issue Number, copyright year, month of publication (like 01, 02, 03), Box number (where it's stored) and, as an attachment (not an OLE object) a picture of the cover. The database is large. Yesterday, I noticed a problem when I tried to attach an image to a record. I can almost complete the process, but at the "commit," I get a popup "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt." I have tried to "Compact and Repair" the database with miserable results. It creates a table (an MDB table, no less) with several system errors. I spent some time with a Microsoft Tech Support person a few minutes ago, but we didn't accomplish anything useful. To be complete, we opened MSAccess from the CMD prompt using "MSACCESS.EXE /decompile" (no problem), then opened the actual corrupt ACCDB file from the command prompt (no evident problem), but when I tried to Save As (new file name), the Save As process locked up after about 1,800,000 KB of processing, and I had to kill it. Any suggestions welcome. I have used OneDrive to try and roll back the corrupt file to a previous version (not too far back, because I've put in a lot of work this week!), but those older versions still seem to have the Compact and Repair problem.ADODB performance problem
Hi All, (Win10, Win11) To highlight a performance problem (with ADODB/ACCESS), on a heavy client, I wrote two small programs one in VB.NET (and VBA), the other in CPP/MFC. Using the same component (msado15.dll). This piece of code does nothing but connect to two ACCESS files (with a single table of 15,000 records), and via a recordset copy from one database to the other. Under VB.NET, it takes 1 second. Under CPP, on a machine that has the runtime access 2013 x64, it takes 9 seconds. (i spend 99% of my time in AddNew() )... Under CPP, on a machine that has the runtime access 2016 x64 OR Office365, it takes 20 minutes. (i spend 99% of my time in AddNew() )... The target computer must have Office365... I need an issue... PS : My two sln (exe/cpp/...) are available...There is no sensitive data... A short running test... |Nom de la fonction|Total \[unit, %\]|Self \[unit, %\]|Nombre d'appels|Module| |-|-|-|-|-||*+*D:\\users\\phili\\Downloads\\Test\_ADO\_Cpp\_VB\\test\_minimal\\x64\\Release\\test.exe \(PID : \)|115,73s \(100,00*%\)|0ns \(0,00*%\)|0|Plusieurs modules| |\|*+*\_\_report\_gsfailure|115,73s \(100,00*%\)|9,20?s \(0,00*%\)|1|test| |\|\|*+*\_\_scrt\_common\_main\_seh|115,73s \(100,00*%\)|13,20?s \(0,00*%\)|1|test| |\|\|\|*+*main|115,73s \(100,00*%\)|654,83ms \(0,57*%\)|1|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947bc40|104,82s \(90,58*%\)|104,82s \(90,58*%\)|6215|msado15| <= AddNew() !!! |\|\|\|\|*-*\[Native\] ucrtbase.dll!0x00007fff373ccad0|9,31s \(8,05*%\)|9,31s \(8,05*%\)|1|ucrtbase| |\|\|\|\|*-*ADODB::Connection15::Open|413,29ms \(0,36*%\)|8,80?s \(0,00*%\)|2|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947c010|101,83ms \(0,09*%\)|101,83ms \(0,09*%\)|2|msado15| |\|\|\|\|*-*printf|96,90ms \(0,08*%\)|157,30?s \(0,00*%\)|214|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947d3d0|91,69ms \(0,08*%\)|91,69ms \(0,08*%\)|74568|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947d380|50,46ms \(0,04*%\)|50,46ms \(0,04*%\)|74568|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe894d8fc0|30,92ms \(0,03*%\)|30,92ms \(0,03*%\)|149136|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947bfc0|28,68ms \(0,02*%\)|28,68ms \(0,02*%\)|6214|msado15| |\|\|\|\|*-*\[Native\] combase.dll!0x00007fff39403f10|26,94ms \(0,02*%\)|26,94ms \(0,02*%\)|5|combase| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe89513fc0|17,39ms \(0,02*%\)|17,39ms \(0,02*%\)|149137|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe894c1480|15,11ms \(0,01*%\)|15,11ms \(0,01*%\)|1|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947c230|14,46ms \(0,01*%\)|14,46ms \(0,01*%\)|149137|msado15| |\|\|\|\|*-Native\]msado15.dll!0x00007ffe8947d2b0|11,32ms\0,01*%\)|11,32ms\0,01*%\)|149136|msado15| RegardsIndexing and Duplicates
Hi I don't need help resolving a problem on this occasion, but I would appreciate some help in understandingIndexing and Duplicates. I have a main Audits table plus 7 clause tables. They are related through the clause AuditID. The FK AuditIDs in the main table I would have thought should be indexed with no duplicates (that's what makes sense in my head). But if I do that, the query becomes not updateable. I would have thought that having no duplicates in the main table was the correct thing to do. Or is there something I am completely missing here? Thanks.
