Access Archives - Page 5 of 6 - Technology Solutions Provider

Access VBA: How do I delete all records in a table?

========= QUESTION ---------How do I delete all records in a table using VBA? ========= ANSWER ---------Simply execute a SQL statement as follows: DELETE * FROM NameOfTable; ========= EXAMPLES ---------Docmd.RunSQL "DELETE * FROM NameOfTable;" ========= APPLIES TO / KEY WORDS ---------Microsoft Access VBA SQL Clear ========= REF ---------http://www.access-programmers.co.uk/forums/archive/index.php/t-81194.html ---http://www.anysitesupport.com/access-vba-how-do-i-delete-all-records-in-a-table/ http://anySiteHosting.com

By AdmAny9348|2017-12-01T23:47:50+00:00April 26th, 2011|Access, Computers, Documentation, Microsoft, VBA|Comments Off

Access: Handling Null Values

========= QUESTION ---------Microsoft Access doesn't handle null values very well when doing calculations, etc... Is there a way to check for a null value, and simply flip it to zero, or another entry? ========= ANSWER ---------Use the NZ() function Nz ( variant, [ value_if_null ] ) ========= EXAMPLES ---------Nz(Data,0) Was: StudentApprovals: IIf([StudentApproves] Is [...]

By AdmAny9348|2011-04-25T14:38:12+00:00April 25th, 2011|Access, Computers, Documentation, Microsoft|Comments Off

Access: Round Function

========= QUESTION --------- How do I round a number in the Query output in Microsoft Access ========= ANSWER --------- Round(Expression,[Decimal_Places])Expression is the number you wish to roundDecimal_Places is the number of decimals you wish to round to ========= EXAMPLES --------- Round(Goals) Round(Goal,2) Round(153.38,1) = 153.4 ========= APPLIES TO / KEY WORDS --------- Microsoft Access Functions [...]

By AdmAny9348|2011-04-25T13:43:49+00:00April 25th, 2011|Access, Computers, Documentation, Microsoft|Comments Off

Access: Application best practices

=========NOTES---------Create a form to house the controls (ControlPanel) Create buttons on the form and a CurrentStatus text box Write VBA Code tied to the buttons to update the CurrentStatus text box and to execute Macros that execute Queries ---http://www.anysitesupport.com/access-application-best-practices/

By AdmAny9348|2017-12-01T23:47:51+00:00April 12th, 2011|Access, Computers, Documentation, Microsoft|Comments Off

Access VBA: How do I display a field value?

=========EXAMPLES---------Set rst = CurrentDb.OpenRecordset("Parms")EOW = rst.EOWDateMe.CurrentStatus = "EOW Date set to " & EOW & vbcrlf rst is simply a name to hold your recordset where you are opening the table ParmsEOW is simply a name to hold the field you are querying ---http://www.anysitesupport.com/access-vba-how-do-i-display-a-field-value/

By AdmAny9348|2017-12-01T23:47:51+00:00April 12th, 2011|Access, Computers, Documentation, Microsoft, VBA|Comments Off

Access: The database has been placed … opened or locked

=========ERROR / MESSAGE---------The database has been placed in a state by <user> on <machine> that prevents it from being opened or locked. =========SCENARIO---------You are pasting a table, query, form, etc from Access Database file to another, and this error pops up stopping you from pasting. =========NOTES---------Standard answer:This error occurs when the new passive shutdown/connection control [...]

By AdmAny9348|2017-12-01T23:47:51+00:00April 12th, 2011|Access, Computers, Documentation, Microsoft|Comments Off

Access: Format fields

=========EXAMPLES---------Phone Number:If numbers stored as text:Format([field],"@-@@@-@@@-@@@@") If numbers stored as numbers:Format(2127851212,"(000)000-0000") ---------Format([Store],"0000") Input Mask for Table Field:!(999)000-0000 --------- ZIP: IIf(Len([ZipCode])<6, Format([ZipCode],"@@@@@"),Format([ZipCode],"@@@@@-@@@@")) ---http://www.anysitesupport.com/access-format-fields/

By AdmAny9348|2011-04-12T09:45:43+00:00April 12th, 2011|Access, Computers, Documentation, Microsoft|Comments Off

How do I query the top X of something?

=========QUESTION---------I have TABLE-A with 19000 records in it.Each record contains REGION, DISTRICT, STORE, NAME, and that person’s SALESHow do I find the top sales per Region, or per District, or per Store, etc... =========ANSWER---------OPTION 1 All it takes is three simple MAKE TABLE* queries: In this example, we will find the top SALES per [...]

By AdmAny9348|2017-12-01T23:47:51+00:00April 7th, 2011|Access, Documentation, Microsoft|Comments Off

VBA: Check if table exists

=========QUESTION---------In Microsoft Access 2007, using VBA, how can you check to see if a table exists? =========EXAMPLES---------On Error Resume Next --------- Public Function dbsTableDelete(DBName) Count = 0 tblNotFound = False Do Until tblNotFound If DBName = dbs2020.TableDefs(Count).Name Then dbs2020.TableDefs.Delete (DBName) Exit function End If Count = Count + 1 'Determine if we are at the [...]

By AdmAny9348|2017-12-01T23:47:51+00:00April 7th, 2011|Access, Computers, Documentation, Microsoft, VBA|Comments Off

Consolidate multiple Query Results into a single datasheet

=========REF: http://office.microsoft.com/en-us/access-help/combine-the-results-of-several-select-queries-by-using-a-union-query-HA010206109.aspx#BM2a http://www.databasedev.co.uk/union_query.html =========NOTES / WARNINGS---------Union queries drop out duplicate records by default! If you want all records, including duplicates, you need to include the term ALL after the term UNION Example:<SQL from one Query>UNION ALL<SQL from another query>UNION ALL<SQL from another query> =========HOW TO---------Create the select queries in Design view first, and then combine [...]

By AdmAny9348|2017-12-01T23:47:52+00:00March 30th, 2011|Access, Computers, Microsoft|Comments Off