Set Query Criteria from Form

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top