Access first date of month and last date of month

Posted on March 14, 2016 by Wyman W

This Access tutorial explains how to find first date of month, last date of month using Access Expression and VBA Function. You may also want to read: Excel first date of month and last date of month Access first date of month To find first date of month in Access, first we have to understand DateSerial, you can use it to combine the year, month and day to a date. Syntax of Date Function DateSerial( year, month, day ) Example Formula Result Explanation DateSerial(2012,1,...

Get exchange rate using VBA Access Function

Posted on January 18, 2016July 24, 2017 by Wyman W

This tutorial demonstrates how to create a VBA custom function (UDF) in Microsoft Access in order to get the real time exchange rate. You may also want to read Get Foreign Exchange Rate in Excel Get exchange rate using VBA Access Function Yahoo Finance is designed in the way to facilitate you to get data. You can establish a connection to Yahoo and directly get the exchange rate using User Defined Function. Open Access, press ALT+F11 > Tools > References > enable "Microsoft W...

Access VBA DoCmd.TransferText Method

Posted on January 10, 2016June 12, 2017 by Wyman W

This Access VBA tutorial explains how to import and export Access Objects to text using DoCmd.TransferText Method. You may also want to read Access VBA Export all Queries to Excel Access VBA import txt using DoCmd.TransferText Method Syntax of DoCmd.TransferText Method DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage) Name Required/Optional Data Type Description TransferType Optional AcTextTransferType T...

Microsoft Access VBA Export all Query to Text File

Posted on January 10, 2016January 10, 2016 by Wyman W

This Access tutorial explains how to use Access VBA to export all Query to Text file. You may also want to read: Microsoft Access Export all Queries to Excel Access VBA DoCmd.TransferText Method Export Access Objects to Text (non VBA) There are two kinds of export that we can use to export an Access object to Text file. Assume that we want to export Query1 to Text. Right click on the Query > Export > Text File This page has a check box Export data with format...

Microsoft Access Export all Queries to Excel

Posted on January 8, 2016June 1, 2016 by Wyman W

This Access tutorial explains how to export all Queries to Excel. You may also want to read: Access VBA auto generate mass report by group to Excel Microsoft Access Export all Queries to Excel Assume that you have created three Queries in Access database, all you want to export them to Excel. Press ALT+F11 > insert a Module > paste the below code Public Sub export_Excel() Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb() For Each ...

Microsoft Access UNION and UNION ALL

Posted on January 6, 2016October 26, 2016 by Wyman W

This Access tutorial explains how to use UNION and UNION ALL in Access Query. You may also want to read: Excel VBA Union Method UNION and UNION ALL in Access UNION and UNION ALL are used to combine two Query results vertically into one Query, therefore both Queries require to have the same number of fields and same data types. When two Queries are combined, there could be rows of record that are exactly the same (duplicates). UNION removes all duplicates while UNION does not remove an...

Run Access Macro using RunCode

Posted on January 2, 2016September 16, 2017 by Wyman W

This Access tutorial explain how to run Access Macro using RunCode. You may also want to read: Run Excel Macro from Access VBA Run Access Macro Assume that you have already written a Sub Procedure helloWorld(). To run the Macro, you can press ALT+F11 and run the Procedure by pressing F5. Alternatively, go back to the Access Windows, double click on the Module on the left hand side and run the Procedure by pressing F5. If you are writing a Macro for users, they pro...

Add Auto number in Access Query

Posted on January 2, 2016September 21, 2017 by Wyman W

This Access tutorial explains how to add auto number in Access Query using Access VBA Function. You may also want to read: Sort Data in Access Report Add sorting in Access Table field Add Auto number in Access Table In Microsoft Access, go to Design View of a Table and define the Data Type of a field as AutoNumber Go to data view of the Table, each row of data is assigned a sequence number in ascending order. However, we cannot add the AutoNumber Data Type in Q...

Excel delete blank rows and blank cells before import into Access

Posted on December 15, 2015 by Wyman W

This Access tutorial explains how to delete blank rows and blank cells in Excel before import into Access. You may also want to read: Excel Delete Blank Rows Import Chinese CSV to Excel Import Excel File into Access When you import an Excel file into Access, you may come up with several issues regarding Excel blank cells. Excel blank cells are not considered as Null value in Access Excel blank rows after the last record are also imported Excel blank columns after the last column a...

Access Excel extract file name from file path

Posted on December 7, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to extract file name from file path using VBA and without VBA. Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Excel extract file name from file path Assume that you have a file path such as C:\Program Files\Google\GoogleToolbarNotifier\a.jpg If you want to extract file name a.jpg from the path in Excel, first we need to think about how to de...