Formatting a query field using code?

[Guest]

Hi,

I need to set the format property of a field in a query using VBA but I'm
not sure how to do it? I've tried this...
CurrentDb().QueryDefs("Custom_Sales_Query").Fields("Downloads").Properties("Format").Value = "Standard"

....but it doesn't seem to do anything? As you can see, the field
"Downloads" is the field I am trying to change. I want it to be displayed as
standard and no decimal places. I need it to change the query properties as
I'm not displaying the data on the form. As I can change the properties
manually in the query, I'm assuming I can also do it using code, but I code
above doesn't do anything (note - it doesn't actually crash, it just doesn't
format the field). I also need to make it 0 decimal places.

Really appreciate any help!

 

[Graham Mandeno]

Hi Ben

The help for the "Standard" format says: "Display number with thousand
separator, at least one digit to the left and two digits to the right of the
decimal separator."

Have you tried "0"?

 

[RoyVidar]

Hi,

I need to set the format property of a field in a query using VBA but
I'm not sure how to do it? I've tried this....
CurrentDb().QueryDefs("Custom_Sales_Query").Fields("Downloads").Properties("Format").Value
= "Standard"

...but it doesn't seem to do anything? As you can see, the field
"Downloads" is the field I am trying to change. I want it to be
displayed as standard and no decimal places. I need it to change the
query properties as I'm not displaying the data on the form. As I can
change the properties manually in the query, I'm assuming I can also
do it using code, but I code above doesn't do anything (note - it
doesn't actually crash, it just doesn't format the field). I also
need to make it 0 decimal places.

Really appreciate any help!

I don't see why it's important to use the format property of a field in
a query, as you shouldn't display this to anyone anyway. Then the
important part, is to format it when you display it, say in a form or
report (control properties).

The exception, is when you actually need a specific format (for
instance
for export, or listboxes etc), where in this case, you could use either
the format function or the Int/Fix function (for the latter, check out
how they behave on negative values).

select field1, format(field2, "#"), int(field3), ...

Else, something along these lines seem to work on my setup.

dim db as dao.database
dim qd as dao.querydef
set db = dbengine(0)(0)
set qd = db.querydefs("Custom_Sales_Query")
qd.fields("Downloads").properties("Format").Value = "Standard"
qd.fields("Downloads").properties("DecimalPlaces").Value = 0

Remember though, that the contents of the field, might still be
123.456789123, you've only formatted it.

 

[Guest]

Thanks for the prompt response.

Unfortunately this still doesn't work. Bizarre!?

You're right - the data should only be displayed on a form/report. However
the reason I was using a query is because I have a new SQL string that's
built based on selections in a form. It's not just the conditions that
change, the selected fields do to. I couldn't get the form to correctly
display fields based on the rebuilt query. It seemed to struggle with
putting new fields in or taking old fields out. It works fine by using a
query in a form except the formatting.

If you know how to get the form to alter what fields it displays based on an
ever changing query then I'd really appreciate your advice!!

Many thanks!