Critical Help needed please on query date

[Guest]

I am going insane with this - i have built a database for a garage which
tracks customer mots - (date passed) - (date remind due) etc. i need to do a
query to search on (date remind due)- which will open from switchboard which
will then open a box so i can just put a month in (ie aug 07) and it will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it worked
that way but the problem being - i need it to be able to do it by just
entering the month in the search box - and it will then fetch up whatever
month is stipulated. without physically going in and changing the criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[David F Cox]

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())

 

[Guest]

thanks for quick reply but i am pretty much a newbie to this could you tell
me where i should put this code - does it all go into the `criteria` and do i
need to adjust any of it with my own? I tried pasting it in criteria as it is
and its fetching up error - (You may have entered an operand without an
operator )

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())

I am going insane with this - i have built a database for a garage which
tracks customer mots - (date passed) - (date remind due) etc. i need to do
a
query to search on (date remind due)- which will open from switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by just
entering the month in the search box - and it will then fetch up whatever
month is stipulated. without physically going in and changing the criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[Guest]

I took out the `where` at the beggining and entered it into criteria - then i
get the (enterr parameter value - yourdate) box followed by (Enter parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

thanks for quick reply but i am pretty much a newbie to this could you tell
me where i should put this code - does it all go into the `criteria` and do i
need to adjust any of it with my own? I tried pasting it in criteria as it is
and its fetching up error - (You may have entered an operand without an
operator )

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())

I am going insane with this - i have built a database for a garage which
tracks customer mots - (date passed) - (date remind due) etc. i need to do
a
query to search on (date remind due)- which will open from switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by just
entering the month in the search box - and it will then fetch up whatever
month is stipulated. without physically going in and changing the criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[David F Cox]

[Yourdate] should be replaced by the name of your date field. I could not
use your fieldname because you have not told me what it is. I should have
been more explicit, it is difficult to guess how much knowledge a questioner
has.

ley us know how you get on.

I took out the `where` at the beggining and entered it into criteria - then
i
get the (enterr parameter value - yourdate) box followed by (Enter
parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

thanks for quick reply but i am pretty much a newbie to this could you
tell
me where i should put this code - does it all go into the `criteria` and
do i
need to adjust any of it with my own? I tried pasting it in criteria as
it is
and its fetching up error - (You may have entered an operand without an
operator )

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())


I am going insane with this - i have built a database for a garage
which
tracks customer mots - (date passed) - (date remind due) etc. i need
to do
a
query to search on (date remind due)- which will open from
switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it
will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by
just
entering the month in the search box - and it will then fetch up
whatever
month is stipulated. without physically going in and changing the
criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[Guest]

Hi i have entered the whole thing into criteria and changed the (yourdate) to
my field in database which is - Date Remind Due - My database is called MOT
Reminders.
its telling me iv entered a operand without an operator - i too out the
`WHERE` at the beggining of your code and it accepted that - But then i get a
Enter date prompt box Which i put in the year - 4 digits - then i get a Enter
month as 3 letters so i enter `Nov` for instance and it fetches up a dataview
with no data - iv tried it everywhich way. Is there something else that i
should be doing?? im new with query stuff.

[Yourdate] should be replaced by the name of your date field. I could not
use your fieldname because you have not told me what it is. I should have
been more explicit, it is difficult to guess how much knowledge a questioner
has.

ley us know how you get on.

I took out the `where` at the beggining and entered it into criteria - then
i
get the (enterr parameter value - yourdate) box followed by (Enter
parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

thanks for quick reply but i am pretty much a newbie to this could you
tell
me where i should put this code - does it all go into the `criteria` and
do i
need to adjust any of it with my own? I tried pasting it in criteria as
it is
and its fetching up error - (You may have entered an operand without an
operator )

:

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())


I am going insane with this - i have built a database for a garage
which
tracks customer mots - (date passed) - (date remind due) etc. i need
to do
a
query to search on (date remind due)- which will open from
switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it
will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by
just
entering the month in the search box - and it will then fetch up
whatever
month is stipulated. without physically going in and changing the
criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[Guest]

I copied this If this helps -- If not would it be possible to be able to
retrive them just on a todays date?

SELECT [Mot Reminders].Name, [Mot Reminders].[Mot Remind date], [Mot
Reminders].[Date Mot Due], ([month]) AS Expr1, [Mot Reminders].[Mot Remind
date], [Mot Reminders].[Mot Remind date], [Mot Reminders].Month AS [Month
Due], [Mot Reminders].[Mot Remind date]
FROM [Mot Reminders]
WHERE ((Format([Mot Remind Due],"mmm")=[Enter month as 3 letters] And
Format([Mot Remind Due],"yyyy")=Year(Date())));

[Yourdate] should be replaced by the name of your date field. I could not
use your fieldname because you have not told me what it is. I should have
been more explicit, it is difficult to guess how much knowledge a questioner
has.

ley us know how you get on.

