Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

SQL to VBA

Recently, I saw a post regarding moving a Query (SQL) to VBA. I remember I saw something somewhere that automated this task but could not remember where. Well, after a little surfing of my favorite sites I found it… Copy SQL statement from query to VBA by Allen Browne. And, because I couldn’t leave well […]

 929 total views

Remove Carriage Return from imported Excel Spreadsheets

You import a spreadsheet and you noticed in some of your fields you have a little box with a question mark in it (see image below). This is a Carriage Return (Alt+Enter in Excel).

To remove create an UPDATE query and run against the offending field…

UPDATE YourTable SET FieldContainingCarriageReturn = […]

 5,497 total views

UNION Queries

A UNION query takes data from two (or more) tables and/or queries and makes it appear as if it is from one source, i.e…

SELECT FieldName, FieldName FROM 1stTableOrQueryName UNION SELECT FieldName, FieldName FROM 2ndTableOrQueryName UNION SELECT FieldName, FieldName FROM 3ndTableOrQueryName;

A few points…

1. UNION queries are READ ONLY, no edits allowed!

2. In […]

 468 total views

UPDATE Query Samples

Simple UPDATE query…

UPDATE YourTable SET YourTable.YourField1 = "Your Value";

Update a field in your table with values from another field in your table…

UPDATE YourTable SET YourTable.YourField1 = [YourField2];

Update a field based on a field on your form… Note: The form must be open to perform this action!

TEXT

UPDATE YourTable SET YourTable.YourField1 […]

 284 total views