N
Nelson
i have a table contain 15 fields, how can write a sql to query those field
name?
Thx a lot
nelson
name?
Thx a lot
nelson
J
Jeff Boyce
Nelson
If you have Access, you can use the query design mode to create a query,
selecting the fields from the table. If you want to see the SQL, click on
the Toolbar, design button, and select SQL View.
Good luck
Jeff Boyce
<Access MVP>
If you have Access, you can use the query design mode to create a query,
selecting the fields from the table. If you want to see the SQL, click on
the Toolbar, design button, and select SQL View.
Good luck
Jeff Boyce
<Access MVP>
D
Duane Hookom
If you are looking for a query to return the structure of a table, you won't
find it. The documenter can create a report of database objects and
properties.
find it. The documenter can create a report of database objects and
properties.
N
Nelson
thx for your suggestion, but i would like to make the following form base on
the query:
a dynamic check box display the field of the table, when user check the
field, it will export the data of those selected fields.
Like this:
Data export:
O First name
O Last name
O Joindate
cmdExport button
regards,
Nelson
the query:
a dynamic check box display the field of the table, when user check the
field, it will export the data of those selected fields.
Like this:
Data export:
O First name
O Last name
O Joindate
cmdExport button
regards,
Nelson
A
Alick [MSFT]
Hi Nelson,
If I haven't misunderstood the issue, you have to resort to VBA code to
retrieve what you are after: export data from table/query to a file based
on the fields name you selected.
I created a sample for your reference.
1. Open NorthWind.mdb
2. Create a new form, add three checkboxes to the form, add one command
button.
3. since it is a sample, I create 3 checkboxes based on three fields on the
table, you can add more checkboxes based on your table. The checkboxes'
name and its corresponding table filed/column
Checkcity: City filed/column in table employees
Checkfirstname: firtname filed/column in table employees
Checklastname: LastName filed/column in table employees
4. Copy and paste the code below to button click event.
Note: remember to add reference to ADOX library and ADO library.
Option Compare Database
Option Explicit
Private Sub Command4_Click()
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim mview As ADOX.View
Dim myvalue As String
Dim myquery As String
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
'Checkfirstname, Checklastname
If Checklastname Then 'checklastname is selected
myvalue = " LastName,"
End If
If Checkfirstname Then
myvalue = myvalue & " firstname,"
End If
If checkcity Then
myvalue = myvalue & " city,"
End If
If myvalue = "" Then
MsgBox "No field is selected!"
Exit Sub
End If
myvalue = Left(myvalue, Len(myvalue) - 1)
myquery = "select" & myvalue & " from Employees"
'basequery is the query that generates data for report.
Set mview = cat.Views("basequery")
Set cmd = mview.Command
cmd.CommandText = myquery
Set mview.Command = cmd
DoCmd.OutputTo acOutputQuery, "basequery", acFormatTXT, "c:\employees.txt"
Set mview = Nothing
Set cmd = Nothing
Set cat = Nothing
End Sub
Private Sub Form_Load()
'set all checkboxes default value to false(unchecked status)
checkcity = False
Checkfirstname = False
Checklastname = False
End Sub
Please feel free to reply to the threads if you have any concerns or
questions.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Nelson" <g>
| References: <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| thx for your suggestion, but i would like to make the following form base
on
| the query:
|
| a dynamic check box display the field of the table, when user check the
| field, it will export the data of those selected fields.
| Like this:
|
| Data export:
|
| O First name
| O Last name
| O Joindate
|
| cmdExport button
|
| regards,
| Nelson
|
| "Nelson" <g> ¦b¶l¥ó ¤¤¼¶¼g...
| > i have a table contain 15 fields, how can write a sql to query those
| field
| > name?
| >
| >
| > Thx a lot
| >
| > nelson
| >
| >
|
|
|
If I haven't misunderstood the issue, you have to resort to VBA code to
retrieve what you are after: export data from table/query to a file based
on the fields name you selected.
I created a sample for your reference.
1. Open NorthWind.mdb
2. Create a new form, add three checkboxes to the form, add one command
button.
3. since it is a sample, I create 3 checkboxes based on three fields on the
table, you can add more checkboxes based on your table. The checkboxes'
name and its corresponding table filed/column
Checkcity: City filed/column in table employees
Checkfirstname: firtname filed/column in table employees
Checklastname: LastName filed/column in table employees
4. Copy and paste the code below to button click event.
Note: remember to add reference to ADOX library and ADO library.
Option Compare Database
Option Explicit
Private Sub Command4_Click()
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim mview As ADOX.View
Dim myvalue As String
Dim myquery As String
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
'Checkfirstname, Checklastname
If Checklastname Then 'checklastname is selected
myvalue = " LastName,"
End If
If Checkfirstname Then
myvalue = myvalue & " firstname,"
End If
If checkcity Then
myvalue = myvalue & " city,"
End If
If myvalue = "" Then
MsgBox "No field is selected!"
Exit Sub
End If
myvalue = Left(myvalue, Len(myvalue) - 1)
myquery = "select" & myvalue & " from Employees"
'basequery is the query that generates data for report.
Set mview = cat.Views("basequery")
Set cmd = mview.Command
cmd.CommandText = myquery
Set mview.Command = cmd
DoCmd.OutputTo acOutputQuery, "basequery", acFormatTXT, "c:\employees.txt"
Set mview = Nothing
Set cmd = Nothing
Set cat = Nothing
End Sub
Private Sub Form_Load()
'set all checkboxes default value to false(unchecked status)
checkcity = False
Checkfirstname = False
Checklastname = False
End Sub
Please feel free to reply to the threads if you have any concerns or
questions.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Nelson" <g>
| References: <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| thx for your suggestion, but i would like to make the following form base
on
| the query:
|
| a dynamic check box display the field of the table, when user check the
| field, it will export the data of those selected fields.
| Like this:
|
| Data export:
|
| O First name
| O Last name
| O Joindate
|
| cmdExport button
|
| regards,
| Nelson
|
| "Nelson" <g> ¦b¶l¥ó ¤¤¼¶¼g...
| > i have a table contain 15 fields, how can write a sql to query those
| field
| > name?
| >
| >
| > Thx a lot
| >
| > nelson
| >
| >
|
|
|
N
News
Thx for your great help!
However, i have around 40 fields, so i would like the form create the check
box itself, base on query or VBA coding.
by the way, if i can build the VBA in a class, so i can do the same way to
another table. becox i need to export another table.
Nelson
However, i have around 40 fields, so i would like the form create the check
box itself, base on query or VBA coding.
by the way, if i can build the VBA in a class, so i can do the same way to
another table. becox i need to export another table.
Nelson
A
Alick [MSFT]
Hi Nelson,
Based on your previous post, it seems you would like to control the query
according to user's selection (select the checkbox), however, based on your
current post, it seems you would like to control the checkbox based on the
query? They are two contrary aspects:
1. When the user selects the checkboxes on the Interface (form), the query
is created dynamically and exports the results. It is what the sample does
in my previous post.
2. You have already created the query, which will export the results you
want. You want to select the checkboxes (fields) based on your query? It is
my understanding of your current post. Is it right?
Would you please clarify the situation?
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "News" <[email protected]>
| References: <[email protected]> <#wc84uHkDHA.2424
| Thx for your great help!
| However, i have around 40 fields, so i would like the form create the
check
| box itself, base on query or VBA coding.
| by the way, if i can build the VBA in a class, so i can do the same way to
| another table. becox i need to export another table.
|
|
| Nelson
|
|
|
Based on your previous post, it seems you would like to control the query
according to user's selection (select the checkbox), however, based on your
current post, it seems you would like to control the checkbox based on the
query? They are two contrary aspects:
1. When the user selects the checkboxes on the Interface (form), the query
is created dynamically and exports the results. It is what the sample does
in my previous post.
2. You have already created the query, which will export the results you
want. You want to select the checkboxes (fields) based on your query? It is
my understanding of your current post. Is it right?
Would you please clarify the situation?
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "News" <[email protected]>
| References: <[email protected]> <#wc84uHkDHA.2424
| Thx for your great help!
| However, i have around 40 fields, so i would like the form create the
check
| box itself, base on query or VBA coding.
| by the way, if i can build the VBA in a class, so i can do the same way to
| another table. becox i need to export another table.
|
|
| Nelson
|
|
|
N
News
sorry, i clarify the situation what i would to do.
1. A form has a numbers of check box , which represent the table field.
2. When user click on the box, it will export corr. data base on the
selected field to a file, or prt reports.
3. THE main point is i want those check box is created dynamically during
the form load. Becox table field may have changed, i don't want to ddd check
box again in that form.
4. So , i want to know is it possible to create a query to return the table
field and set the check box bound to the query?
Thx for your greate help again.
Nelson
1. A form has a numbers of check box , which represent the table field.
2. When user click on the box, it will export corr. data base on the
selected field to a file, or prt reports.
3. THE main point is i want those check box is created dynamically during
the form load. Becox table field may have changed, i don't want to ddd check
box again in that form.
4. So , i want to know is it possible to create a query to return the table
field and set the check box bound to the query?
Thx for your greate help again.
Nelson
J
Jeff Boyce
Nelson
You have multiple Yes/No fields in your table? Or you want the form to list
all fields in a table, and allow selection of certain fields?
Are you trying to select only certain fields for export/print/...? And
allow the user to change which fields?
Are you absolutely certain you want checkboxes? There are other ways to
"list" available data, including fields, and to select which ones you want
to process further.
Good luck
Jeff Boyce
<Access MVP>
News said:
You have multiple Yes/No fields in your table? Or you want the form to list
all fields in a table, and allow selection of certain fields?
Are you trying to select only certain fields for export/print/...? And
allow the user to change which fields?
Are you absolutely certain you want checkboxes? There are other ways to
"list" available data, including fields, and to select which ones you want
to process further.
Good luck
Jeff Boyce
<Access MVP>
A
Alick [MSFT]
Hi Nelson,
Besides Jeff's questions, would you please also post the table's structure?
Is the form bounded to the table?
If possible, would illustrate the issue based on the sample I posted? Once
we get what you are exactly looking for, we can provide the extract
informant you want if the resolution is reachable.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "News" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| sorry, i clarify the situation what i would to do.
| 1. A form has a numbers of check box , which represent the table field.
|
| 2. When user click on the box, it will export corr. data base on the
| selected field to a file, or prt reports.
|
| 3. THE main point is i want those check box is created dynamically during
| the form load. Becox table field may have changed, i don't want to ddd
check
| box again in that form.
|
| 4. So , i want to know is it possible to create a query to return the
table
| field and set the check box bound to the query?
|
| Thx for your greate help again.
| Nelson
|
|
|
|
| "Alick [MSFT]" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D
|
[email protected]...
| > Hi Nelson,
| >
| > Based on your previous post, it seems you would like to control the
query
| > according to user's selection (select the checkbox), however, based on
| your
| > current post, it seems you would like to control the checkbox based on
the
| > query? They are two contrary aspects:
| >
| > 1. When the user selects the checkboxes on the Interface (form), the
query
| > is created dynamically and exports the results. It is what the sample
does
| > in my previous post.
| >
| > 2. You have already created the query, which will export the results you
| > want. You want to select the checkboxes (fields) based on your query? It
| is
| > my understanding of your current post. Is it right?
| >
| > Would you please clarify the situation?
| >
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Product Support Services
| > Microsoft Corporation
| > Get Secure! - <www.microsoft.com/security>
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| > --------------------
| >
| > | From: "News" <[email protected]>
| > | References: <[email protected]> <#wc84uHkDHA.2424
| >
| > | Thx for your great help!
| > | However, i have around 40 fields, so i would like the form create the
| > check
| > | box itself, base on query or VBA coding.
| > | by the way, if i can build the VBA in a class, so i can do the same
way
| to
| > | another table. becox i need to export another table.
| > |
| > |
| > | Nelson
| > |
| > |
| > |
| >
|
|
|
Besides Jeff's questions, would you please also post the table's structure?
Is the form bounded to the table?
If possible, would illustrate the issue based on the sample I posted? Once
we get what you are exactly looking for, we can provide the extract
informant you want if the resolution is reachable.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "News" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| sorry, i clarify the situation what i would to do.
| 1. A form has a numbers of check box , which represent the table field.
|
| 2. When user click on the box, it will export corr. data base on the
| selected field to a file, or prt reports.
|
| 3. THE main point is i want those check box is created dynamically during
| the form load. Becox table field may have changed, i don't want to ddd
check
| box again in that form.
|
| 4. So , i want to know is it possible to create a query to return the
table
| field and set the check box bound to the query?
|
| Thx for your greate help again.
| Nelson
|
|
|
|
| "Alick [MSFT]" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D
|
[email protected]...| > Hi Nelson,
| >
| > Based on your previous post, it seems you would like to control the
query
| > according to user's selection (select the checkbox), however, based on
| your
| > current post, it seems you would like to control the checkbox based on
the
| > query? They are two contrary aspects:
| >
| > 1. When the user selects the checkboxes on the Interface (form), the
query
| > is created dynamically and exports the results. It is what the sample
does
| > in my previous post.
| >
| > 2. You have already created the query, which will export the results you
| > want. You want to select the checkboxes (fields) based on your query? It
| is
| > my understanding of your current post. Is it right?
| >
| > Would you please clarify the situation?
| >
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Product Support Services
| > Microsoft Corporation
| > Get Secure! - <www.microsoft.com/security>
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| > --------------------
| >
| > | From: "News" <[email protected]>
| > | References: <[email protected]> <#wc84uHkDHA.2424
| >
| > | Thx for your great help!
| > | However, i have around 40 fields, so i would like the form create the
| > check
| > | box itself, base on query or VBA coding.
| > | by the way, if i can build the VBA in a class, so i can do the same
way
| to
| > | another table. becox i need to export another table.
| > |
| > |
| > | Nelson
| > |
| > |
| > |
| >
|
|
|