want other value in query and report

[Guest]

Access 2000 - Set up database that pulls month name from Month Table (fields
Month_Name and Month_Number). On the Data Input Form, the month name is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I look at the
data in data view, each record displays these field with month name (from a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for different
reports, sorted in various ways. The queries and a wizard generated report
show MonthDue, PeriodStart and PeriodEnd with month number not month name.
Want to see month name. Doesn't seem to change anything if I establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able to put
together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[PC Datasheet]

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

 

[Guest]

Did it - doesn't work right. For months numbered 0 and 1 the query shows
December as month name - (0 = January and 1 = February - don't know why the
Data Input Form uses these numbers, my table uses 1 - January, 2 - February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows January
as month name ( 2 thru 12 follows the rest of the months chronologically with
12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Access 2000 - Set up database that pulls month name from Month Table (fields
Month_Name and Month_Number). On the Data Input Form, the month name is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I look at the
data in data view, each record displays these field with month name (from a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for different
reports, sorted in various ways. The queries and a wizard generated report
show MonthDue, PeriodStart and PeriodEnd with month number not month name.
Want to see month name. Doesn't seem to change anything if I establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able to put
together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[PC Datasheet]

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query shows
December as month name - (0 = January and 1 = February - don't know why the
Data Input Form uses these numbers, my table uses 1 - January, 2 - February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows January
as month name ( 2 thru 12 follows the rest of the months chronologically with
12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Access 2000 - Set up database that pulls month name from Month Table (fields
Month_Name and Month_Number). On the Data Input Form, the month name is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I look

at
the

data in data view, each record displays these field with month name

(from
a

MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for different
reports, sorted in various ways. The queries and a wizard generated report
show MonthDue, PeriodStart and PeriodEnd with month number not month name.
Want to see month name. Doesn't seem to change anything if I establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able to put
together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[Guest]

The database is for tracking the due dates of reports that we provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess around
with year dates, just month and day due. Occassionally, some of the reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are due upon
request.

I have a table named Months which consists of the fields MonthNumber (PK)
and MonthName. I created a form for data entry called Reports Database Info.
Three fields in this table use a combo list from the MonthsQuery to give a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the locations the
reports are covering - so I was left with no uniformity to build this on. I
figured the easiest way was to fill in this information manually in the three
fields - giving me the most flexibility, but pull from one table rather than
use a date field -remember, I don't care what year it is due, just the month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked to the
query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort, extract and
view the information in the database. Instead of displaying the name of the
month, the report displays the month number. The month number doesn't follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data Input
Form, each record displays the month name but when I look directly into the
table Reports Database Info, the three fields display the numbers 0 - 12 .

As I said before, your last suggestion displays the month name but only the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems than
it is worth.

Any suggestions?

Suzanne

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query shows
December as month name - (0 = January and 1 = February - don't know why the
Data Input Form uses these numbers, my table uses 1 - January, 2 - February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows January
as month name ( 2 thru 12 follows the rest of the months chronologically with
12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month name is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I look at
the
data in data view, each record displays these field with month name (from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for
different
reports, sorted in various ways. The queries and a wizard generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not month name.
Want to see month name. Doesn't seem to change anything if I establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able to put
together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[PC Datasheet]

Your table of months is a trouble maker and your idea to scrap it is a good
one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue. All your
queries that show something about when a report is due need to include the
OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to False and
include TblReportDue in your query. If you want to find only the reports
that are due upon request, you would set OnlyUponRequest to True and you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

The database is for tracking the due dates of reports that we provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess around
with year dates, just month and day due. Occassionally, some of the reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are due upon
request.

I have a table named Months which consists of the fields MonthNumber (PK)
and MonthName. I created a form for data entry called Reports Database Info.
Three fields in this table use a combo list from the MonthsQuery to give a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the locations the
reports are covering - so I was left with no uniformity to build this on. I
figured the easiest way was to fill in this information manually in the three
fields - giving me the most flexibility, but pull from one table rather than
use a date field -remember, I don't care what year it is due, just the month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked to the
query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort, extract and
view the information in the database. Instead of displaying the name of the
month, the report displays the month number. The month number doesn't follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data Input
Form, each record displays the month name but when I look directly into the
table Reports Database Info, the three fields display the numbers 0 - 12 .

As I said before, your last suggestion displays the month name but only the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems than
it is worth.

Any suggestions?

Suzanne

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query shows
December as month name - (0 = January and 1 = February - don't know

why
the

Data Input Form uses these numbers, my table uses 1 - January, 2 - February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows January
as month name ( 2 thru 12 follows the rest of the months

chronologically
with

12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month

name
is

displayed for MonthDue, PeriodStart and PeriodEnd fields. When I

look
at

the
data in data view, each record displays these field with month

name
(from

a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for
different
reports, sorted in various ways. The queries and a wizard generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not

month
name.

Want to see month name. Doesn't seem to change anything if I establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able

to
put

together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[Guest]

Am in the process of making changes - question regarding month...

Identifying the months by their number helps keep things in chronologic
order (combo lists). When I'm ready to extract reports for the boss that
will display a list of our reports that are due, how do I get rid of the
month number and replace it with the month name?

Originally, I had created a table of just the month names (because I could
not get a date field to only display month or month and day without year) but
when I used that table as a combo list, it automatically sorted in alpha
order, forcing the months to not be chronological - pushing the need for
numbered months, but the boss doesn't like to see numbered months, he likes
names - he also wanted the data entry person to be able to select the month
by number or month name - but I never got that working.

Thanks for your help, I need to close out this project and move on to the
next, hopefully restructuring the tables will resolve this issue.

Most Appreciative - Suzanne

Your table of months is a trouble maker and your idea to scrap it is a good
one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue. All your
queries that show something about when a report is due need to include the
OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to False and
include TblReportDue in your query. If you want to find only the reports
that are due upon request, you would set OnlyUponRequest to True and you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

The database is for tracking the due dates of reports that we provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess around
with year dates, just month and day due. Occassionally, some of the reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are due upon
request.

I have a table named Months which consists of the fields MonthNumber (PK)
and MonthName. I created a form for data entry called Reports Database Info.
Three fields in this table use a combo list from the MonthsQuery to give a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the locations the
reports are covering - so I was left with no uniformity to build this on. I
figured the easiest way was to fill in this information manually in the three
fields - giving me the most flexibility, but pull from one table rather than
use a date field -remember, I don't care what year it is due, just the month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked to the
query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort, extract and
view the information in the database. Instead of displaying the name of the
month, the report displays the month number. The month number doesn't follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data Input
Form, each record displays the month name but when I look directly into the
table Reports Database Info, the three fields display the numbers 0 - 12 .

As I said before, your last suggestion displays the month name but only the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems than
it is worth.

Any suggestions?

Suzanne

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query shows
December as month name - (0 = January and 1 = February - don't know why
the
Data Input Form uses these numbers, my table uses 1 - January, 2 -
February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows
January
as month name ( 2 thru 12 follows the rest of the months chronologically
with
12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month name
is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I look
at
the
data in data view, each record displays these field with month name
(from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for
different
reports, sorted in various ways. The queries and a wizard generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not month
name.
Want to see month name. Doesn't seem to change anything if I
establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able to
put
together but cannot seem to find a fix. Don't have any books but am
seriously contemplating Access 2000 for Dummies.

 

[PC Datasheet]

The values in combo lists should be derived from the Date Serial function
and you then just sort the dates ascending.

<<When I'm ready to extract reports for the boss that will display a list
of our reports that are due, how do I get rid of the
month number and replace it with the month name? >>
This question is answered in the sample queries.

You are too much focused on the month number and day number! You must use
these in the Date Serial function to derive the report due dates and then
work with the dates and not the month number or day number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Am in the process of making changes - question regarding month...

Identifying the months by their number helps keep things in chronologic
order (combo lists). When I'm ready to extract reports for the boss that
will display a list of our reports that are due, how do I get rid of the
month number and replace it with the month name?

Originally, I had created a table of just the month names (because I could
not get a date field to only display month or month and day without year) but
when I used that table as a combo list, it automatically sorted in alpha
order, forcing the months to not be chronological - pushing the need for
numbered months, but the boss doesn't like to see numbered months, he likes
names - he also wanted the data entry person to be able to select the month
by number or month name - but I never got that working.

Thanks for your help, I need to close out this project and move on to the
next, hopefully restructuring the tables will resolve this issue.

Most Appreciative - Suzanne

Your table of months is a trouble maker and your idea to scrap it is a good
one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue. All your
queries that show something about when a report is due need to include the
OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to False and
include TblReportDue in your query. If you want to find only the reports
that are due upon request, you would set OnlyUponRequest to True and you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

The database is for tracking the due dates of reports that we provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess around
with year dates, just month and day due. Occassionally, some of the reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are due upon
request.

I have a table named Months which consists of the fields MonthNumber (PK)
and MonthName. I created a form for data entry called Reports

Database
Info.

Three fields in this table use a combo list from the MonthsQuery to

give
a

drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the

locations
the

reports are covering - so I was left with no uniformity to build this

on.
I

figured the easiest way was to fill in this information manually in

the
three

fields - giving me the most flexibility, but pull from one table

rather
than

use a date field -remember, I don't care what year it is due, just the month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked to the
query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort, extract and
view the information in the database. Instead of displaying the name

of
the

month, the report displays the month number. The month number doesn't follow
my 1-13 numbering system (primary key), it follows a 0-12. On the

Data
Input

Form, each record displays the month name but when I look directly

into
the

table Reports Database Info, the three fields display the numbers 0 - 12 .

As I said before, your last suggestion displays the month name but

only
the

names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems than
it is worth.

Any suggestions?

Suzanne


:

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the

query
shows

December as month name - (0 = January and 1 = February - don't

know
why

the
Data Input Form uses these numbers, my table uses 1 - January, 2 -
February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows
January
as month name ( 2 thru 12 follows the rest of the months chronologically
with
12 = Various - - again, don't know why it follows this format, the Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month Table
(fields
Month_Name and Month_Number). On the Data Input Form, the

month
name

is
displayed for MonthDue, PeriodStart and PeriodEnd fields.

When I
look

at
the
data in data view, each record displays these field with month name
(from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for
different
reports, sorted in various ways. The queries and a wizard generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not month
name.
Want to see month name. Doesn't seem to change anything if I
establish a
relationship or not.

I've searched Access Help, this site and any notes I've been

able
to

put
together but cannot seem to find a fix. Don't have any books

but
am

seriously contemplating Access 2000 for Dummies.

 

[Guest]

I don't understand what you mean by Date Serial function, when I search for
that phrase in help I don't get it as an option... please help...I don't have
a book. I'm using software that is provided through our LAN system and books
weren't issued. It's been well over five years since I went to class on this
stuff.

When I implemented the suggested tables I ran into a problem with tblReport
- there are a couple of situations where the same report name goes to a
different agency. Such as: Emissions Inventory Report can go to Air Quality
Management Board or the Air Pollution Control District based on the
Installation submitting the report.

I figured I could create tables with the list of names: report names, agency
names, report frequency, etc and then use those tables for the combo list.
None of the tables are linked together. I tried to include fields in the
tables that would hold unique information and would be true no matter what
situation arose but found there weren't too many opportunities for this.

What is the best way to build a database that is flexible enough to deal
with the occasional situation that doesn't follow the norm?

I'll look at the samples to try and determine how to change the month number
to month name. Hoepfully it won't be as painful.

Thanks, Suzanne

The values in combo lists should be derived from the Date Serial function
and you then just sort the dates ascending.

<<When I'm ready to extract reports for the boss that will display a list
of our reports that are due, how do I get rid of the
month number and replace it with the month name? >>
This question is answered in the sample queries.

You are too much focused on the month number and day number! You must use
these in the Date Serial function to derive the report due dates and then
work with the dates and not the month number or day number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Am in the process of making changes - question regarding month...

Identifying the months by their number helps keep things in chronologic
order (combo lists). When I'm ready to extract reports for the boss that
will display a list of our reports that are due, how do I get rid of the
month number and replace it with the month name?

Originally, I had created a table of just the month names (because I could
not get a date field to only display month or month and day without year) but
when I used that table as a combo list, it automatically sorted in alpha
order, forcing the months to not be chronological - pushing the need for
numbered months, but the boss doesn't like to see numbered months, he likes
names - he also wanted the data entry person to be able to select the month
by number or month name - but I never got that working.

Thanks for your help, I need to close out this project and move on to the
next, hopefully restructuring the tables will resolve this issue.

Most Appreciative - Suzanne

Your table of months is a trouble maker and your idea to scrap it is a good
one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue. All your
queries that show something about when a report is due need to include the
OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to False and
include TblReportDue in your query. If you want to find only the reports
that are due upon request, you would set OnlyUponRequest to True and you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
The database is for tracking the due dates of reports that we provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess
around
with year dates, just month and day due. Occassionally, some of the
reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are due upon
request.

I have a table named Months which consists of the fields MonthNumber (PK)
and MonthName. I created a form for data entry called Reports Database
Info.
Three fields in this table use a combo list from the MonthsQuery to give
a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even
annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the locations
the
reports are covering - so I was left with no uniformity to build this on.
I
figured the easiest way was to fill in this information manually in the
three
fields - giving me the most flexibility, but pull from one table rather
than
use a date field -remember, I don't care what year it is due, just the
month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked to the
query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort, extract and
view the information in the database. Instead of displaying the name of
the
month, the report displays the month number. The month number doesn't
follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data
Input
Form, each record displays the month name but when I look directly into
the
table Reports Database Info, the three fields display the numbers 0 - 12 .

As I said before, your last suggestion displays the month name but only
the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems
than
it is worth.

Any suggestions?

Suzanne


:

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query
shows
December as month name - (0 = January and 1 = February - don't know
why
the
Data Input Form uses these numbers, my table uses 1 - January, 2 -
February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows
January
as month name ( 2 thru 12 follows the rest of the months
chronologically
with
12 = Various - - again, don't know why it follows this format, the
Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this
affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month
Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month
name
is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I
look
at
the
data in data view, each record displays these field with month
name
(from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different queries for
different
reports, sorted in various ways. The queries and a wizard
generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not
month
name.
Want to see month name. Doesn't seem to change anything if I
establish a
relationship or not.

I've searched Access Help, this site and any notes I've been able
to
put
together but cannot seem to find a fix. Don't have any books but
am
seriously contemplating Access 2000 for Dummies.

 

[Brendan Reynolds]

Try 'DateSerial' - note, no space. Start from the VBA editor window, not the
main Access window.

Building any non-trivial Access database without books or recent classes
and/or experience is not going to be easy. Good luck.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I don't understand what you mean by Date Serial function, when I search for
that phrase in help I don't get it as an option... please help...I don't
have
a book. I'm using software that is provided through our LAN system and
books
weren't issued. It's been well over five years since I went to class on
this
stuff.

When I implemented the suggested tables I ran into a problem with
tblReport
- there are a couple of situations where the same report name goes to a
different agency. Such as: Emissions Inventory Report can go to Air
Quality
Management Board or the Air Pollution Control District based on the
Installation submitting the report.

I figured I could create tables with the list of names: report names,
agency
names, report frequency, etc and then use those tables for the combo list.
None of the tables are linked together. I tried to include fields in the
tables that would hold unique information and would be true no matter what
situation arose but found there weren't too many opportunities for this.

What is the best way to build a database that is flexible enough to deal
with the occasional situation that doesn't follow the norm?

I'll look at the samples to try and determine how to change the month
number
to month name. Hoepfully it won't be as painful.

Thanks, Suzanne

The values in combo lists should be derived from the Date Serial function
and you then just sort the dates ascending.

<<When I'm ready to extract reports for the boss that will display a
list
of our reports that are due, how do I get rid of the
month number and replace it with the month name? >>
This question is answered in the sample queries.

You are too much focused on the month number and day number! You must use
these in the Date Serial function to derive the report due dates and then
work with the dates and not the month number or day number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Am in the process of making changes - question regarding month...

Identifying the months by their number helps keep things in chronologic
order (combo lists). When I'm ready to extract reports for the boss
that
will display a list of our reports that are due, how do I get rid of
the
month number and replace it with the month name?

Originally, I had created a table of just the month names (because I could
not get a date field to only display month or month and day without
year) but
when I used that table as a combo list, it automatically sorted in
alpha
order, forcing the months to not be chronological - pushing the need
for
numbered months, but the boss doesn't like to see numbered months, he likes
names - he also wanted the data entry person to be able to select the month
by number or month name - but I never got that working.

Thanks for your help, I need to close out this project and move on to
the
next, hopefully restructuring the tables will resolve this issue.

Most Appreciative - Suzanne

:

Your table of months is a trouble maker and your idea to scrap it is
a good
one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue. All your
queries that show something about when a report is due need to
include the
OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to
False and
include TblReportDue in your query. If you want to find only the
reports
that are due upon request, you would set OnlyUponRequest to True and
you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:
ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D
ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:
ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay]) and
set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
The database is for tracking the due dates of reports that we
provide to
regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to
mess
around
with year dates, just month and day due. Occassionally, some of
the
reports
are due only when an agency requests, so we came up with 13 months, the
normal 12 and "Varies" for the 13th to cover the reports that are
due upon
request.

I have a table named Months which consists of the fields
MonthNumber (PK)
and MonthName. I created a form for data entry called Reports Database
Info.
Three fields in this table use a combo list from the MonthsQuery
to give
a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart
and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and
PeriodEnd cover
the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc
but
discovered the reports didn't always follow a specific quarter or
even
annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June
etc.
Sometimes, the months due and periods covered are based on the locations
the
reports are covering - so I was left with no uniformity to build
this on.
I
figured the easiest way was to fill in this information manually in the
three
fields - giving me the most flexibility, but pull from one table rather
than
use a date field -remember, I don't care what year it is due, just
the
month
and day.

When I go into the relationships window, it shows the three fields
are
linked one-to-many with the table named Months and are also linked
to the
query MonthsQuery (probably because of the combo box link in the
field
properties on the Data Input Form - I'm not sure). When I delete
the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort,
extract and
view the information in the database. Instead of displaying the
name of
the
month, the report displays the month number. The month number
doesn't
follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data
Input
Form, each record displays the month name but when I look directly into
the
table Reports Database Info, the three fields display the numbers
0 - 12 .

As I said before, your last suggestion displays the month name but only
the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more
problems
than
it is worth.

Any suggestions?

Suzanne


:

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query
shows
December as month name - (0 = January and 1 = February - don't know
why
the
Data Input Form uses these numbers, my table uses 1 - January,
2 -
February
-- I'm stumped on this one). Months numbered 2 thru 12, query shows
January
as month name ( 2 thru 12 follows the rest of the months
chronologically
with
12 = Various - - again, don't know why it follows this format,
the
Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if
this
affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com


message
Access 2000 - Set up database that pulls month name from
Month
Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month
name
is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I
look
at
the
data in data view, each record displays these field with
month
name
(from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different
queries for
different
reports, sorted in various ways. The queries and a wizard
generated
report
show MonthDue, PeriodStart and PeriodEnd with month number
not
month
name.
Want to see month name. Doesn't seem to change anything if
I
establish a
relationship or not.

I've searched Access Help, this site and any notes I've
been able
to
put
together but cannot seem to find a fix. Don't have any
books but
am
seriously contemplating Access 2000 for Dummies.

 

[PC Datasheet]

Look at the sample queries I sent in a previous response for how to use
DateSerial.

<<> When I implemented the suggested tables I ran into a problem with
tblReport - there are a couple of situations where the same report name goes
to a different agency. >>
That's not a problem at all! Per your example, just create two records in
TblReport. Both records will have the same ReportName but each record will
have a different AgencyID. As far as the database is concerned, there are
two different reports. In your queries, if you sort by ReportName, you will
see that report listed twice and should recognize both items in the list is
the same report just sent to two different agencies. I suggest you go to a
bookstore and pick up a good book on database design that explains database
concepts well.

<<occasional situation that doesn't follow the norm>>
You are worrying about problems that don't exist. From everything you have
written so far, your database fits in the three tables I gave you. The
field, report frequency, is not needed for example. It can be calculated if
necessary just by deriving the due date of the report from the MonthDue and
DayDue.

If you need help with this database, I can create it for you for a very
reasonable fee. The amount of time it seems you have already spent on ot
would have paid my fee at least a couple times over.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I don't understand what you mean by Date Serial function, when I search for
that phrase in help I don't get it as an option... please help...I don't have
a book. I'm using software that is provided through our LAN system and books
weren't issued. It's been well over five years since I went to class on this
stuff.

When I implemented the suggested tables I ran into a problem with tblReport
- there are a couple of situations where the same report name goes to a
different agency. Such as: Emissions Inventory Report can go to Air Quality
Management Board or the Air Pollution Control District based on the
Installation submitting the report.

I figured I could create tables with the list of names: report names, agency
names, report frequency, etc and then use those tables for the combo list.
None of the tables are linked together. I tried to include fields in the
tables that would hold unique information and would be true no matter what
situation arose but found there weren't too many opportunities for this.

What is the best way to build a database that is flexible enough to deal
with the occasional situation that doesn't follow the norm?

I'll look at the samples to try and determine how to change the month number
to month name. Hoepfully it won't be as painful.

Thanks, Suzanne

The values in combo lists should be derived from the Date Serial function
and you then just sort the dates ascending.

<<When I'm ready to extract reports for the boss that will display a list
of our reports that are due, how do I get rid of the
month number and replace it with the month name? >>
This question is answered in the sample queries.

You are too much focused on the month number and day number! You must use
these in the Date Serial function to derive the report due dates and then
work with the dates and not the month number or day number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Am in the process of making changes - question regarding month...

Identifying the months by their number helps keep things in chronologic
order (combo lists). When I'm ready to extract reports for the boss that
will display a list of our reports that are due, how do I get rid of the
month number and replace it with the month name?

Originally, I had created a table of just the month names (because I could
not get a date field to only display month or month and day without

year)
but

when I used that table as a combo list, it automatically sorted in alpha
order, forcing the months to not be chronological - pushing the need for
numbered months, but the boss doesn't like to see numbered months, he likes
names - he also wanted the data entry person to be able to select the month
by number or month name - but I never got that working.

Thanks for your help, I need to close out this project and move on to the
next, hopefully restructuring the tables will resolve this issue.

Most Appreciative - Suzanne

:

Your table of months is a trouble maker and your idea to scrap it is

a
good

one! I suggest the following tables:
TblAgency
AgencyID
AgencyName

TblReport
ReportID
AgencyID
ReportName
OnlyUponRequest Yes/No

TblReportDue
ReportDueID
ReportID
ReportDueMonth (1 to 12)
ReportDueDay

TblReportDue is necessary because if a report is due multiple times during
the year, you need multiple records to show this in TblReportDue.

All
your

queries that show something about when a report is due need to

include
the

OnlyUponRequest field. If you want to find what reports are due at a certain
month and day, you need to set the criteria of OnlyUponRequest to

False
and

include TblReportDue in your query. If you want to find only the reports
that are due upon request, you would set OnlyUponRequest to True and you
don't need TblReportDue in your query.

Sample queries:
1. To find the reports due in February ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Include the field,
ReportDueMonth, and set its criteria to 2. Include other fields as needed.
2. To find reports due today---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:

ReportDueDateateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])
and

set the criteria to:
Date()
3. To find reports due tomorrow---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:

ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D

ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:

ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay])
and

set the criteria to:
Date() + 1
4. To find reports due in next 30 days ---
Create a query and include TblReport and TblReportDue. Include the field,
OnlyUponRequest , and set the criteria to False. Enter the following
expression in a blank field in the query:

ReportDueYear:IIF(DateSerial(Year(Date()),[ReportDueMonth],[ReportDueDay])>D

ate(),Year(Date())+1,Year(Date()))
Enter the following expression in the next blank field in the query:

ReportDueDateateSerial(ReportDueYear,[ReportDueMonth],[ReportDueDay])
and

set the criteria to:
Between Date() + 1 And Date() + 30


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
The database is for tracking the due dates of reports that we

provide
to

regulatory and outside agencies (close to 400 reports annually). These
reports are due the same month/day each year so I did not want to mess
around
with year dates, just month and day due. Occassionally, some of the
reports
are due only when an agency requests, so we came up with 13

months,
the

normal 12 and "Varies" for the 13th to cover the reports that are

due
upon

request.

I have a table named Months which consists of the fields

MonthNumber
(PK)

and MonthName. I created a form for data entry called Reports Database
Info.
Three fields in this table use a combo list from the MonthsQuery

to
give

a
drop down menu of the months in chronological order, starting with January
and ending with Varies. The field names are MonthDue, PeriodStart and
PeriodEnd. MonthDue is self-explanatory. PeriodStart and

PeriodEnd
cover

the frequency of the report based on requirement of Monthly, Quarterly,
Semi-Annual, Annual etc. Originally, I wanted to autofill these fields
depending on first quarter (Jan-Mar) second quarter (Apr-Jun) etc but
discovered the reports didn't always follow a specific quarter or even
annual
- some annual are Oct thru Sept, Jan thru Dec, or July thru June etc.
Sometimes, the months due and periods covered are based on the locations
the
reports are covering - so I was left with no uniformity to build

this
on.

I
figured the easiest way was to fill in this information manually

in
the

three
fields - giving me the most flexibility, but pull from one table rather
than
use a date field -remember, I don't care what year it is due, just the
month
and day.

When I go into the relationships window, it shows the three fields are
linked one-to-many with the table named Months and are also linked

to
the

query MonthsQuery (probably because of the combo box link in the field
properties on the Data Input Form - I'm not sure). When I delete the
MonthsQuery relationship and close the window, the relationship
re-establishes.

I'm just now creating different reports and queries to sort,

extract
and

view the information in the database. Instead of displaying the

name
of

the
month, the report displays the month number. The month number doesn't
follow
my 1-13 numbering system (primary key), it follows a 0-12. On the Data
Input
Form, each record displays the month name but when I look directly into
the
table Reports Database Info, the three fields display the numbers

0 -
12 .

As I said before, your last suggestion displays the month name but only
the
names December and January, and does not follow the number logic.

I'm debating on scraping the whole primary key and just making the
information the month name only. It seems to be causing more problems
than
it is worth.

Any suggestions?

Suzanne


:

How do you get 13 months???
Are your month numbers number data type or text? Must be number!
Do you have a textbox on your report whose controlsource is MonthName?

Did it - doesn't work right. For months numbered 0 and 1 the query
shows
December as month name - (0 = January and 1 = February - don't know
why
the
Data Input Form uses these numbers, my table uses 1 - January, 2 -
February
-- I'm stumped on this one). Months numbered 2 thru 12,

query
shows

January
as month name ( 2 thru 12 follows the rest of the months
chronologically
with
12 = Various - - again, don't know why it follows this format, the
Month
table uses numbers 1 thru 13 NOT 0 thru 12).

I had set up the query to sort based on Month. Don't know if this
affects
how the month names display in the query.

Suzanne

:

In your query leave MonthDue but add this in an empty field:
MonthName:Format([MonthDue],"mmmm")
Use the MonthName field rather than MonthDue in your report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Access 2000 - Set up database that pulls month name from Month
Table
(fields
Month_Name and Month_Number). On the Data Input Form, the month
name
is
displayed for MonthDue, PeriodStart and PeriodEnd fields. When I
look
at
the
data in data view, each record displays these field with month
name
(from
a
MonthQuery). So far, so good - the boss is very happy.

Problem - I am in the process of setting up different

queries
for

different
reports, sorted in various ways. The queries and a wizard
generated
report
show MonthDue, PeriodStart and PeriodEnd with month number not
month
name.
Want to see month name. Doesn't seem to change anything if I
establish a
relationship or not.

I've searched Access Help, this site and any notes I've

been
able

to
put
together but cannot seem to find a fix. Don't have any

books
but

am
seriously contemplating Access 2000 for Dummies.