Syndicate content

column

Text import wizard in Excel 2010 not coming up with .csv file

As I was working on a table extracted from a PDF file, I wanted to load that table in Excel. By default it would put all the fields in column A. Not good. So I put that in a text file, added a pipe at the end of each time and then join lines really quick (with vim, it's J to join lines.)

Now I had a nice .csv file, with fields separated by a pipe (|) character.

123|Something|More info|This "worked" before?!

Now, time to load that file in Excel. I tried that and it gave me everything cut in "strange" places but especially, no way to select the column separator. Why ...

Freeze Pan — How do you lock a row and/or column in a long Excel spreadsheet?

I have been wondering how to lock a few rows and/or columns on a side in Excel. I have seen that being done many times, but did not know how to do it myself.

In the Windows menu (Before Office 2007) and in the View menu (Since Office 2007) you will find an option named Freeze Panes. You have three selections:

  1. Freeze the selected top rows
  2. Freeze the selected left columns
  3. Freeze the selected top rows and left columns

If you just need one row and/or column, place your cursor in the A1 cell.

[toc hidden:1]

UPDATE using multiple tables

Today I wanted to copy a column from one table to another. One way is to go through the table and update the items one by one. The other way is to use the UPDATE order a copy the data as required.

The UPDATE command does NOT support such a feature by default and thus each database has its own syntax when it comes to how to make that work.

PostgreSQL uses an extra FROM between the SET and WHERE orders.

[toc hide:1]

MS-Access: INSERT INTO syntax error

Today I was creating a report and created a table with a column named 'Group'. It looked like it worked just fine so I moved on with it.

Then, at the point I wanted to insert data with a simple INSERT INTO ... statement, it broke. The statement would generate a syntax error. Yes. The simple answer is that GROUP is viewed as a keyword and thus when used as a field name it needs to be escaped (i.e. written between backward quotes: `...`). Not liking the need to escape a field name each time I use it, I just renamed the field which is even better.

The bad thing about GetRows()

In MS-Access you can create a Recordset using the OpenRecordset on a database.

Once you have that record set, you can get the rows with the GetRows() function.

It has one advantage: once the function returns the data remains consistent (it is in an array). The really bad part is... what is returned is an array. This means all the fields are now numbered instead of named.

Hiding columns in a Datasheet form

I have been looking for a way to hide some columns in a datasheet form.

The main reason to hide columns is to include data that you need whenever the user clicks somewhere, but that data is too technical to be shown. For instance, if you work with QODBC you have the ListID and TxnID to keep around but both look like GUID and thus are much better hidden from an every day user view.

The reason why I'm bringing this up is simple: whenever you have a field in a form that you want to hide, you click on it, go to the Format properties of that control and select Visible: No, I use that to ...

Editing of a MS-Access table

Whenever you want the end user to edit a table, you create a form that shows a set of cells. These are defined in your form Details area. Up to here, nothing special about that.

The fact is you cannot easilly access rows individually. That is, the TextBox, ComboBox, etc. in each row is considered the same whatever the row. So if you change the background color of a TextBox, all the TextBoxes of that column will be highlighted, not just the current row.

Per Row Highlighting in MS-Access Forms

Today, I found out that the current row in a MS-Access form is not so current.

Somehow, it can extract the data from the current row, but trying to change some parameters on the current box actually affects the entire column. This is because a ContinuousForm includes only one box per column and that box is repeated for each row.

Spaces in database column names

Most of the database systems I've worked with do not support having column names with a space.

Oracle and PostgreSQL accept double quotes for that purpose (i.e. "First Name").

MS-Access and MySQL accept back quotes (i.e. `First Name`)

Visual Basic accepts square brackets (i.e. [First Name])

Obviously, this is just plain craziness! One should just never use spaces in such a place since it completely breaks the object oriented capabilities of pretty much any sensical language.

Error: Too few parameters. Expected 1.

What a strange error!

You execute an SQL statement with the usual OpenRecordset() function in MS-Access. So as usual, you put one parameter to the function, the SQL order.

You have written 100 of those before and had no problems.

Today, the last one you wrote says:

Too few parameters. Expected 1.

You look at this latest statement and your older statements and you just plainly don't get it. There is one parameter, it is the SQL string and it looks just fine.

So? What's wrong?

Simple! The error actually means a field name in the SQL statement is wrong. Yeah! That's how you need

Syndicate content Syndicate content

Diverse Realty

Diverse Realty Team

Want a New Home?
Want to Sell Your House?

Call Alex at
+1 (916)
220 6482

Alexis Wilke, Realtor
Salesperson
Lic. # 02024063

Cory Marcus, Broker
Lic. # 01079165

     

Terms of Site Index

Find the page/content you are looking for with our index.