Properties change in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am searching through the discussion for a while now, but could not find
the answer to my question:

I have a aggregate query on a table - summing up some field with numbers.
The format of the field in the source table is set to standard, so when I run
a normal report I got the field in the right format ( 1,234.56). But in case
of the sum query I got the unformatted: 1234.5678

I know that it is possible to get into query design view, right click on the
field and change the format. BUT I create the query with CreateQueryDef in
VBA and I need to set the format in VBA. Is that possible? Or really MS left
out something from VBA? So far I had the impression that you can do
everything in VBA.

Thank you for your answer in advance!
Br, Kolos
 
Hi.
BUT I create the query with CreateQueryDef in
VBA and I need to set the format in VBA. Is that possible?
Yes.

Or really MS left
out something from VBA? So far I had the impression that you can do
everything in VBA.
Click to expand...

Almost everything that can be done with the GUI can be done in VBA.
However, writing code that duplicates GUI functionality is usually the less
efficient method, so if you can do it while using the GUI, you are usually
better off.

Here's an example of creating a new query and adding the "Standard" Format
Property on the numeric column, Total:

' NOTE: Requires Reference to DAO Object Library.

Public Sub createQryWFormat()

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim sqlStmt As String

sqlStmt = "SELECT SUM(Amount) AS Total " & _
"FROM tblAmounts;"

Set qry = CurrentDb().CreateQueryDef("qryAmtSum", sqlStmt)
Set fld = qry.Fields("Total")
fld.Properties("Format") = "Standard"
DoCmd.OpenQuery qry.Name

CleanUp:

Set prp = Nothing
Set fld = Nothing
Set qry = Nothing

Exit Sub

ErrHandler:

If (Err.Number = 3270) Then ' Property doesn't exist.
Err.Clear
Set prp = fld.CreateProperty("Format", dbText, "Standard")
fld.Properties.Append prp
Resume Next
Else
MsgBox "Error in createQryWFormat( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks a lot!

I was so close, but I got discouraged when the Format property did not exist
and received this 3270 error message
 
Back
Top