Limited field properties?

[Guest]

Hi,

I have created an SQL string which is fed into a query. I then want to
change the "Format" and "DecimalPlaces" properties of the field "Downloads".

However for some reason this field doesn't contain these properties. If I
run the follwing code it shows that I only have these field properties:

### Code to check field properties ###

For count = 0 To 30
Debug.Print count,
CurrentDb.QueryDefs("Custom_Sales_Query").Fields("Downloads").Properties(count).Name
Next count

### Result ###

1 Attributes
2 CollatingOrder
3 Type
4 Name
5 OrdinalPosition
6 Size
7 SourceField
8 SourceTable
9 ValidateOnSet
10 DataUpdatable
11 ForeignName
12 DefaultValue
13 ValidationRule
14 ValidationText
15 Required
16 AllowZeroLength
17 FieldSize
18 OriginalValue
19 VisibleValue
20 GUID

As it doesn't offer me "Format" or "DecimalPlaces" I assume it's not being
recognised as a numerical field. I've tried wrapping cdbl() around the field
so it knows it's a numerical field but this still doesn't work.

I can do it in the design view of the query but I need to do it in code
(which I obviously can't as it doesn't recognise these field properties).
Can anyone help?

Thanks very much in advanced!

 

[Guest]

If you are doing it in code, it would be something like:

strWhere = "Format(Nz([SomeValue],0), "#,##0.00") As TheValue"

 

[Guest]

Thanks for this. However this doesn't work - I don't think you can do this
in the WHERE statement. If I put this in the SELECT part it will work, but
it treats the number as text. So I get the right formatting but it's left
aligned and it's text. I really need it as a number? Any advice?

Many thanks!

 

[Guest]

Maybe a little more info would be in order. What is the data type of the
field, what type do you want it to be and how many decimals do you want?
What does it compare against, and are you using it in a calculation.

 

[Guest]

Sure, thanks.
I'm creating the query as an SQL string and there are two fields "Downloads"
and "Revenue" which I would like as numerical. I'd like both fields to be
standard format and Revenue to have 2 decimal places.
The SQL string is fed into a query, but I guess as the SQL is created from a
text string it doesn't know that these two fields should be numerical. I've
tried wrapping the fields with cdbl() but this doesn't work, nor does making
the property "Type" = "Number". The weird thing is that if I make this query
into a table the fields appear as number in the design view - but like the
query they don't have the "Format" or "DecimalPlaces" property available
using VBA. Although they do in the design view! This is frustrating because
I can change the "format" and "Decimal Places" properties in design view but
I need to do it in code, yet it doesn't think these properties exist in the
code!
As a work around using: Format(Nz(Sum([tbl_Union_Sales].[Final_Revenue]),0),
""#,##0.00"") looks like a good choice, but this formats the data left
aligned and as text. I really need it to be numerical!

Appreciate your time and help!