Set Query Criteria from Form

G

Guest

Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like to be
able to click a button on a form that would set the criteria for a field in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be populated by
code and that used as the criteria in a parameter query. I found out that I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
 
G

Graham Mandeno

Hi Joel

Design your report to be based on a query without parameters, listing all
possible records. Then, in your form, build a WHERE clause that is based on
the options that have been selected and pass that to the report through the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
 
G

Guest

Graham:

WOW!...Thanks for the concept and specific code!

I see what you code is doing.

I have an example of my code below but I have some syntax
problem...something about single quotes for text but I can't seem to get it
right. Any suggestions??

Dim sWhere as String

sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR (>="1601800"
And <="1602316" And <>"1601920")"

DoCmd.OpenReport "rptOrders", , , sWhere

Thanks so much and Cheers!

Joel

Graham Mandeno said:
Hi Joel

Design your report to be based on a query without parameters, listing all
possible records. Then, in your form, build a WHERE clause that is based on
the options that have been selected and pass that to the report through the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Joel said:
Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like to be
able to click a button on a form that would set the criteria for a field
in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be populated by
code and that used as the criteria in a parameter query. I found out that
I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
Click to expand...
Click to expand...
 
D

Douglas J. Steele

sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215')
OR ([Dept Acct Number]>='1601800'
And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')"

or

sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept
Acct Number] BETWEEN '1601800'
And '1602316') And [Dept Acct Number]<>'1601920')"

Note that it's necessary to repeat the field name for each specific check.
Note, too, that I changed all of the double quotes inside the string to
single quotes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Graham:

WOW!...Thanks for the concept and specific code!

I see what you code is doing.

I have an example of my code below but I have some syntax
problem...something about single quotes for text but I can't seem to get
it
right. Any suggestions??

Dim sWhere as String

sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR
(>="1601800"
And <="1602316" And <>"1601920")"

DoCmd.OpenReport "rptOrders", , , sWhere

Thanks so much and Cheers!

Joel

Graham Mandeno said:
Hi Joel

Design your report to be based on a query without parameters, listing all
possible records. Then, in your form, build a WHERE clause that is based
on
the options that have been selected and pass that to the report through
the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Joel said:
Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like to
be
able to click a button on a form that would set the criteria for a
field
in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be populated
by
code and that used as the criteria in a parameter query. I found out
that
I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an
existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Douglas:

(Added a ( after the first quote)... Works fine!

Joel

Douglas J. Steele said:
sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215')
OR ([Dept Acct Number]>='1601800'
And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')"

or

sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept
Acct Number] BETWEEN '1601800'
And '1602316') And [Dept Acct Number]<>'1601920')"

Note that it's necessary to repeat the field name for each specific check.
Note, too, that I changed all of the double quotes inside the string to
single quotes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Graham:

WOW!...Thanks for the concept and specific code!

I see what you code is doing.

I have an example of my code below but I have some syntax
problem...something about single quotes for text but I can't seem to get
it
right. Any suggestions??

Dim sWhere as String

sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR
(>="1601800"
And <="1602316" And <>"1601920")"

DoCmd.OpenReport "rptOrders", , , sWhere

Thanks so much and Cheers!

Joel

Graham Mandeno said:
Hi Joel

Design your report to be based on a query without parameters, listing all
possible records. Then, in your form, build a WHERE clause that is based
on
the options that have been selected and pass that to the report through
the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like to
be
able to click a button on a form that would set the criteria for a
field
in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be populated
by
code and that used as the criteria in a parameter query. I found out
that
I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an
existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Oops, you're right. That's required too. Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Thanks Douglas:

(Added a ( after the first quote)... Works fine!

Joel

Douglas J. Steele said:
sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct
Number]<='1601215')
OR ([Dept Acct Number]>='1601800'
And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')"

or

sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept
Acct Number] BETWEEN '1601800'
And '1602316') And [Dept Acct Number]<>'1601920')"

Note that it's necessary to repeat the field name for each specific
check.
Note, too, that I changed all of the double quotes inside the string to
single quotes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Graham:

WOW!...Thanks for the concept and specific code!

I see what you code is doing.

I have an example of my code below but I have some syntax
problem...something about single quotes for text but I can't seem to
get
it
right. Any suggestions??

Dim sWhere as String

sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR
(>="1601800"
And <="1602316" And <>"1601920")"

DoCmd.OpenReport "rptOrders", , , sWhere

Thanks so much and Cheers!

Joel

:

Hi Joel

Design your report to be based on a query without parameters, listing
all
possible records. Then, in your form, build a WHERE clause that is
based
on
the options that have been selected and pass that to the report
through
the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) &
cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) &
cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like
to
be
able to click a button on a form that would set the criteria for a
field
in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be
populated
by
code and that used as the criteria in a parameter query. I found
out
that
I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an
existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

Thanks for jumping in, Doug. It was 3am in this part of the world!
:)

G.

Douglas J. Steele said:
sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215')
OR ([Dept Acct Number]>='1601800'
And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')"

or

sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept
Acct Number] BETWEEN '1601800'
And '1602316') And [Dept Acct Number]<>'1601920')"

Note that it's necessary to repeat the field name for each specific check.
Note, too, that I changed all of the double quotes inside the string to
single quotes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joel said:
Graham:

WOW!...Thanks for the concept and specific code!

I see what you code is doing.

I have an example of my code below but I have some syntax
problem...something about single quotes for text but I can't seem to get
it
right. Any suggestions??

Dim sWhere as String

sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR
(>="1601800"
And <="1602316" And <>"1601920")"

DoCmd.OpenReport "rptOrders", , , sWhere

Thanks so much and Cheers!

Joel

Graham Mandeno said:
Hi Joel

Design your report to be based on a query without parameters, listing
all
possible records. Then, in your form, build a WHERE clause that is
based on
the options that have been selected and pass that to the report through
the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:

Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) &
cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thank you Thank you Thank you for your help...

Concept: I have a report and underlying query object. I would like
to be
able to click a button on a form that would set the criteria for a
field
in
the query and then run the report. Of course several buttons to set
different criteria...

One approach was to set a text box on the form that would be populated
by
code and that used as the criteria in a parameter query. I found out
that
I
couldn't pass >= in the parameter which is part of the criteria.

Is there a way through code to set the criteria for a field in an
existing
query object??

Otherwise is there another way to solve the problem??

Thanks again,

Joel
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

Access Dcount (multiple criteria) 3
change query criteria dynamically with value from a 2nd form 1
Using Option Buttons to Modify a Query 1
Populating the Criteria section of a query.? 2
Should I re-create criteria using form filter of just use the relevant query as recordsource? 2
query variables 2
Set value for function through form code 3
Pass criteria to a query design 1

Top