The Linux Page

column

Add or subtract to a Time field in LibreOffice

Today I was working on a spreadsheet where I added a time for a calendar type of sheet.

What I wanted to do is have a column where time increases without having to type each time manually.

So I needed to add 15 or 30 minutes to the first time.

Say my first time is 7am, it appears as 07:00:00 AM.

In the next row (B4), I wanted to do something like:

=B3+"15 minutes"

The fact is that LibreOffice saves time and date as a double number where 1.0 represents 1 day.

So to add just 15 minutes, you need to divide your number by the number of minutes in a day:

=B3+15/60/24

Or if ...

Can I create an INDEX to accelerate a SELECT MIN(IF(...)) in MySQL?

I have a table with a few columns, two of which are integers, one of which can be NULL in which case the other integer is to be used in my query.

CREATE TABLE my_table (col1 INTEGER NOT NULL, col2 INTEGER);

If `col2` is NULL, then use `col1`. As is, there is how my query looks like:

SELECT MIN(IF(col2, col2, col1)) AS smallest FROM my_table LIMIT 1;

In other words, I need to get the row with the smallest integer, but if one column is NULL, I want to use the other column value.

On a very small sample, the query is still very fast, but the real version is not unlikely to have thousands ...

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 ...

Automatic Datasheet Columns Width with MS-Access VBA

You may have noticed that whenever you create a Datasheet you can double click on the right edge to resize the column to the widest text currently defined in that column.

This is neat but if I create a table with 10 columns, that's work. Especially if I need to reload data in that table each time I use it, 10 times an hour. (i.e. 100 x double click would definitively kill me!)

Of course, you may not need to resize all 10 columns each time, but wouldn't it be neat to have the columns width auto-adjust with the data you put in that Datasheet table?!

I think it would be!

The solution

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.

Syndicate content Syndicate content

SMS From Me Logo

SMS From Me

To automatically start one on one conversations with your online leads.

     

   

Terms of Site Index

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

  • git
  • jQuery
  • row
  • test
  • 2

    Computers use binary, thus the number 2 is very important in computing. You will find it everywhere.