Sorting by Function results

[Zunn]

I have a function BirthdaySoon() that returns the number of days left before a client's birthday. However I want to put this information on a report in sorted order from the nearest birthday until the furthest and maybe filtering out birthdays that are very far away.

The design of the report at the moment is like this:

Name DoB Age Days to go
-------------------------------------------
[Name] [DoB] [age] BirthdaySoon() & " days to go"


As you can see the function is called BirthdaySoon() and this just returns a number of days left to go. Is there a way to sort the report out?

 

[jebry]

Hi!

Write a query where you define a field like so:

Day to go: BirthdaySoon() & " days to go"

Then you will need to add the other fields as well. Base your report on the query and you will be able to sort and/or group on the field.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Zunn]

I don't understand.

How do I define a field in a query like above?

Where do I put what?

 

[jebry]

Hi!

In the query design screen on the field line use:

[Days to go]: BirthdaySoon() & " days to go"

This will run your function for each record and concatenate the words days to go to the result. How you get different results without passing any information to the function, I'm not sure but the above in the field row in your query design will define the field [Days to go] which you can then use like any other field in your report including grouping and sorting.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Zunn]

Where do I put the code for the BirthdaySoon() function?

It doesn't like the [Days to go]:BirthdaySoon()& " days to go" line. Getting errors "Database engine can't find [days to go].."

 

[Zunn]

I've made the query work fine!

However on the criteria line I've added,

Between [Lower Age] and [Upper Age]

This asks the user to input a lower and upper age and will then only display the records that lie in between this range.

This works in Access 97 but not on Access XP. Why? and how could I fix this?