Select Query By Month.

[Terry]

I am currently using the following query to determine which invoices remain
unpaid since the first of the year in our invoice processing database.

Currently the query is based on the following and you must re-enter each
month separately. As an example, if I wanted to know which invoices are
outstanding since the first of the year, I must enter, 01/01/2005 through
05/01/2005 separately. Since the data gained from this query is then issued
in an Excel report this becomes a lengthy and confusing process.

I need to determine either by writing a new query or altering this one, how
I can enter a start and stop date and find those invoices that are unpaid.

Because I specifically need these records broken out by the month they are
missing I am not able to use “Between #date1# And #date2# as my expression
criteria.

Any help and assistance will be greatly appreciated.


SELECT [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].Dept, [Account Info Table].[Vendor
Name], [Account Info Table].[Account Number], [Account Info
Table].[Description of Bill], [Account Info Table].Start, Month([Date
Entered]) AS [Month], [Account Info Table].[Bill Date], Year([Date Entered])
AS [Year], [Account Info Table].Status, [Account Info Table].Audit, [Account
Info Table].Message

FROM ([Account Info Table] LEFT JOIN [Accounts By Month Query] ON [Account
Info Table].[Account Number] = [Accounts By Month Query].[Account Number])
INNER JOIN [Line of Business Number Table] ON [Account Info Table].[Line of
Business Number] = [Line of Business Number Table].[Line of Business Number]

WHERE ((([Account Info Table].[LOB Manager])<>"Bingham") AND ((Year([Date
Entered]))=2005) AND (([Account Info Table].Status)="A") AND (([Accounts By
Month Query].[Invoice Number]) Is Null)) OR ((([Account Info
Table].Status)="AC"))

ORDER BY [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].[Vendor Name], [Account Info
Table].[Account Number], Month([Date Entered]), [Account Info Table].[Bill
Date];

Thank you,

 

[John H W]

If you don't mind the report being in exact date order and not just with the
months in order, use "ORDER BY datefield ASC," to begin your query.
This will give you something like
1/1/05
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.
1/2/05
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.

If you want something like
Month
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.
you will need to write a VB script that does your sorting instead of using a
query.

Since this is put into an Excel spreadsheet, why don't you do the sorting
there. Do the ORDER BY datefield ASC above, then in Excel, select each month
and sort based on your other criteria.

John H W

John,

In the "Missing Invoice Query, I am using a "Is Null" as my criteria
expression to find those accounts where no invoice has been paid.

In the past I have tried to use the "Is Null" and Between Dates as my
criteria. It either goes back to the begining of the database(all records) or
it won't break it out by month. Which Is why the missing invoice query is
based on the Accounts by Month Query --

Thanks,

Terry

Terry:

Do you have some way to determine which invoices are unpaid, such as a paid
field? If so, then use ((BETWEEN startdate AND enddate) AND paidfield = 0)

John H W

I am currently using the following query to determine which invoices remain
unpaid since the first of the year in our invoice processing database.

Currently the query is based on the following and you must re-enter each
month separately. As an example, if I wanted to know which invoices are
outstanding since the first of the year, I must enter, 01/01/2005 through
05/01/2005 separately. Since the data gained from this query is then issued
in an Excel report this becomes a lengthy and confusing process.

I need to determine either by writing a new query or altering this one, how
I can enter a start and stop date and find those invoices that are unpaid.

Because I specifically need these records broken out by the month they are
missing I am not able to use “Between #date1# And #date2# as my expression
criteria.

Any help and assistance will be greatly appreciated.


SELECT [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].Dept, [Account Info Table].[Vendor
Name], [Account Info Table].[Account Number], [Account Info
Table].[Description of Bill], [Account Info Table].Start, Month([Date
Entered]) AS [Month], [Account Info Table].[Bill Date], Year([Date Entered])
AS [Year], [Account Info Table].Status, [Account Info Table].Audit, [Account
Info Table].Message

