Group as "000"

[RoseM]

Could someone point me in the right direction for this:

I want to display groups of 1000 as a single number -- e.g., if my number is 28078, I want to display '28'....(I'm working on a report at the moment, but this will also apply to how items get displayed in textboxes on forms).

I'm not sure how to do this; it's not exactly rounding, and it's not exactly an input mask. I'd appreciate the help.

Thanks
Rosey

[JKing]

You're basically dividing by 1000 and taking the integer value left.

Int( Field / 1000) should work to do this.

If field is 56021 dividing by 1000 would give you 56.021 and the Int Function would return only 56.

[RoseM]

Well...I guess it is rounding. Is there any way to do this without creating a new field (like by formatting what my existing textbox displays)?

There are many columns displaying the number of accounts overdue at specific time points (over 90 days, over 180 days, etc...). I'm manipulating a report from SAP, so I can't get at how overdue something is via calculations -- I don't have the date to increment from. So I'm stuck with all these columns...I'm just trying to avoid having to create all those additional columns to get at increments in the thousands.

Thanks

[RoseM]

Sorry JK--my post collided with yours...

Any shorter (lazier) way to do this?

Thanks

[JKing]

I just need to try to understand exactly what you're doing here. So your data is drawn from a SAP report. Are you placing this in a table in access and building forms off of that table to display overdude accounts and instead of showing a big long number you just want to display a smaller number representing the overdue accounts in 1000s.

[RoseM]

Right...in this case I'm looking to build a report that displays outstanding accounts by collector. This is what the header will look like:

Name ReportDate Over90 Over130 Over160 [etc...]

The report is run monthly and includes previous report dates, so we'll store the report in Access with the date the report was run, and over time the manager can see whether a collector trends up or down in a given bucket (I'm not arguing the efficacy here ;).

Your original answer is probably what I need to do--I just get a little frustrated at not being able to manipulate the SAP report and doing so much on the back end to make the reports useful.

[JKing]

Ok, but you do have a table in access storing the amount of overdue accounts for each time period.

So could you not create a query that would supply this information for your report?

Something like this:

SELECT Int([90DaysOverdue] / 1000) AS 90DaysOverBy1000 , Int([120DaysOverDue]/1000) As 120DaysOverBy1000
FROM tblOverDueAccounts
 

Then the calculation would be done within access and the fields would already be in the format for you on your report.

[RoseM]

I could try that. I've had a hard time understanding how to assign a recordset to a report, though.

I'm comfortable manipulating queries used by forms in SQL, but I've been using the query visual interface to build reports. When I need to assign a query to a report, I just create the query and call it by name. When I'm moving stuff around in a form or manipulating data in tables, I write out the SQL statement.

I guess in this case I'd attach that query to a button on the form, run it, then docmd.openreport or some such?

I know I'm missing something here. I'm new to building reports in Access and I'm still figuring things out philosophically (like do I want to use the visual interface versus writing in VBA? I do prefer to use code because I like the one-stop readability and transparency.)

Thanks for your insights.

[JKing]

What you can do is create the query with all the fields you need to be on the report and save it. Then use the report wizard to create a report based off that saved query. The report wizard gives you an option at the very start to select a query or a table from the drop down. You can then add code to a button to open the report you've saved.

The code will be fairly simple:

Private sub cmdPreviewReport_click
 
Docmd.OpenReport "rptAccountReport"
End Sub
 

[RoseM]

What you can do is create the query with all the fields you need to be on the report and save it.

Okay--thanks; I'll give it a shot.