Filter Report

[Pedro]

Hello everyone
I'd like to filter my report to ask the user to enter only year and month of
a field that has dd-mm-aa and also to filter the actual month. For this one
is sometingg like : (Year([Data]))=[Ano]) AND ((Month([Data]))=[Mês])). I've
tried on my filter properties report but it's not working. Any ideas for
these 2 questions?
Apreciate any help
Tkank you all
Pedro

 

[Ofer Cohen]

Is Data is a Date type field or Text?

Can you post the full SQL?

As a criteria it look that it should work, what do you mean it doesn't work
Do you get the wrong records, do you get an error message?

 

[Pedro]

Hello
1 . It is not a text field but a data Field.

2. A report to show records of this month : The SQL after all the select
fields the SQL ends like this : WHERE (((Year([Data]))=Year(Now())) AND
((Month([Data]))=Month(Now())));

It's working if i put it on my query, but not, if i put on filter properties
of my report wich was better cose i wouldn't have to do another query.

But i would like to have another report to show me only records that the
user enters only month and year, on same box like 2007-01 (January)

3. I never get records. It's always zero records or an error like missing
brakets or parentisis or operator...

Is Data is a Date type field or Text?

Can you post the full SQL?

As a criteria it look that it should work, what do you mean it doesn't work
Do you get the wrong records, do you get an error message?

--
Good Luck
BS"D

Hello everyone
I'd like to filter my report to ask the user to enter only year and month of
a field that has dd-mm-aa and also to filter the actual month. For this one
is sometingg like : (Year([Data]))=[Ano]) AND ((Month([Data]))=[Mês])). I've
tried on my filter properties report but it's not working. Any ideas for
these 2 questions?
Apreciate any help
Tkank you all
Pedro

 

[Ofer Cohen]

You can use this SQL as the report RecordSource, you don't need to create a
new Query

WHERE Year([Data])=[Please select a Year] AND Month([Data])=[Please select a
Month]

The user will be prompt to enter a year and month


--
Good Luck
BS"D

Hello
1 . It is not a text field but a data Field.

2. A report to show records of this month : The SQL after all the select
fields the SQL ends like this : WHERE (((Year([Data]))=Year(Now())) AND
((Month([Data]))=Month(Now())));

It's working if i put it on my query, but not, if i put on filter properties
of my report wich was better cose i wouldn't have to do another query.

But i would like to have another report to show me only records that the
user enters only month and year, on same box like 2007-01 (January)

3. I never get records. It's always zero records or an error like missing
brakets or parentisis or operator...

Is Data is a Date type field or Text?

Can you post the full SQL?

As a criteria it look that it should work, what do you mean it doesn't work
Do you get the wrong records, do you get an error message?

--
Good Luck
BS"D

Hello everyone
I'd like to filter my report to ask the user to enter only year and month of
a field that has dd-mm-aa and also to filter the actual month. For this one
is sometingg like : (Year([Data]))=[Ano]) AND ((Month([Data]))=[Mês])). I've
tried on my filter properties report but it's not working. Any ideas for
these 2 questions?
Apreciate any help
Tkank you all
Pedro

 

[Pedro]

Hello again Ofer
It makes sense what you said, although i'm not an expert. One (dum) question
: Isn't it on record source tne name of the query?
If i put that sql instrucion on filter it doesn't work. Error message about
missing operator with and without WHERE and =
Any ideas?
Tks in advance
Pedro

You can use this SQL as the report RecordSource, you don't need to create a
new Query

WHERE Year([Data])=[Please select a Year] AND Month([Data])=[Please select a
Month]

The user will be prompt to enter a year and month


--
Good Luck
BS"D

Hello
1 . It is not a text field but a data Field.

2. A report to show records of this month : The SQL after all the select
fields the SQL ends like this : WHERE (((Year([Data]))=Year(Now())) AND
((Month([Data]))=Month(Now())));

It's working if i put it on my query, but not, if i put on filter properties
of my report wich was better cose i wouldn't have to do another query.

But i would like to have another report to show me only records that the
user enters only month and year, on same box like 2007-01 (January)

3. I never get records. It's always zero records or an error like missing
brakets or parentisis or operator...

Is Data is a Date type field or Text?

Can you post the full SQL?

As a criteria it look that it should work, what do you mean it doesn't work
Do you get the wrong records, do you get an error message?

--
Good Luck
BS"D


:

Hello everyone
I'd like to filter my report to ask the user to enter only year and month of
a field that has dd-mm-aa and also to filter the actual month. For this one
is sometingg like : (Year([Data]))=[Ano]) AND ((Month([Data]))=[Mês])). I've
tried on my filter properties report but it's not working. Any ideas for
these 2 questions?
Apreciate any help
Tkank you all
Pedro

 

[Ofer Cohen]

Hi Pedro,

Yes, the RecordSource can be based on a query, or just SQL in the
RecordSource that is just as a Query.
Don't use the Filter property of the Report, instead use the RecordSource to
filter the records.
If you need help, please post the SQL you have now, and the name of the
fields you want to filter by.

--
Good Luck
BS"D

Hello again Ofer
It makes sense what you said, although i'm not an expert. One (dum) question
: Isn't it on record source tne name of the query?
If i put that sql instrucion on filter it doesn't work. Error message about
missing operator with and without WHERE and =
Any ideas?
Tks in advance
Pedro

You can use this SQL as the report RecordSource, you don't need to create a
new Query

WHERE Year([Data])=[Please select a Year] AND Month([Data])=[Please select a
Month]

The user will be prompt to enter a year and month


--
Good Luck
BS"D

Hello
1 . It is not a text field but a data Field.

2. A report to show records of this month : The SQL after all the select
fields the SQL ends like this : WHERE (((Year([Data]))=Year(Now())) AND
((Month([Data]))=Month(Now())));

It's working if i put it on my query, but not, if i put on filter properties
of my report wich was better cose i wouldn't have to do another query.

But i would like to have another report to show me only records that the
user enters only month and year, on same box like 2007-01 (January)

3. I never get records. It's always zero records or an error like missing
brakets or parentisis or operator...

:

Is Data is a Date type field or Text?

Can you post the full SQL?

As a criteria it look that it should work, what do you mean it doesn't work
Do you get the wrong records, do you get an error message?

--
Good Luck
BS"D


:

Hello everyone
I'd like to filter my report to ask the user to enter only year and month of
a field that has dd-mm-aa and also to filter the actual month. For this one
is sometingg like : (Year([Data]))=[Ano]) AND ((Month([Data]))=[Mês])). I've
tried on my filter properties report but it's not working. Any ideas for
these 2 questions?
Apreciate any help
Tkank you all
Pedro