I took out the `where` at the beggining and entered it into criteria - then
i
get the (enterr parameter value - yourdate) box followed by (Enter
parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

thanks for quick reply but i am pretty much a newbie to this could you
tell
me where i should put this code - does it all go into the `criteria` and
do i
need to adjust any of it with my own? I tried pasting it in criteria as
it is
and its fetching up error - (You may have entered an operand without an
operator )

:

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())


I am going insane with this - i have built a database for a garage
which
tracks customer mots - (date passed) - (date remind due) etc. i need
to do
a
query to search on (date remind due)- which will open from
switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it
will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by
just
entering the month in the search box - and it will then fetch up
whatever
month is stipulated. without physically going in and changing the
criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[Guest]

Ok I MANAGED TO GET SOME DATA BUT ITS FETCHING UP ALL RECORDS IV COPIED WHAT
I GET

NameMot Remind date Date Mot Due Expr1 Expr1004
Victoria 21/02/2007 07/03/2007 Mar 2006
Mr Hart 22/02/2007 08/03/2007 Mar 2006
Mr Mo 24/02/2007 11/03/2007 Mar 2006
P smith 01/09/2006 01/09/2007 Mar 2006
mr smith 02/03/2006 02/04/2006 Mar 2006

Hi i have entered the whole thing into criteria and changed the (yourdate) to
my field in database which is - Date Remind Due - My database is called MOT
Reminders.
its telling me iv entered a operand without an operator - i too out the
`WHERE` at the beggining of your code and it accepted that - But then i get a
Enter date prompt box Which i put in the year - 4 digits - then i get a Enter
month as 3 letters so i enter `Nov` for instance and it fetches up a dataview
with no data - iv tried it everywhich way. Is there something else that i
should be doing?? im new with query stuff.

[Yourdate] should be replaced by the name of your date field. I could not
use your fieldname because you have not told me what it is. I should have
been more explicit, it is difficult to guess how much knowledge a questioner
has.

ley us know how you get on.

I took out the `where` at the beggining and entered it into criteria - then
i
get the (enterr parameter value - yourdate) box followed by (Enter
parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

:

thanks for quick reply but i am pretty much a newbie to this could you
tell
me where i should put this code - does it all go into the `criteria` and
do i
need to adjust any of it with my own? I tried pasting it in criteria as
it is
and its fetching up error - (You may have entered an operand without an
operator )

:

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())


I am going insane with this - i have built a database for a garage
which
tracks customer mots - (date passed) - (date remind due) etc. i need
to do
a
query to search on (date remind due)- which will open from
switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it
will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by
just
entering the month in the search box - and it will then fetch up
whatever
month is stipulated. without physically going in and changing the
criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.

 

[Guest]

OK iv managed to get it to work slightly - but it will only fetch up certain
months - ie if i enter NOV - MAR it fetches data - but if i enter FEB it wont
- even though there are records for feb - also its not asking me for year its
just bringing up 2006 records - not 2007

I copied this If this helps -- If not would it be possible to be able to
retrive them just on a todays date?

SELECT [Mot Reminders].Name, [Mot Reminders].[Mot Remind date], [Mot
Reminders].[Date Mot Due], ([month]) AS Expr1, [Mot Reminders].[Mot Remind
date], [Mot Reminders].[Mot Remind date], [Mot Reminders].Month AS [Month
Due], [Mot Reminders].[Mot Remind date]
FROM [Mot Reminders]
WHERE ((Format([Mot Remind Due],"mmm")=[Enter month as 3 letters] And
Format([Mot Remind Due],"yyyy")=Year(Date())));

[Yourdate] should be replaced by the name of your date field. I could not
use your fieldname because you have not told me what it is. I should have
been more explicit, it is difficult to guess how much knowledge a questioner
has.

ley us know how you get on.

I took out the `where` at the beggining and entered it into criteria - then
i
get the (enterr parameter value - yourdate) box followed by (Enter
parameter
value - enter month as 3 letters) - this opens dataview but theres no data
only headings

:

thanks for quick reply but i am pretty much a newbie to this could you
tell
me where i should put this code - does it all go into the `criteria` and
do i
need to adjust any of it with my own? I tried pasting it in criteria as
it is
and its fetching up error - (You may have entered an operand without an
operator )

:

WHERE FORMAT([yourdate],"MMM") = [Enter month as 3 letters] AND
FORMAT([yourdate],"yyyy") = YEAR(DATE())


I am going insane with this - i have built a database for a garage
which
tracks customer mots - (date passed) - (date remind due) etc. i need
to do
a
query to search on (date remind due)- which will open from
switchboard
which
will then open a box so i can just put a month in (ie aug 07) and it
will
return only the records for aug 07.
i have been able to put a (between dates) code in the criteria and it
worked
that way but the problem being - i need it to be able to do it by
just
entering the month in the search box - and it will then fetch up
whatever
month is stipulated. without physically going in and changing the
criteria
everytime.
the date format on my table is dd/mm/yy - i need a search of mmm/yy
its been driving me crazy for days and i cant sleep now please help.