Report query by digits

[The Phil]

Hi, I'm kind of new to access but I understand computers well so I think I'll pick it up quick.

I'm trying to make a report based on the account numbers in the database. It will be an income statement. All of the values in the account column are 6 digits long. I want it to look at the 3rd and 4th digits and if they are 31 then it goes on the Income line in the report and if the 3rd and 4th digits are 41 then it goes in the expense line in the report. The income and expense lines in the report will be a gross total of all of the rows that meet the criteria.

My end result will be much more complicated than this but once I understand the concept I should be able to do the rest myself.

Thanks!

[RuralGuy]

You will want to look at the Mid() function.

[maximus]

The main problem that you have is extracting the third and the fourth number from your six digit number.

This expression will help you. Text Here will be you Account Number Field
Number: Right(Left([Text],4),2)

You can even use it in a query as a calculative field and enhance the code Like this:

Number: IIf(Right(Left([Text],4),2)=31,"Income",IIf(Right(Left([Text],4),2)=41,"Expenditure"))

or

Number1: IIf(Mid([Text],3,2)=31,"Income",IIf(Mid([Text],3,2)=41,"Expenditure"))

Here the Account Number will be classified either income or expenditure based on the 3rd and the 4th Digit.

Note: in the second expression I have assumed that the 3rd and the 4th digit either can be 31 or 41.

[The Phil]

Those formulas make sense to me and I understand what I would need to do to change them to suite my needs. They are similar to excel and I'm quite familiar with excel.

I guess my biggest problem here is the very basics of setting up the report. I've been googling it but I can't seem to find any articles to help people get started. I understand the code you've posted and what it does but I can't seem to figure out where to apply it to.

I can upload a small sample file if you like but I think the best thing for me would be to find a well written tutorial online that goes over some of the basics.

Thanks.

[maximus]

I am attaching a sample mdb file go to queries and open the query in the design view and see for yourself. You are most welcome to upload a sample.