Access database number format

[dhunt]

I need to export to an Access DB with prices of items with no $ sign and no decimals. This will be a "wholesale price that we disguise with additional numbers leading and trailing the wholesale price. (Leading 2 digits are the century, trailing 2 digits are current year.)

i.e., $24.99 should look like 20249911 Is there a format command that will work for this?

TIA,

DBNewbie

 

[rallcorn]

You could put a formula in Excel sheet to get the desired export results.
(I have the year 2011 in cell A1)

<TABLE style="WIDTH: 200pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=267 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>2011</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 152pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=203>Formula</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>$24.99</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">202499011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">=LEFT(A1,2)&A2*1000&RIGHT(A1,2)</TD></TR></TBODY></TABLE>

 

[dhunt]

Thanks, but I'm using QuickBooks POS, not an Excel spreadsheet. The program I'm using to print price tags (Label Link) uses "format" commands for the fields that are inserted into the label template.

Any ideas?

 

[xenou]

You should import the values in the proper format to Access either as text -- "20249911" -- or as long integers -- 20249911. You could also run an update query to make this change after importing.