format a currency field within arrSummaryData (1 Viewer)

iankerry

Registered User.
Local time
Today, 15:54
Joined
Aug 10, 2005
Messages
190
Hi Folks

I have the following code:

Code:
f OpenConnection Then
        If rs.State = adStateOpen Then rs.Close
         rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

         If Not rs.EOF Then
            dblRecord = rs.RecordCount
            ReDim arrSummaryData(1 To dblRecord)
            'create an Array depends on number of records and put all data with features
            '
            With rs
                 intRecordCount = 1
                 Do While Not .EOF
                      
                      arrSummaryData(intRecordCount).strPromoterName = IIf(IsNull(![NAME]), "", ![NAME])
                      strPromoterName = arrSummaryData(intRecordCount).strPromoterName
                      arrSummaryData(intRecordCount).strEmail = IIf(IsNull(![email]), "", ![email])
                      strEmail = arrSummaryData(intRecordCount).strEmail
                      arrSummaryData(intRecordCount).dtDatefield = IIf(IsNull(![datefield]), "", ![datefield])
                      arrSummaryData(intRecordCount).strTime = IIf(IsNull(![time]), "", ![time])
                      arrSummaryData(intRecordCount).strDVD = IIf(IsNull(![owndvd]), "", ![owndvd])
                      If arrSummaryData(intRecordCount).strDVD = "False" Then
                      arrSummaryData(intRecordCount).strDVD = "We will provide the DVD for this screening"
                      Else
                      arrSummaryData(intRecordCount).strDVD = "You will provide your own DVD"
                      End If
                      arrSummaryData(intRecordCount).strFilmName = IIf(IsNull(![film name]), "", ![film name])
                      arrSummaryData(intRecordCount).strVenue = IIf(IsNull(![VENUE]), "", ![VENUE])
                      arrSummaryData(intRecordCount).curAdultTP = IIf(IsNull(![adulttp]), "", ![adulttp])
                      arrSummaryData(intRecordCount).curChildTP = IIf(IsNull(![ChildTP]), "", ![ChildTP])
                      arrSummaryData(intRecordCount).curFamilyTP = IIf(IsNull(![FamilyTP]), "", ![FamilyTP])
                      arrSummaryData(intRecordCount).curother1tp = IIf(IsNull(![Other1TP]), "", ![Other1TP])
                      arrSummaryData(intRecordCount).StrSpEvent = IIf(IsNull(![specialevents]), "", ![specialevents])
                     .MoveNext
                     intRecordCount = intRecordCount + 1
                 Loop
                .Close
            End With

The problem is that the field [adulttp] comes up as £3.5 rather than £3.50 - which is what it is in the actual table.

How can I force the format with two decimal places?

many thanks,

ian
 

bob fitz

AWF VIP
Local time
Today, 15:54
Joined
May 23, 2011
Messages
4,717
The problem is that the field [adulttp] comes up as £3.5 rather than £3.50 - which is what it is in the actual table.
Is this on a form? If so, have you looked at the Format property of the control that displays the figure?
 

Ranman256

Well-known member
Local time
Today, 11:54
Joined
Apr 9, 2015
Messages
4,339
You don't need arrays in Access. It uses queries. No need for code, run queries.
 

iankerry

Registered User.
Local time
Today, 15:54
Joined
Aug 10, 2005
Messages
190
You don't need arrays in Access. It uses queries. No need for code, run queries.

hmmm - this code goes and gets the data from an SQL server. It then puts all the information I need into an email, ready to send to someone.

It is in this email that the [adulttp] doesn't show 2 digits.

Even if there is a better way of doing it, given that i have done it this way, is there a way i can format the field?

thanks for your input.

Ian
 

iankerry

Registered User.
Local time
Today, 15:54
Joined
Aug 10, 2005
Messages
190
Is this on a form? If so, have you looked at the Format property of the control that displays the figure?

Thanks for replying.


No, I have a button on a form, that runs a whole routine, which eventually brings up an email with all the data in it that i need to send to someone.

It is in that email that the £3.5 shows.

Ian
 

iankerry

Registered User.
Local time
Today, 15:54
Joined
Aug 10, 2005
Messages
190
Thanks Bob, have just been googling, but I can't see how i can use it within the line:

arrSummaryData(intRecordCount).curAdultTP = IIf(IsNull(![adulttp]), "", ![adulttp])

I have just noticed something whilst trying CCur - if the [adulttp] = £3.51 then it works fine - so the routine just cuts off any final 0's.

Do you know why it might do that - or how i can change it?

thanks

ian
 
Last edited:

iankerry

Registered User.
Local time
Today, 15:54
Joined
Aug 10, 2005
Messages
190
Thanks all, have found solution.

the field curAdultTP is defined elsewhere and has not been defined correctly.
 

Users who are viewing this thread

Top Bottom