Posts

Showing posts with the label Access

MS Access -- Run-time error '3464'

Being that I don't use MS Access often, I always look for examples of my prior work to assist me with my new problem. Today I ran into this error, Run-time error '3464': Data type mismatch in criteria expression , which was a good reminder. When writing a VBA query, Text needs wrapping in quotes, Dates need wrapping in hashes, and numbers need neither of these. If you get it wrong, you'll get the 3464 error message.

Concatenate and Combine Access Rows

I was working with a dataset in Access, where I had a single field that was different to an otherwise exact data row. I wanted to merge these rows, so I had a unique record; I wanted the single field of different data to be concatenated together, as to not lose the data. I was able to create a VBA function that I then called from a query to perform the merging of rows. It does have one shortcoming -- if you have duplicated data in the field you are concatenating, it may miss it and add it again. Here's what I mean: Removed Duplicates: Data order a, a, a, b, c -- output = a, b, c Creates Duplicates: Data order a, b, a, c, a -- output = a, b, a, c, a Note that given the time/effort, you could improve my function to remove all duplicates. Here's the function. Press Alt-F11 to get to the VBA Editor. Public Function Concat(email As String) As String Dim rs As DAO.Recordset Dim strSQL As String Concat = "" strSQL = "SELECT [Some ID] FROM MyTable WHERE [Email Address] =

Always have expanded menus in Office apps

Image
I just got a new laptop and was reminded of a common annoyance in MS Office products. The menus never fully display immediately -- you either have to click the arrow at the bottom of the menu or wait a few seconds. There is actually a fairly easy way to keep them expanded all the time. Right-click any toolbar Select Customize Check "Always show full menus" That's it.

Convert date format of CSV and import to MS Access

Image
Similar to my prior post on Convert Date/Time Format with MS Access , this explains how to take a date in dd/mm/yyyy format and convert it to mm/dd/yyyy , so that MS Access will recognize it. It takes just a few easy steps: Assume you start with your data in CSV format, the first step is to Import the data into Access ( File > Get External Data > Import... ). When importing the date field, import it as text, therefore loading all the data into your database. Create a Query to convert your date data and change from text to date format. From Design View , add all the fields except the field with your date data that you want converted. Insert a new field , and add ( Build... ) the following formula (see explanation and picture below): NEW_DATE: CDate(Mid$([DATE],4,3) & Left$([DATE],3) & Right$([DATE],4)) Run the query to validate that the formula is entered correctly. Once verified, change the Query Type to a Make-Table Query . Give it a name Run the query Confirm the num

Convert Date/Time Format With MS Access

I haven't had to use MS Access in 7 or 8 years, so recently when I had to do some simple data conversion, it took me longer than it should to figure out how to best accomplish the task. I needed to run some reports against some Salesforce.com data, and the data set was too large for the built-in report tool to handle, so I decided to import the data into Access to perform the reporting. I quickly learned that the date/time format from Salesforce.com is not in the same format as Access. The solution is actually quite easy... transform the data when you import it. I began with my Salesforce.com data in the form of a CSV file (too big for Excel). Here are the steps I used: Link to the CSV data source Create a Make Table Query against the CSV datasource, selecting all data columns -- of course you only need to select the columns that you want to import Replace every date/time field with a forumla that parses the format and writes it in the format Access will accept. Salesforce.com fo