totaling and subtracting semesters

[tjones]

is this even possible and if yes how do you write a report (or query for the report) to do:

201030 = 1 spring semester
201050 = 1 summer semester
201070 = 1 fall semester

so then attendance from 201030 till 201230 = 7 semesters

and also going the other way so 201230 - 201030 = 7 semesters

 

[plog]

A query/report implies multiple rows of data. What you've asked for is something to return one value (number of semsters between two inputs), so that lends itself to a function. Exactly how do you want to use this?

If you have or can compile a datasource that lists all the unique semesters then you can do a Dcount(http://www.techonthenet.com/access/functions/domain/dcount.php) into that datasource.

 

[tjones]

I do have the datasource that lists unique semesters but can not get the query to work. I have attached a graphic of the query, the data source and an attempt at DCount. Any help is very much appreciated.

 

[plog]

Look over the Dcount link I provided, you are not correctly calling it with what you listed.

 

[tjones]

I did look it over and even copied the "setup" but still can not figure out how to make it count from the graduate term to the start term and then list the number of semesters. I have several variations of the DCount code shown in the graphic and none of them work, most of the time it says "code to simple/complex" to compile and returns blank.

 

[plog]

The first thing I see with your DCount is that you aren't just referencing a field in your query--you're trying to subtract two fields. That's not how this works. Just put one field in the first part.

See if that will work.

Also, take of the NZ around the Dcount. We can add it back later, but for now, let's make it as simple as possible to get it to work.