Ms Access – Percentage Field Formatting

A quick tip to fix what shouldn’t be a problem!

Occasionally, Access can act rather confusingly. I ran into a typical example early this morning, when I was trying to get Access to allow entry of percentages correctly. I’ve done this about one thousand times before and probably solved this problem one thousand times as well, but every time it happens it causes me to scratch my head (mostly in disbelief).

What happens is this: You set up a field in a table, designate it to be formatted as percentage. Right OK – you go to your form design, set up your input fields, and then try to enter a number as a percentage, and notice that it keeps rounding to 100% or 0%. Frustration ++.

Back to table design. The problem here is that you have to correctly set the field data type as well as the data type format. However, the data type is not integer, double, or even decimal – it is usually single. Once you set that on your percentage fields, you can enter data in decimal format (i.e. 0.5, 0.25 etc etc) and it will show up in its proper percent format.

I know this is shouldn’t be a problem but, amazingly, people get caught by it all the time, and there doesn’t seem to be clear answers on the web. Hopefully this tip does something to addressing this issue.

Useful Access Links

In the absence of any real content, I’ve decided to post a few Access and VBA related links here. Some are rather general, some are rather specific. Regardless, they’ve been floating around in a text file called ‘useful stuff to look at’ on my computer for a while. So why not share the love?

http://www.vb123.com/toolshed/02_docs/classyforms.htm – Forms as Class Modules
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html – “Not Another Access Page” Albert D. Kallal page on random Access related tips. The section on implementing simple mail merges is recommended.
http://www.tek-tips.com/faqs.cfm?fid=5010 – Looping through Form Controls.
http://www.fontstuff.com/access/ – Martin Green’s Office Tips. Great section on Access.
http://www.allenbrowne.com – Allen Browne extrememley valuable Access page.
http://www.applecore99.com/tbl/tbl012.asp – Alternatives to Autonumbers
http://www.vb123.com/toolshed/00_Docs/buildwizards.htm – Building your own Access Wizard interface
http://www.groupacg.com/ – Ms Access Developer Tools
http://www.oreilly.com/catalog/vbcnut/chapter/ch05-tv.html – Some information on the TreeView Control that can be used in Access. I will be writing a detailed article on how to use a treeview control to drive your Access user interface.

More to come soon….[and be included in my general ‘Access links’ to arrive shortly on the navigation menu of this site]

Running SQL from Visual Basic Code – Apostrophe problems!

I recently encountered a problem that I’ve actually encountered millions of times before – trying to run an SQL string from VBA code which makes use of double apostrophe’s (“”). Naturally, VBA uses these to break from a string literal, but pure Jet-SQL uses them for functions like Format() and Nz().

So if you do something like this:

strSqlCode = “Select format([field1], “Currency”) from table1″
db.execute strSqlCode

You will get a nice friendly Access message indicating that your SQL is faulty. Surely that ain’t the case!

There are several different solutions and here is one I find the most handy – using chr$(34)!

Reformat your SQL like this:

strSqlCode = “Select format([field1], ” & Chr$(34) & “Currency” & Chr$(34) & “) from table1”
db.execute strSqlCode

And Viola!

Ms Access Tips

Hello valued reader!

Over the years, I’ve had the pleasure of doing quite a bit of work in Ms Access, and also in Visual Basic for Applications. On countless occasions I’ve benefited from the work of countless others who toil in this Rapid Application Development suite when I’ve encountered a hitch. I’ve always wanted to ‘give back’ some ideas in regards to my Ms Access experiences.

Therefore, I’ve decided to put some effort in putting up a few ‘tips’ or ‘shortcuts’ is an easy to access manner (no pun intended). These will be sometimes a few generic code snippets, sometimes a diatribe on things that can be done, or could be done, with Access. So expect some soon.

Regards,
Darragh