FROM ([Account Info Table] LEFT JOIN [Accounts By Month Query] ON [Account
Info Table].[Account Number] = [Accounts By Month Query].[Account Number])
INNER JOIN [Line of Business Number Table] ON [Account Info Table].[Line of
Business Number] = [Line of Business Number Table].[Line of Business Number]

WHERE ((([Account Info Table].[LOB Manager])<>"Bingham") AND ((Year([Date
Entered]))=2005) AND (([Account Info Table].Status)="A") AND (([Accounts By
Month Query].[Invoice Number]) Is Null)) OR ((([Account Info
Table].Status)="AC"))

ORDER BY [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].[Vendor Name], [Account Info
Table].[Account Number], Month([Date Entered]), [Account Info Table].[Bill
Date];

Thank you,

 

[John H W]

Terry:

Do you have some way to determine which invoices are unpaid, such as a paid
field? If so, then use ((BETWEEN startdate AND enddate) AND paidfield = 0)

John H W

 

[Van T. Dinh]

Post the SQL of the [Accounts By Month Query]. My guess is that the
restriction comes from this Query and not the SQL you posted.

 

[Terry]

Van T. Dinh,

I apologise for the delay in my response, meetings.

I am sure that is where the problem originates, Unfortunately that was the
only way that I could get it to identify the MONTH. Listed below is the SQL
of the [Accounts By Month Query].

SELECT [Account Info Table].[Account Number], [Invoice Info Table].[Invoice
Number], [Invoice Info Table].[Invoice Date], [Date - (MM/DD/YYYY)] AS [Date
Entered], [Account Info Table].Attention, [Account Info Table].Message

FROM [Account Info Table] LEFT JOIN [Invoice Info Table] ON [Account Info
Table].[Account Number] = [Invoice Info Table].[Account Number]

WHERE (((Month([Date - (MM/DD/YYYY)]))=Month([Invoice Date])) AND
((Year([Date - (MM/DD/YYYY)]))=Year([Invoice Date])))

ORDER BY [Invoice Info Table].[Invoice Date];

All assistance is appreciated.
--
Terry

Post the SQL of the [Accounts By Month Query]. My guess is that the
restriction comes from this Query and not the SQL you posted.

--
HTH
Van T. Dinh
MVP (Access)

I am currently using the following query to determine which invoices remain
unpaid since the first of the year in our invoice processing database.

Currently the query is based on the following and you must re-enter each
month separately. As an example, if I wanted to know which invoices are
outstanding since the first of the year, I must enter, 01/01/2005 through
05/01/2005 separately. Since the data gained from this query is then issued
in an Excel report this becomes a lengthy and confusing process.

I need to determine either by writing a new query or altering this one, how
I can enter a start and stop date and find those invoices that are unpaid.

Because I specifically need these records broken out by the month they are
missing I am not able to use "Between #date1# And #date2# as my expression
criteria.

Any help and assistance will be greatly appreciated.


SELECT [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].Dept, [Account Info Table].[Vendor
Name], [Account Info Table].[Account Number], [Account Info
Table].[Description of Bill], [Account Info Table].Start, Month([Date
Entered]) AS [Month], [Account Info Table].[Bill Date], Year([Date Entered])
AS [Year], [Account Info Table].Status, [Account Info Table].Audit, [Account
Info Table].Message

FROM ([Account Info Table] LEFT JOIN [Accounts By Month Query] ON [Account
Info Table].[Account Number] = [Accounts By Month Query].[Account Number])
INNER JOIN [Line of Business Number Table] ON [Account Info Table].[Line of
Business Number] = [Line of Business Number Table].[Line of Business Number]

WHERE ((([Account Info Table].[LOB Manager])<>"Bingham") AND ((Year([Date
Entered]))=2005) AND (([Account Info Table].Status)="A") AND (([Accounts By
Month Query].[Invoice Number]) Is Null)) OR ((([Account Info
Table].Status)="AC"))

ORDER BY [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].[Vendor Name], [Account Info
Table].[Account Number], Month([Date Entered]), [Account Info Table].[Bill
Date];

Thank you,

 

[Terry]

John,

Sorry for the delay in my response, meetings.

