How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?

MEMO / TEXT / VARCHAR(>255) 
 
Depending on the version of your MDAC driver, and the database you are connecting to, these columns can either (a) not show up at all, (b) only show up the first time they're called, or (c) cause 'Unspecified Error', 'Exception Occured' or 'Errors Occurred' runtime errors, if the following recommendations are not observed: 

• Avoid SELECT * notation; NAME your columns in a list, and name the offending column(s) LAST.
• Assign the value of the column to a variable IMMEDIATELY, and only use this variable from that point on.
• Make sure your MDAC drivers are most current (MDAC Download Page).
• See KB #200124 and KB #175239

Another problem people have is retaining line breaks from a textarea, once the value is returned to the screen. Access and SQL Server DO store this information, it's just not in a format that a browser's HTML engine understands. You need to replace line feeds and carriage returns with HTML tags. 
 
Here is some example syntax: 
 

<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      sql = "SELECT idcolumn,intcolumn,memocolumn FROM table"      set rs = conn.execute(sql)      do while not rs.eof          memocolumn = rs("memocolumn")          response.write(replace(memocolumn, CHR(10), CHR(10) & "&nbsp;<br>"))          rs.movenext      loop  %>

 

---

Hyperlink, Currency, MONEY 
 
Many people assume that, because Access can understand that a hyperlink column should be underlined and clickable and that a currency column starts with a dollar sign and is followed by two decimal places, the browser should be able to as well. So they write syntax like this: 
 

<%      response.write(rs("hyperlink_column"))       response.write(rs("currency_column"))   %>

 
And expect the browser to automatically wrap the hyperlink in an anchor tag, and format the currency column appropriately. Unfortunately, this isn't how it works. HTML doesn't know what a hyperlink is, unless you tell it to use an <A> tag. Similarly, HTML doesn't automatically format your numbers and assume you want thousands separators and dollar signs. 
 
DO NOT use the hyperlink datatype in Access. This is designed to allow users to click on links in the GUI. It is NOT meant for presentation-layer formatting (e.g. HTML). Use a VARCHAR column for hyperlinks. I would also recommend using a numeric data type with two decimal places for currency; in SQL Server, use the MONEY datatype. storing dollar signs and #link# values in the table aren't going to help you in HTML, and only add unnecessary storage size to your tables. 
 
Based on that, to format these values in your HTML, you need to do something like this (from Access)): 
 

<%      hl = rs("hyperlink_column")      response.write "<a href='" & hl & "'>" & hl & "</a>"      curr = rs("currency_column")      response.write formatcurrency(curr,2)  %>

 
To get a hyperlink formatted properly right out of SQL Server, you can use: 
 

SELECT      '<a href='+hyperlink_column+'>'+hyperlink_column+'</a>'      AS hyperlink_column      FROM table

 
To get a money value formatted properly right out of SQL Server, you can use: 
 

SELECT      '$'+CONVERT(VARCHAR(100), money_column, 1)      AS money_column      FROM table

 
Some people have reported that this doesn't properly insert commas (I assume it has to do with collation or regional settings on the server). So, to force it using a bunch of wacky string manipulation, you can try something like this: 
 

SELECT      '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,money_column),1)),1,100))      AS money_column      FROM table

 
Please let us know if this still doesn't work in your environment.