467,894 Members | 1,577 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

Report query problem

Hi

I have a report with an underlying query. The criteria is coming from a
field on the form which calls the report. So the query is 'select * from
mytable where mytable.myfield = [Forms]![myform]![myfield]'. The problem is
that the form field doe snot contain a single value like x but 'x or y'.
This does not work in a query if the form field has this value. How do I get
the report to work with two or more values connected by an 'or' operator?

Thanks

Regards
Nov 13 '05 #1
  • viewed: 1093
Share:
4 Replies
On Sun, 31 Oct 2004 01:05:56 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Simply concatenate the entire sql statement. For example in the
Report_Open event of your report you can write:
Me.RecordSource = "select * from mytable where mytable.myfield in (" &
[Forms]![myform]![myfield] & ")"
Note that I'm concatenating 3 substrings to create the RecordSource.

The IN-clause requires comma-separated values, for example:
2,3
So that's what you would enter in your field.

Note that this is not legal SQL:
select * from mytable where mytable.myfield = 2 or 3
This should be:
select * from mytable where mytable.myfield=2 or mytable.myfield=3

Note that if you have text rather than numbers, quotes may be needed.
For example your field would contain:
'aaa','bbb'
and a sql statement with OR would read:
select * from mytable where mytable.myfield='aaa' or
mytable.myfield='bbb'

-Tom.

Hi

I have a report with an underlying query. The criteria is coming from a
field on the form which calls the report. So the query is 'select * from
mytable where mytable.myfield = [Forms]![myform]![myfield]'. The problem is
that the form field doe snot contain a single value like x but 'x or y'.
This does not work in a query if the form field has this value. How do I get
the report to work with two or more values connected by an 'or' operator?

Thanks

Regards


Nov 13 '05 #2
How do I assign the constructed sql as report data source?

Thanks

Regards
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:0b********************************@4ax.com...
On Sun, 31 Oct 2004 01:05:56 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Simply concatenate the entire sql statement. For example in the
Report_Open event of your report you can write:
Me.RecordSource = "select * from mytable where mytable.myfield in (" &
[Forms]![myform]![myfield] & ")"
Note that I'm concatenating 3 substrings to create the RecordSource.

The IN-clause requires comma-separated values, for example:
2,3
So that's what you would enter in your field.

Note that this is not legal SQL:
select * from mytable where mytable.myfield = 2 or 3
This should be:
select * from mytable where mytable.myfield=2 or mytable.myfield=3

Note that if you have text rather than numbers, quotes may be needed.
For example your field would contain:
'aaa','bbb'
and a sql statement with OR would read:
select * from mytable where mytable.myfield='aaa' or
mytable.myfield='bbb'

-Tom.

Hi

I have a report with an underlying query. The criteria is coming from a
field on the form which calls the report. So the query is 'select * from
mytable where mytable.myfield = [Forms]![myform]![myfield]'. The problem isthat the form field doe snot contain a single value like x but 'x or y'.
This does not work in a query if the form field has this value. How do I getthe report to work with two or more values connected by an 'or' operator?

Thanks

Regards

Nov 13 '05 #3
On Sun, 31 Oct 2004 03:19:52 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Re-read the first paragraph of my original reply.
-Tom.
How do I assign the constructed sql as report data source?

Thanks

Regards

<clip>

Nov 13 '05 #4
Great. Thanks.

Regards

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:qm********************************@4ax.com...
On Sun, 31 Oct 2004 03:19:52 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Re-read the first paragraph of my original reply.
-Tom.
How do I assign the constructed sql as report data source?

Thanks

Regards

<clip>

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Grant Stanley | last post: by
3 posts views Thread by Grim Reaper | last post: by
8 posts views Thread by David Horsman | last post: by
12 posts views Thread by jkearns | last post: by
5 posts views Thread by David L. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.