|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
|
|
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66eeb863d1023[ at ]uwe...
[Quoted Text] > I'm using the Default MS Access Sales by Customer reports. Is it possible
> to
> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales
> format?
_What_ "Default MS Access Sales by Customer" Reports are you talking about?
The ones in the Northwind Traders sample database or something else? Access
itself has no such Reports.
Larry Linson
Microsoft Access MVP
|
|
I would suggest creating a public function that accepts a date and returns a
string value of the range name. You can then use this function as the Column
Heading for a crosstab query.
--
Duane Hookom
MS Access MVP
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66eeb863d1023[ at ]uwe...
[Quoted Text]
|
|
Thank you for the response. Sorry, I didn't mention it earlier, I'm using
the MS Access "Order Entry" database. There is 2 reports I've tried:
"Receivables Aging" which does work, but it lists all the entries line by
line for every entry for that customber account. This report would work if
only listed the total sales and not every entry.
The other report is the 1 I mentions in MS Access "Order Entry" database
"Total Sales by Customber". This would work if we can list it in 0-30, 31-60,
61-90, 90+. Please let me know if this is possible. Thank you Arnold
Larry Linson wrote:
[Quoted Text] >> I'm using the Default MS Access Sales by Customer reports. Is it possible
>> to
>> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales
>> format?
>
>_What_ "Default MS Access Sales by Customer" Reports are you talking about?
>The ones in the Northwind Traders sample database or something else? Access
>itself has no such Reports.
>
> Larry Linson
> Microsoft Access MVP
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200609/1
|
|
Sorry. I know of the existence of this sample database, but have never used
it. Unfortunately, I do not have time and energy to delve into it enough to
be able to answer questions about it.
Larry Linson
Microsoft Access MVP
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66fa37a3cad5d[ at ]uwe...
[Quoted Text] > Thank you for the response. Sorry, I didn't mention it earlier, I'm using > the MS Access "Order Entry" database. There is 2 reports I've tried: > "Receivables Aging" which does work, but it lists all the entries line by > line for every entry for that customber account. This report would work > if > only listed the total sales and not every entry. > > The other report is the 1 I mentions in MS Access "Order Entry" database > "Total Sales by Customber". This would work if we can list it in 0-30, > 31-60, > 61-90, 90+. Please let me know if this is possible. Thank you Arnold > > > Larry Linson wrote: >>> I'm using the Default MS Access Sales by Customer reports. Is it >>> possible >>> to >>> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales >>> format? >> >>_What_ "Default MS Access Sales by Customer" Reports are you talking >>about? >>The ones in the Northwind Traders sample database or something else? >>Access >>itself has no such Reports. >> >> Larry Linson >> Microsoft Access MVP > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200609/1>
|
|
Did you read my reply? Do you understand my reply or how to create a
user-defined function?
--
Duane Hookom
MS Access MVP
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66fa37a3cad5d[ at ]uwe...
[Quoted Text] > Thank you for the response. Sorry, I didn't mention it earlier, I'm using > the MS Access "Order Entry" database. There is 2 reports I've tried: > "Receivables Aging" which does work, but it lists all the entries line by > line for every entry for that customber account. This report would work > if > only listed the total sales and not every entry. > > The other report is the 1 I mentions in MS Access "Order Entry" database > "Total Sales by Customber". This would work if we can list it in 0-30, > 31-60, > 61-90, 90+. Please let me know if this is possible. Thank you Arnold > > > Larry Linson wrote: >>> I'm using the Default MS Access Sales by Customer reports. Is it >>> possible >>> to >>> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales >>> format? >> >>_What_ "Default MS Access Sales by Customer" Reports are you talking >>about? >>The ones in the Northwind Traders sample database or something else? >>Access >>itself has no such Reports. >> >> Larry Linson >> Microsoft Access MVP > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200609/1>
|
|
Hello Duane,
I'm fairly new to MS Access, so I don't understand that particular function.
It would be great if you can show me please. Thank you for your quick
response. Arnold
Duane Hookom wrote:
[Quoted Text] >Did you read my reply? Do you understand my reply or how to create a
>user-defined function?
>
>> Thank you for the response. Sorry, I didn't mention it earlier, I'm using
>> the MS Access "Order Entry" database. There is 2 reports I've tried:
>[quoted text clipped - 22 lines]
>>> Larry Linson
>>> Microsoft Access MVP
--
Message posted via http://www.accessmonster.com
|
|
Create a new standard module and add something like:
Public Function GetRange(pintDays As Integer) As String
' function that accepts a number and returns a value like
' 0-30, 31-60, 61-90, 90+
' use this in a query like:
' DaysRange: GetRange(DateDiff("d",[DateField],Date()))
'
Select Case pintDays
Case 0 To 30
GetRange = "From 0 to 30"
Case 31 To 60
GetRange = "From 31 to 60"
Case 61 To 90
GetRange = "From 61 to 90"
Case Else
GetRange = "Greater Than 90"
End Select
End Function
Save the module as "modDateFunctions".
You can then use the function in the crosstab as a column heading. If you
date ranges change, you will need to modify the function. If you want a more
flexible solution, you can set up a table with Min and Max days and the
range title.
--
Duane Hookom
MS Access MVP
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66faff193415c[ at ]uwe...
[Quoted Text] > Hello Duane, > > I'm fairly new to MS Access, so I don't understand that particular > function. > It would be great if you can show me please. Thank you for your quick > response. Arnold > > Duane Hookom wrote: >>Did you read my reply? Do you understand my reply or how to create a >>user-defined function? >> >>> Thank you for the response. Sorry, I didn't mention it earlier, I'm >>> using >>> the MS Access "Order Entry" database. There is 2 reports I've tried: >>[quoted text clipped - 22 lines] >>>> Larry Linson >>>> Microsoft Access MVP > > -- > Message posted via http://www.accessmonster.com>
|
|
Hello Duane,
Thank you for your help. I got as far as saving the module as
"modDateFunctions". I got lost with the use of the function crosstab in
column heading. Do I need to create another query with: customer, acct#, 30,
60, 90, 90+? or use the existing Sales by Customer query or report? Sorry,
I'm new here when it comes to reports. Thanks again. Arnold
Duane Hookom wrote:
[Quoted Text] >Create a new standard module and add something like:
>Public Function GetRange(pintDays As Integer) As String
> ' function that accepts a number and returns a value like
> ' 0-30, 31-60, 61-90, 90+
> ' use this in a query like:
> ' DaysRange: GetRange(DateDiff("d",[DateField],Date()))
> '
> Select Case pintDays
> Case 0 To 30
> GetRange = "From 0 to 30"
> Case 31 To 60
> GetRange = "From 31 to 60"
> Case 61 To 90
> GetRange = "From 61 to 90"
> Case Else
> GetRange = "Greater Than 90"
> End Select
>End Function
>
>Save the module as "modDateFunctions".
>
>You can then use the function in the crosstab as a column heading. If you
>date ranges change, you will need to modify the function. If you want a more
>flexible solution, you can set up a table with Min and Max days and the
>range title.
>
>> Hello Duane,
>>
>[quoted text clipped - 12 lines]
>>>>> Larry Linson
>>>>> Microsoft Access MVP
--
Message posted via http://www.accessmonster.com
|
|
Instead of referring to a report in some other database, why don't you
describe your data and desired report?
--
Duane Hookom
MS Access MVP
"iaqsuk via AccessMonster.com" <u25423[ at ]uwe> wrote in message
news:66fc9834f28a7[ at ]uwe...
[Quoted Text] > Hello Duane, > > Thank you for your help. I got as far as saving the module as > "modDateFunctions". I got lost with the use of the function crosstab in > column heading. Do I need to create another query with: customer, acct#, > 30, > 60, 90, 90+? or use the existing Sales by Customer query or report? > Sorry, > I'm new here when it comes to reports. Thanks again. Arnold > > > Duane Hookom wrote: >>Create a new standard module and add something like: >>Public Function GetRange(pintDays As Integer) As String >> ' function that accepts a number and returns a value like >> ' 0-30, 31-60, 61-90, 90+ >> ' use this in a query like: >> ' DaysRange: GetRange(DateDiff("d",[DateField],Date())) >> ' >> Select Case pintDays >> Case 0 To 30 >> GetRange = "From 0 to 30" >> Case 31 To 60 >> GetRange = "From 31 to 60" >> Case 61 To 90 >> GetRange = "From 61 to 90" >> Case Else >> GetRange = "Greater Than 90" >> End Select >>End Function >> >>Save the module as "modDateFunctions". >> >>You can then use the function in the crosstab as a column heading. If you >>date ranges change, you will need to modify the function. If you want a >>more >>flexible solution, you can set up a table with Min and Max days and the >>range title. >> >>> Hello Duane, >>> >>[quoted text clipped - 12 lines] >>>>>> Larry Linson >>>>>> Microsoft Access MVP > > -- > Message posted via http://www.accessmonster.com>
|
|
Hello Duane,
I just want to thank you for all you responses. In another forum I asked the
same question, but the question was more geared to MS Access "Order Entry"
Receivable Aging Report. This report gives aging report in 0-30, 31-60, 61-
90, 90+. This works fine, but it gives every entry for each line items and
not the totals.
I got a response to hide the Details section in that report, but I don't know
if it would of given me all the custombers with just the totals. Thank you.
Arnold
Duane Hookom wrote:
[Quoted Text] >Instead of referring to a report in some other database, why don't you
>describe your data and desired report?
>
>> Hello Duane,
>>
>[quoted text clipped - 38 lines]
>>>>>>> Larry Linson
>>>>>>> Microsoft Access MVP
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200609/1
|
|
|