format a currency field within arrSummaryData

[iankerry]

Hi Folks

I have the following 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]

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]

You don't need arrays in Access. It uses queries. No need for code, run queries.

 

[iankerry]

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]

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

 

[bob fitz]

Have you tried using the CCur() function

 

[iankerry]

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

 

[iankerry]

Thanks all, have found solution.

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