How to set a runtime datatype in MS Access using Design mode

Question

I've been working on MS Access. My students have to create a runtime field but I can't for the life of me determine how to set the datatype without going into the SQL itself. Is it possible to use the design view to set this up something like

Somefrigginfield (Set data type here e.g. Currency) (Decimal places to round here) : [Somefieldname] *1.10

I know in SQL it would be something akin to

Select ID as int expr1

It's been a while but you get the idea

(FYI if this is in the wrong forumn of stack please let me know where to shoot it)

what do you mean by runtime field? you have a textbox and you would like to change the format? tb.format = currency? or are you trying to declare a decimal variable? dim xyz as double? — Krish, Sep 01 '14 at 09:33
When designing queries in MS Access there is a design mode feature. I would like to continue to use this to make it easy for students so I'm wondering if there is a way to do a runtime field in this way with parameters. — Kevrone, Sep 01 '14 at 09:35

Krish · Accepted Answer · 2014-09-01T09:46:28.713

2

Open the query in design mode: in the design view fields just type

CalcValue : [Fieldname] * 1.0

in SQL it would look like : SELECT ([FieldName] * 1.10) as CalcValue

or:

calcValue : Format([property_Rent],"Currency")

OR:

enter image description here

edited Sep 01 '14 at 09:46

answered Sep 01 '14 at 09:41

Krish

5,917
2
14
35

If you read my comments I know this. What I'm asking for is to be able to set the data type and rounding in the design view. – Kevrone Sep 01 '14 at 09:43
@Kevrone: ahh okay.. just open the property sheet view for the query and click the fields you want to format and set the formats: – Krish Sep 01 '14 at 09:47

score 2 · Answer 2 · edited May 23 '17 at 10:33

2

Perhaps you mean:

SELECT aTable.aNumber, 
       CCur([aInteger])/3 AS [Currency], 
       Round(CCur([aInteger])/3,2) AS Round, 
       Format(CCur([aInteger])/3,"Currency") AS Format
FROM aTable;

In design view

query design

Giving the results below, you will note that null fields / columns give an error. The Nz function is probably the easiest way to avoid this.

enter image description here

How to set a runtime datatype in MS Access using Design mode

2 Answers2