2 simple questions (I hope)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.
I have a form with a combo box which row source is a table. I'd like to add
a string to that row source ("ALL") to work as a wildcard on a filter. This
table is built every day by a make table query. How can i add this string?

The second question i have is to make this wildcard work. What i'd like is
that when the user chooses the "ALL" string a certain query should return all
the records of a table. If the user chooses another string the query uses
this value as filter.

Is all this possible ?
 
Hi Luis:

This is the way I do it- I setup a query statement then I reset the form's
recordsource function. Here's an example of actual code:

' note that the select choices uses SQL queries. Don't leave out or put in
any
' extraneous periods, commas, or use any concanations that give you errors!
Select Case Forms![*LEDGER FORM]![EmbLedger].Form![FilterOption].value
Case "All Records"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE (ACCOUNTS.DATESVC)
Date()-800 AND ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT] ORDER BY
Click to expand...
ACCOUNTS.DATESVC;"
Case "1-6 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) > Date()-183) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "7-12 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-366 And (ACCOUNTS.DATESVC) < Date()-182) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "13-18 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-548 And (ACCOUNTS.DATESVC)<Date()-365) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "19-24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-731 And (ACCOUNTS.DATESVC) < Date()-547) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "<24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) < Date()-730) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
End Select
Forms![*LEDGER FORM]![EmbLedger].Form.RecordSource = SQLStmt
Exit Sub

What exactly are you basing you filter on? Numbers, text, dates....

Regards,
Al
(check out my free MS Word EMR Project, downloadable at
http://www.emrupdate.com/freestuff/alborgesmd.aspx)
 
I will have to assume a lot from your question.
Assuming you mean Append Query and Not Make Table Query.
Assuming this is done in code.
Before you run your Append query add this code
DoCmd.RunSQL "INSERT INTO TableName (FieldName) VALUES ('All')"
In the After Update Event for your combo box put this code
If ComboBoxName = "All" Then
' Open a query with no criteria
Else
' Open a query with criteria set to [Forms]![FormName]![ComboBoxName]
End if
 
If memory serves, you'd use a UNION query as in

SELECT "<ALL>" AS [displayField], "*" As [boundField]
UNION
SELECT [fields] FROM myTableName;

Its been awhile so I'd have to go digging around to confirm the syntax,
but that's basically how to do it. You do have to change [displayField]
and [boundField] in the first part so that they match up with the NAMES
pulled from myTableName. That's neccessary to get the <ALL> into the
comboBox and the * into the bound column so that the comboBox works
correctly. I added the <> to make the selection stand out a bit more
other variations would be [Select All Records] and whatnot.

David H
 
Al, you may want to revise the code so that the common portions of the
SQL statement reside in a string with the various WHERE statements in
underneath the CASE keywords as in

strSQL = ""
strSQL = "SELECT * FROM myTable "

Select Case myVariable
Case 1
strSQL = strSQL & "WHERE lngTransportID > 1000"
Case 2
strSQL = strSQL & "WHERE txtType = 'RES'"
Case Else
End Select

strSQL = strSQL & ";"

It'll make the code much easier to read.

Al said:
Hi Luis:

This is the way I do it- I setup a query statement then I reset the form's
recordsource function. Here's an example of actual code:

' note that the select choices uses SQL queries. Don't leave out or put in
any
' extraneous periods, commas, or use any concanations that give you errors!
Select Case Forms![*LEDGER FORM]![EmbLedger].Form![FilterOption].value
Case "All Records"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE (ACCOUNTS.DATESVC)
Date()-800 AND ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT] ORDER BY
Click to expand...
ACCOUNTS.DATESVC;"
Case "1-6 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) > Date()-183) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "7-12 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-366 And (ACCOUNTS.DATESVC) < Date()-182) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "13-18 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-548 And (ACCOUNTS.DATESVC)<Date()-365) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "19-24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-731 And (ACCOUNTS.DATESVC) < Date()-547) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "<24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) < Date()-730) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
End Select
Forms![*LEDGER FORM]![EmbLedger].Form.RecordSource = SQLStmt
Exit Sub

What exactly are you basing you filter on? Numbers, text, dates....

