Access Query Formula

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
My source document is an excel spreadsheet uploaded in Access (tblRicoh). This Excel spreadsheet is used to send to our printer to have checks printed. There is a field called Amount (represents $ of check) and a column called Amount Spelled Out. This is exactly what it says . . . it spells the dollar amount in the Amount column spelled out (like any printed check), the code for example is =spellnumber(F2) and returns a value of #Name ? This is required by the printer's merge template.

In Access, is there a way to insert an extra column in a query and duplicate this formula so when the query results is exported has incremental rows for =spellnumber( ) and the #Name ?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
Hi Nancy,
Yes there is!
Within your access query, in an empty field you can enter a virtual field. For this you can name with any name doing so:
Name: Formula
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
Just to clarify for nancybrown, what precedes the : is the name of the aliased field. Maybe neither of you would use the word "Name" for this or any other Access object, but just in case, I'll point out that it's a reserved word and should not be used anywhere other than when referring to the Name property of an object. For a great list, see
http://allenbrowne.com/AppIssueBadWord.html#N

Any expression that returns #Name as a result is an error that probably isn't going to help.
 
Last edited:

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
Just to clarify for nancybrown, what precedes the : is the name of the aliased field. Maybe neither of you would use the word "Name" for this or any other Access object, but just in case, I'll point out that it's a reserved word and should not be used anywhere other than when referring to the Name property of an object. For a great list, see
http://allenbrowne.com/AppIssueBadWord.html#N

Any expression that returns #Name as a result is an error that probably isn't going to help.

You're correct Micron!
But what I wanted to exemplify with the word "Name" is the name of the field.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
I suspected as much but wasn't sure. What I do in these cases is something like MyFieldName:Expression or AliasName:Expression. Anything but a reserved word if at all possible. That way I won't contribute to other problems if my suggestion is taken literally. It's just the way I try to post.
 

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
I suspected as much but wasn't sure. What I do in these cases is something like MyFieldName:Expression or AliasName:Expression. Anything but a reserved word if at all possible. That way I won't contribute to other problems if my suggestion is taken literally. It's just the way I try to post.

You are correct one more time!
Apologize me.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top