Need expression for next month's expirations??

G

Guest

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?
 
F

fredg

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?
Click to expand...

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")
 
G

Guest

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
fredg said:
I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?
Click to expand...

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")
Click to expand...
 
F

fredg

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
fredg said:
I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?
Click to expand...

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")
Click to expand...
Click to expand...

You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
 
G

Guest

Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName, [RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

fredg said:
Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
fredg said:
On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")
Click to expand...
Click to expand...

You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
Click to expand...
 
F

fredg

Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName, [RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

fredg said:
Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
:

On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")
Click to expand...

You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
Click to expand...
Click to expand...

Did you move your semi-colon (after FROM [RBWA Enrolment Table]) to
the end of the Where clause?

Is there a field named [RBWA Expiry Date] in the [RBWA Enrolment
Table]?

Are you sure you have spelled the [RBWA Expiry Date] field name
correctly, with the appropriate spaces in the name? (Not really a good
idea to have field or table names that include spaces.)
Are you aware that you have miss-spelled the word "Enrollment"?
It doesn't matter to Access as long as it's always spelled the same
way.

The below SQL works for me.

SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName, [RBWA Enrolment Table].MiddleName, [RBWA Enrolment
Table].LastName, [RBWA Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table]
WHERE (((Format([RBWA Expiry Date],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

Note: Access throws in extra un-needed parenthesis in the Where
clause. Don't worry about them.
 
G

Guest

Well Fred:
[RBWA Enrolment Table]![RBWA Enrolment Date]WHERE
(((Format([RBWAexpirydate],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

This is what I input! I redid the RBWA expiry table as you suggested. No
avail. Can you see anything else I'm doing wrong?? It keeps talking about an
operator without an operand.
Thanks
Mike
fredg said:
Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName, [RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

fredg said:
On Sun, 9 Sep 2007 08:36:01 -0700, SilverTip 147 wrote:

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
:

On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
Click to expand...
Click to expand...

Did you move your semi-colon (after FROM [RBWA Enrolment Table]) to
the end of the Where clause?

Is there a field named [RBWA Expiry Date] in the [RBWA Enrolment
Table]?

Are you sure you have spelled the [RBWA Expiry Date] field name
correctly, with the appropriate spaces in the name? (Not really a good
idea to have field or table names that include spaces.)
Are you aware that you have miss-spelled the word "Enrollment"?
It doesn't matter to Access as long as it's always spelled the same
way.

The below SQL works for me.

SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName, [RBWA Enrolment Table].MiddleName, [RBWA Enrolment
Table].LastName, [RBWA Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table]
WHERE (((Format([RBWA Expiry Date],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

Note: Access throws in extra un-needed parenthesis in the Where
clause. Don't worry about them.
Click to expand...
 
F

fredg

Well Fred:
[RBWA Enrolment Table]![RBWA Enrolment Date]WHERE
(((Format([RBWAexpirydate],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

This is what I input! I redid the RBWA expiry table as you suggested. No
avail. Can you see anything else I'm doing wrong?? It keeps talking about an
operator without an operand.
Thanks
Mike
fredg said:
Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName, [RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

:

On Sun, 9 Sep 2007 08:36:01 -0700, SilverTip 147 wrote:

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented an
operand without an operator."

Any suggestions?
Thanks again!
:

On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
Click to expand...

Did you move your semi-colon (after FROM [RBWA Enrolment Table]) to
the end of the Where clause?

Is there a field named [RBWA Expiry Date] in the [RBWA Enrolment
Table]?

Are you sure you have spelled the [RBWA Expiry Date] field name
correctly, with the appropriate spaces in the name? (Not really a good
idea to have field or table names that include spaces.)
Are you aware that you have miss-spelled the word "Enrollment"?
It doesn't matter to Access as long as it's always spelled the same
way.

The below SQL works for me.

SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName, [RBWA Enrolment Table].MiddleName, [RBWA Enrolment
Table].LastName, [RBWA Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table]
WHERE (((Format([RBWA Expiry Date],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

Note: Access throws in extra un-needed parenthesis in the Where
clause. Don't worry about them.
Click to expand...
Click to expand...

You need a space here v
[RBWA Enrolment Table]![RBWA Enrolment Date]WHERE

should be:
[RBWA Enrolment Table]![RBWA Enrolment Date] WHERE
 
M

Michel Walsh

Are you using the SQL view or the query designer view?

*If* you use the designer view, add a new column, first line, type:


Format( [RBWAexpirydate], "mm/yyyy" )

and in the criteria line, type:

Format( DateAdd("m" , 1, Date()), "mm/yyyy" )




Vanderghast, Access MVP



SilverTip 147 said:
Well Fred:
[RBWA Enrolment Table]![RBWA Enrolment Date]WHERE
(((Format([RBWAexpirydate],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

This is what I input! I redid the RBWA expiry table as you suggested. No
avail. Can you see anything else I'm doing wrong?? It keeps talking about
an
operator without an operand.
Thanks
Mike
fredg said:
Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName,
[RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error
message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

:

On Sun, 9 Sep 2007 08:36:01 -0700, SilverTip 147 wrote:

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented
an
operand without an operator."

Any suggestions?
Thanks again!
:

On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this
query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
Click to expand...

Did you move your semi-colon (after FROM [RBWA Enrolment Table]) to
the end of the Where clause?

Is there a field named [RBWA Expiry Date] in the [RBWA Enrolment
Table]?

Are you sure you have spelled the [RBWA Expiry Date] field name
correctly, with the appropriate spaces in the name? (Not really a good
idea to have field or table names that include spaces.)
Are you aware that you have miss-spelled the word "Enrollment"?
It doesn't matter to Access as long as it's always spelled the same
way.

The below SQL works for me.

SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName, [RBWA Enrolment Table].MiddleName, [RBWA Enrolment
Table].LastName, [RBWA Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table]
WHERE (((Format([RBWA Expiry Date],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

Note: Access throws in extra un-needed parenthesis in the Where
clause. Don't worry about them.
Click to expand...
Click to expand...
 
G

Guest

Michel Walsh and Fred:
Thank you both for your input!1 i finally got it to work using Michel's
suggestion!
People like you guys make my day!!
Thanks again!
Mike

Michel Walsh said:
Are you using the SQL view or the query designer view?

*If* you use the designer view, add a new column, first line, type:


Format( [RBWAexpirydate], "mm/yyyy" )

and in the criteria line, type:

Format( DateAdd("m" , 1, Date()), "mm/yyyy" )




Vanderghast, Access MVP



SilverTip 147 said:
Well Fred:
[RBWA Enrolment Table]![RBWA Enrolment Date]WHERE
(((Format([RBWAexpirydate],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

This is what I input! I redid the RBWA expiry table as you suggested. No
avail. Can you see anything else I'm doing wrong?? It keeps talking about
an
operator without an operand.
Thanks
Mike
fredg said:
On Sun, 9 Sep 2007 09:38:03 -0700, SilverTip 147 wrote:

Fred:
Here it is:
SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName,
[RBWA Enrolment Table].MiddleName, [RBWA Enrolment Table].LastName,
[RBWA
Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table];

I did not add on any of your info as it would only give me an error
message.

I added the following to it through "Build"
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

Mike

:

On Sun, 9 Sep 2007 08:36:01 -0700, SilverTip 147 wrote:

Thanks Fred for replying!!
However, it's not working yet!
This is what i typed:

[RBWA Enrolment Table] Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

This is the error message I get:
"The expression you entered contains invalid syntax. You have ented
an
operand without an operator."

Any suggestions?
Thanks again!
:

On Sat, 8 Sep 2007 05:42:01 -0700, SilverTip 147 wrote:

I'm using Access 2003.
I am a new user who si trying to build an expression for this
query:
Whose membership expires next month??
So far I have: [RBWA Enrolment Table]![RBWA Expiry Date]

How do I finish this expression?
I am using the short form for dates?
Thank you for your help?

Select [RBWA Enrolment Table].* from [RBWA Enrolment Table]
Where Format([RBWA Expiry Date],"mm/yyyy") =
Format(DateAdd("m",1,Date()),"mm/yyyy")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


You have posted an incomplete statement.
In Query Design View, click on View + SQL View,
Please copy and paste into a reply message your EXACT complete query
SQL.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Did you move your semi-colon (after FROM [RBWA Enrolment Table]) to
the end of the Where clause?

Is there a field named [RBWA Expiry Date] in the [RBWA Enrolment
Table]?

Are you sure you have spelled the [RBWA Expiry Date] field name
correctly, with the appropriate spaces in the name? (Not really a good
idea to have field or table names that include spaces.)
Are you aware that you have miss-spelled the word "Enrollment"?
It doesn't matter to Access as long as it's always spelled the same
way.

The below SQL works for me.

SELECT [RBWA Enrolment Table].[ID Number], [RBWA Enrolment
Table].FirstName, [RBWA Enrolment Table].MiddleName, [RBWA Enrolment
Table].LastName, [RBWA Enrolment Table].[RBWA Enrolment Date]
FROM [RBWA Enrolment Table]
WHERE (((Format([RBWA Expiry Date],"mm/yyyy")) =
Format(DateAdd("m",1,Date()),"mm/yyyy")));

Note: Access throws in extra un-needed parenthesis in the Where
clause. Don't worry about them.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Filter out records with earlier dates 1
Use DATE in query expression 1
Converting concactenated fields to date 6
Expression help 1
End of Month Expression for Query Question 4
Filter for Previous Month's Records 5
Specified Expression/Aggregate Funciton error 5
Building Expression for an Update Query 1

Top