Regards,
Al
(check out my free MS Word EMR Project, downloadable at
http://www.emrupdate.com/freestuff/alborgesmd.aspx)

Hello.
I have a form with a combo box which row source is a table. I'd like to
add
a string to that row source ("ALL") to work as a wildcard on a filter.
This
table is built every day by a make table query. How can i add this string?

The second question i have is to make this wildcard work. What i'd like is
that when the user chooses the "ALL" string a certain query should return
all
the records of a table. If the user chooses another string the query uses
this value as filter.

Is all this possible ?
Click to expand...
Click to expand...
 
David C. Holley said:
If memory serves, you'd use a UNION query as in

SELECT "<ALL>" AS [displayField], "*" As [boundField]
UNION
SELECT [fields] FROM myTableName;

Its been awhile so I'd have to go digging around to confirm the
syntax, but that's basically how to do it. You do have to change
[displayField] and [boundField] in the first part so that they match
up with the NAMES pulled from myTableName. That's neccessary to get
the <ALL> into the comboBox and the * into the bound column so that
the comboBox works correctly. I added the <> to make the selection
stand out a bit more other variations would be [Select All Records]
and whatnot.
Click to expand...

This is the right approach -- or at least *a* right approach; there are
probably others -- but there are one or two wrinkles. Using this
approach, you need to use the Like operator in your query criterion. Or
else you can use Null instead of "*" for [BoundField], and write your
query like this:

SELECT ... FROM ...
WHERE SomeField = Forms!frmFilter!cboChoose
OR Forms!frmFilter!cboChoose Is Null;

Or you can build the query SQL on the fly, and either add a
WHERE-condition for this value or not, depending on whether the combo is
Null (or "*") or not.
 
I agree... thanks.

Al

David C. Holley said:
Al, you may want to revise the code so that the common portions of the
SQL statement reside in a string with the various WHERE statements in
underneath the CASE keywords as in

strSQL = ""
strSQL = "SELECT * FROM myTable "

Select Case myVariable
Case 1
strSQL = strSQL & "WHERE lngTransportID > 1000"
Case 2
strSQL = strSQL & "WHERE txtType = 'RES'"
Case Else
End Select

strSQL = strSQL & ";"

It'll make the code much easier to read.

Al said:
Hi Luis:

This is the way I do it- I setup a query statement then I reset the form's
recordsource function. Here's an example of actual code:

' note that the select choices uses SQL queries. Don't leave out or put in
any
' extraneous periods, commas, or use any concanations that give you errors!
Select Case Forms![*LEDGER FORM]![EmbLedger].Form![FilterOption].value
Case "All Records"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE (ACCOUNTS.DATESVC)
Date()-800 AND ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT] ORDER BY
Click to expand...
ACCOUNTS.DATESVC;"
Case "1-6 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) > Date()-183) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "7-12 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-366 And (ACCOUNTS.DATESVC) < Date()-182) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "13-18 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-548 And (ACCOUNTS.DATESVC)<Date()-365) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "19-24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) >= Date()-731 And (ACCOUNTS.DATESVC) < Date()-547) AND
ACCOUNTS.ACCT = FORMS![*LEDGER FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
Case "<24 Months"
SQLStmt = "SELECT ACCOUNTS.* FROM ACCOUNTS WHERE
(((ACCOUNTS.DATESVC) < Date()-730) AND ACCOUNTS.ACCT = FORMS![*LEDGER
FORM]![ACCT]) ORDER BY ACCOUNTS.DATESVC;"
End Select
Forms![*LEDGER FORM]![EmbLedger].Form.RecordSource = SQLStmt
Exit Sub

What exactly are you basing you filter on? Numbers, text, dates....

Regards,
Al
(check out my free MS Word EMR Project, downloadable at
http://www.emrupdate.com/freestuff/alborgesmd.aspx)

Hello.
I have a form with a combo box which row source is a table. I'd like to
add
a string to that row source ("ALL") to work as a wildcard on a filter.
This
table is built every day by a make table query. How can i add this string?

The second question i have is to make this wildcard work. What i'd like is
that when the user chooses the "ALL" string a certain query should return
all
the records of a table. If the user chooses another string the query uses
this value as filter.

Is all this possible ?
Click to expand...
Click to expand...
Click to expand...
 
I did that trick back in '97 and unfortanetly(sp) don't remember how I
dealt with the where statement. I'm thinking that I just did a SELECT
CASE or If...Then to build it as needed.

Dirk said:
If memory serves, you'd use a UNION query as in

SELECT "<ALL>" AS [displayField], "*" As [boundField]
UNION
SELECT [fields] FROM myTableName;

Its been awhile so I'd have to go digging around to confirm the
syntax, but that's basically how to do it. You do have to change
[displayField] and [boundField] in the first part so that they match
up with the NAMES pulled from myTableName. That's neccessary to get
the <ALL> into the comboBox and the * into the bound column so that
the comboBox works correctly. I added the <> to make the selection
stand out a bit more other variations would be [Select All Records]
and whatnot.
Click to expand...


This is the right approach -- or at least *a* right approach; there are
probably others -- but there are one or two wrinkles. Using this
approach, you need to use the Like operator in your query criterion. Or
else you can use Null instead of "*" for [BoundField], and write your
query like this:

SELECT ... FROM ...
WHERE SomeField = Forms!frmFilter!cboChoose
OR Forms!frmFilter!cboChoose Is Null;

Or you can build the query SQL on the fly, and either add a
WHERE-condition for this value or not, depending on whether the combo is
Null (or "*") or not.
Click to expand...
 
To add to Dirk's suggestion, I have a sample database that shows how you can
build a WHERE string in VBA code based on whether a combo box has a value in
it or not. (There also are examples of other controls too.)
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm
--

Ken Snell
<MS ACCESS MVP>

Dirk Goldgar said:
David C. Holley said:
If memory serves, you'd use a UNION query as in

SELECT "<ALL>" AS [displayField], "*" As [boundField]
UNION
SELECT [fields] FROM myTableName;

Its been awhile so I'd have to go digging around to confirm the
syntax, but that's basically how to do it. You do have to change
[displayField] and [boundField] in the first part so that they match
up with the NAMES pulled from myTableName. That's neccessary to get
the <ALL> into the comboBox and the * into the bound column so that
the comboBox works correctly. I added the <> to make the selection
stand out a bit more other variations would be [Select All Records]
and whatnot.
Click to expand...

This is the right approach -- or at least *a* right approach; there are
probably others -- but there are one or two wrinkles. Using this
approach, you need to use the Like operator in your query criterion. Or
else you can use Null instead of "*" for [BoundField], and write your
query like this:

SELECT ... FROM ...
WHERE SomeField = Forms!frmFilter!cboChoose
OR Forms!frmFilter!cboChoose Is Null;

Or you can build the query SQL on the fly, and either add a
WHERE-condition for this value or not, depending on whether the combo is
Null (or "*") or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top