How to display Data Based on Current Month

N

NFL

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
K

KARL DEWEY

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
 
N

NFL

Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





KARL DEWEY said:
Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


NFL said:
Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
K

KARL DEWEY

Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





KARL DEWEY said:
Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


NFL said:
Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
N

NFL

SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


KARL DEWEY said:
Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





KARL DEWEY said:
Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
K

KARL DEWEY

Try this --
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE Format([IntialContact],"yyyymm")=Format(Date(),"yyyymm")
ORDER BY MainTable.NPNAME, MainTable.CPNAME;

--
KARL DEWEY
Build a little - Test a little


NFL said:
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


KARL DEWEY said:
Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





:

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
N

NFL

Hello....

When I execute the query, the "InitialContact" appears in the dialog box and
a blank text box. I entered the year and month and get nothing.

KARL DEWEY said:
Try this --
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE Format([IntialContact],"yyyymm")=Format(Date(),"yyyymm")
ORDER BY MainTable.NPNAME, MainTable.CPNAME;

--
KARL DEWEY
Build a little - Test a little


NFL said:
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


KARL DEWEY said:
Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





:

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
K

KARL DEWEY

InitialContact --- IntialContact - different spelling.
You made a typing error some where as "IntialContact" is a field in your
query.

--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello....

When I execute the query, the "InitialContact" appears in the dialog box and
a blank text box. I entered the year and month and get nothing.

KARL DEWEY said:
Try this --
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE Format([IntialContact],"yyyymm")=Format(Date(),"yyyymm")
ORDER BY MainTable.NPNAME, MainTable.CPNAME;

--
KARL DEWEY
Build a little - Test a little


NFL said:
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


:

Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





:

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
N

NFL

uI didn't see that ... thank you for pointing that out... :)

Now when I open the form, I don't see any records at all. How can I filter
the records to show contacts made for the current month. In this case it
would be March 2009. I know I have at least 8 records for March and a couple
for Feb. So, when the month of April comes up, there should be no records
to show for the upcoming month. I don't get a dialog box now as I did
before.

KARL DEWEY said:
InitialContact --- IntialContact - different spelling.
You made a typing error some where as "IntialContact" is a field in your
query.

--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello....

When I execute the query, the "InitialContact" appears in the dialog box and
a blank text box. I entered the year and month and get nothing.

KARL DEWEY said:
Try this --
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE Format([IntialContact],"yyyymm")=Format(Date(),"yyyymm")
ORDER BY MainTable.NPNAME, MainTable.CPNAME;

--
KARL DEWEY
Build a little - Test a little


:

SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


:

Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





:

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 
K

KARL DEWEY

Post the SQL of your latest query.
--
KARL DEWEY
Build a little - Test a little


NFL said:
uI didn't see that ... thank you for pointing that out... :)

Now when I open the form, I don't see any records at all. How can I filter
the records to show contacts made for the current month. In this case it
would be March 2009. I know I have at least 8 records for March and a couple
for Feb. So, when the month of April comes up, there should be no records
to show for the upcoming month. I don't get a dialog box now as I did
before.

KARL DEWEY said:
InitialContact --- IntialContact - different spelling.
You made a typing error some where as "IntialContact" is a field in your
query.

--
KARL DEWEY
Build a little - Test a little


NFL said:
Hello....

When I execute the query, the "InitialContact" appears in the dialog box and
a blank text box. I entered the year and month and get nothing.

:

Try this --
SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE Format([IntialContact],"yyyymm")=Format(Date(),"yyyymm")
ORDER BY MainTable.NPNAME, MainTable.CPNAME;

--
KARL DEWEY
Build a little - Test a little


:

SELECT MainTable.IntialContact, MainTable.NPNAME, MainTable.CPNAME,
MainTable.ControlNbr, MainTable.Notes, MainTable.CSENO
FROM MainTable
WHERE (((Format([Enter Year, Month "yyyy
mmm"],"yyyymm"))=Format(Date(),"yyyymm")))
ORDER BY MainTable.NPNAME, MainTable.CPNAME;


:

Post the SQL of your query by opening it in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello and thank you for the quick response.

The process appears to work, but I have some questions. The first one is
where is this condition looking? For example, before the form opens it asks
the user to enter the year and date. In this case I entered "2009 Mar". It
works perfect, but now let's I modify a record on the current month and
change the date to "2009 Feb". I exit the form and reopen the form and see
if that same record will appear for Feb. For some reason that record didn't
move to Feb. The name of my field is called "InitialContact". I thought
[YourDateField] would be where I enter the name of my field, but it only puts
information on the dialog box before the form opens.

Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")

Thank you!





:

Same query used every month.
In design view of query scroll to the right to new block field row in the
grid and add calculated field like this --
Year_Month: Format([YourDateField], "yyyymm")
Click on the check box to turn off display.
In criteria row enter --
Format(Date(), "yyyymm")
You will only get records from current month and year.
--
KARL DEWEY
Build a little - Test a little


:

Is there a way to separate data on a form by month? For example, a person is
working on the current month and see all records created for that month and
when the upcoming up arrives, there should be no record to display (unless
records from the previous year appears and those would be purged. I'm not
sure if I need to create a form for each month and/or create a query for each
month.

Thank you,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top