monthly expiry

[Tia]

Hi,
First of everything i would like t thank you all for your continuos
support. i made me know better about access and how to start using
it .

I have an sql that give me todays end of probation reminder, what i
need is to get it on monthly basis i tried the month() but it didnt
work

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Date()

Thank you for your support

 

[John W. Vinson]

Hi,
First of everything i would like t thank you all for your continuos
support. i made me know better about access and how to start using
it .

I have an sql that give me todays end of probation reminder, what i
need is to get it on monthly basis i tried the month() but it didnt
work

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Date()

Thank you for your support

Please explain by "get it on a monthly basis".

If you were to run the query today, which dates in the [End Of Probation
Period] field would you expect to see? Those for this month, those for last
month, those for the coming month?

 

[Daryl S]

Tia -

If you want all records where the End of Probation Period is in the current
month, try this:

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE Month([End of Probation Period]) =Month(Date())
AND Year([End of Probation Period]) =Year(Date())

 

[Mark A. Sam]

Tia,

The Month() function needs an argument, being a field or expression
representing a date, like Month(Date()) or Month([SomeDateField)

Not knowing your app, I am guessing you might want:

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Month(Date())

Which would select the [End of Probation Period] for the month of the
current day.

God Bless,

Mark A. Sam

 

[John W. Vinson]

Tia,

The Month() function needs an argument, being a field or expression
representing a date, like Month(Date()) or Month([SomeDateField)

Not knowing your app, I am guessing you might want:

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Month(Date())

Which would select the [End of Probation Period] for the month of the
current day.

But it does assume that [End of probation period] is an Integer field with a
value between 1 (January) and 12 (December). Tia will need to explain her data
structure to be sure, but if [End of probation period] is a Date/Time field
she won't get the desired result!

A criterion

[End of Probation Period] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
AND [End of Probation Period] < DateSerial(Year(Date()), Month(Date()) + 2, 1)

will get all record during the upcoming month (December 2009 if you run the
query today, January 2010 if it's run during this next December).

 

[Mark A. Sam]

John,
I agree. I didn't want to read into it more than was given. By its
application, it seems as though it is.
God Bless,

Mark

Tia,

The Month() function needs an argument, being a field or expression
representing a date, like Month(Date()) or Month([SomeDateField)

Not knowing your app, I am guessing you might want:

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Month(Date())

Which would select the [End of Probation Period] for the month of the
current day.

But it does assume that [End of probation period] is an Integer field with
a
value between 1 (January) and 12 (December). Tia will need to explain her
data
structure to be sure, but if [End of probation period] is a Date/Time
field
she won't get the desired result!

A criterion

[End of Probation Period] >= DateSerial(Year(Date()), Month(Date()) + 1,
1)
AND [End of Probation Period] < DateSerial(Year(Date()), Month(Date()) +
2, 1)

will get all record during the upcoming month (December 2009 if you run
the
query today, January 2010 if it's run during this next December).

 

[Tia]

Tia,

The Month() function needs an argument, being a field or expression
representing a date, like Month(Date())  or Month([SomeDateField)

Not knowing your app, I am guessing you might want:

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Month(Date())

Which would select the [End of Probation Period] for the month of the
current day.

But it does assume that [End of probation period] is an Integer field with a
value between 1 (January) and 12 (December). Tia will need to explain herdata
structure to be sure, but if [End of probation period] is a Date/Time field
she won't get the desired result!

A criterion

[End of Probation Period] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
AND [End of Probation Period] < DateSerial(Year(Date()), Month(Date()) + 2, 1)

will get all record during the upcoming month (December 2009 if you run the
query today, January 2010 if it's run during this next December).

hello,
when i am using this one it will not be in sql but as a query and no
data is shown it is emty but once i put it back the way it was remider
per day not month or <=day i am getting data

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE Month([End of Probation Period]) =Month(Date())
AND Year([End of Probation Period]) =Year(Date())


and i tried without the year as well, same story, no data is displayed
i am getting data only when i am using this
SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Date()

Please help

 

[John W. Vinson]

[End of Probation Period] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
AND [End of Probation Period] < DateSerial(Year(Date()), Month(Date()) + 2, 1)

will get all record during the upcoming month (December 2009 if you run the
query today, January 2010 if it's run during this next December).

hello,
when i am using this one it will not be in sql but as a query and no
data is shown it is emty but once i put it back the way it was remider
per day not month or <=day i am getting datas

Queries ARE SQL.

SQL is the language in which queries are written and stored in your database.
The query grid is not a query; it's a tool designed to build a SQL string.

I have no idea what the sentence above means. "put it back the way it was" -
you may know how it was, I don't.

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE Month([End of Probation Period]) =Month(Date())
AND Year([End of Probation Period]) =Year(Date())

Of [End of probation period] is a non-NULL date/time field this should show
all records with a date during November 2009.

and i tried without the year as well, same story, no data is displayed
i am getting data only when i am using this
SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Date()

This should retrieve only those records with #11/11/2009# in the [End of
probation period] field.

Might you perhaps have a field named Date (or Month or Year) in your table or
query, or a control with that name on your Form? If so, Access may be getting
confused about whether you mean your field or the builtin function. This is
the main reason to NEVER use these reserved words to name fields or controls!

 

[Tia]

[End of Probation Period] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
AND [End of Probation Period] < DateSerial(Year(Date()), Month(Date())+ 2, 1)
will get all record during the upcoming month (December 2009 if you run the
query today, January 2010 if it's run during this next December).

hello,
when i am using this one it will not be in sql but as a query and no
data is shown it is emty but once i put it back the way it was remider
per day not month or <=day i am getting datas

Queries ARE SQL.

SQL is the language in which queries are written and stored in your database.
The query grid is not a query; it's a tool designed to build a SQL string..

I have no idea what the sentence above means. "put it back the way it was" -
you may know how it was, I don't.

SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE Month([End of Probation Period]) =Month(Date())
AND Year([End of Probation Period]) =Year(Date())

Of [End of probation period] is a non-NULL date/time field this should show
all records with a date during November 2009.

and i tried without the year as well, same story, no data is displayed
i am getting data only when i am using this
SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF
PROBATION" AS DocumentType
FROM [ASSESSEMENT query]
WHERE [End of Probation Period] =Date()

This should retrieve only those records with #11/11/2009# in the [End of
probation period] field.

Might you perhaps have a field named Date (or Month or Year) in your table or
query, or a control with that name on your Form? If so, Access may be getting
confused about whether you mean your field or the builtin function. This is
the main reason to NEVER use these reserved words to name fields or controls!

can u provide me with your email adress so i can attach the file plz
its not working and i dont know what i am doing wrong
Please help
Tia

 

[John W. Vinson]

can u provide me with your email adress so i can attach the file plz
its not working and i dont know what i am doing wrong
Please help

Ordinarily no - I'm a self employed consultant, donating (entirely too much)
time to this free forum; private email support is normally reserved to paying
clients.

But just this once... right click the .mdb file, and choose Send To Compressed
(Zip) Folder, and email it to jvinson <at> wysard of info <dot> com. Remove
the blanks and make the obvious edits. There is some problem that we're just
not communicating!