Let me work through you're example and see if I can figure it out. I
apologize, I'm a new user.

Thanks for the help.

--
Terry

Terry:
"ORDER BY" is SQL's way of sorting.

If you look at your query below, you will see "ORDER BY" and a list of
fields. What I am suggesting is that you use the "((BETWEEN firstdate AND
enddate) AND paidfield=NULL)" in the WHERE clause and "datefield ASC," as
the first element of the "ORDER BY" clause. (ASC means ascending[default] vs.
DESC for decending.)

John H W

John,

I am unfamiliar with the "ORDER BY datefield ASC". I have never used it before

Can you give further insight?
--
Terry

If you don't mind the report being in exact date order and not just with the
months in order, use "ORDER BY datefield ASC," to begin your query.
This will give you something like
1/1/05
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.
1/2/05
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.

If you want something like
Month
LOB Manager
Line of Busines Number
Vendor Name
Account Number
etc.
you will need to write a VB script that does your sorting instead of using a
query.

Since this is put into an Excel spreadsheet, why don't you do the sorting
there. Do the ORDER BY datefield ASC above, then in Excel, select each month
and sort based on your other criteria.

John H W

:

John,

In the "Missing Invoice Query, I am using a "Is Null" as my criteria
expression to find those accounts where no invoice has been paid.

In the past I have tried to use the "Is Null" and Between Dates as my
criteria. It either goes back to the begining of the database(all records) or
it won't break it out by month. Which Is why the missing invoice query is
based on the Accounts by Month Query --

Thanks,

Terry


:

Terry:

Do you have some way to determine which invoices are unpaid, such as a paid
field? If so, then use ((BETWEEN startdate AND enddate) AND paidfield = 0)

John H W

:

I am currently using the following query to determine which invoices remain
unpaid since the first of the year in our invoice processing database.

Currently the query is based on the following and you must re-enter each
month separately. As an example, if I wanted to know which invoices are
outstanding since the first of the year, I must enter, 01/01/2005 through
05/01/2005 separately. Since the data gained from this query is then issued
in an Excel report this becomes a lengthy and confusing process.

I need to determine either by writing a new query or altering this one, how
I can enter a start and stop date and find those invoices that are unpaid.

Because I specifically need these records broken out by the month they are
missing I am not able to use “Between #date1# And #date2# as my expression
criteria.

Any help and assistance will be greatly appreciated.


SELECT [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].Dept, [Account Info Table].[Vendor
Name], [Account Info Table].[Account Number], [Account Info
Table].[Description of Bill], [Account Info Table].Start, Month([Date
Entered]) AS [Month], [Account Info Table].[Bill Date], Year([Date Entered])
AS [Year], [Account Info Table].Status, [Account Info Table].Audit, [Account
Info Table].Message

FROM ([Account Info Table] LEFT JOIN [Accounts By Month Query] ON [Account
Info Table].[Account Number] = [Accounts By Month Query].[Account Number])
INNER JOIN [Line of Business Number Table] ON [Account Info Table].[Line of
Business Number] = [Line of Business Number Table].[Line of Business Number]

WHERE ((([Account Info Table].[LOB Manager])<>"Bingham") AND ((Year([Date
Entered]))=2005) AND (([Account Info Table].Status)="A") AND (([Accounts By
Month Query].[Invoice Number]) Is Null)) OR ((([Account Info
Table].Status)="AC"))

ORDER BY [Account Info Table].[LOB Manager], [Account Info Table].[Line of
Business Number], [Account Info Table].[Vendor Name], [Account Info
Table].[Account Number], Month([Date Entered]), [Account Info Table].[Bill
Date];

Thank you,

 

[Van T. Dinh]

It looks to me that the Parameter is:

[Date - (MM/DD/YYYY)]

in the WHERE Clause

I would simply change the WHERE Clause to:

.... WHERE [Invoice Date] BETWEEN [Enter StartDate (MM/DD/YYYY):]
AND [Enter EndDate (MM/DD/YYYY):]

You will also need to change the SELECT Clause as you referred to the (old)
Param in the SELECT Clause.