Good Afternoon:
After i wrote the code you sent, (Thanks very much for it) i had some
problems.
1 - When i uptaded the CmbSector (1st Combo) all the records from the
listbox disapeared. It is suposed to stay one.
2 - On DateTime Picker i haven't the afterupate property.
3 - the Format of date is a timestamp format (date time) and as you can see
on debug from SQL . It could be one of the problems.
----------------------------------------------
My code:
Function BuildListRowSource()
Dim strSQL As String
Dim strwhere As String
strwhere = "1 = 1 "
If Not IsNull(Me.CmbSector) Then
strwhere = strwhere & " and [CodSector]=""" & _
Me.CmbSector & """ "
End If
If Not IsNull(Me.CmbCentroAnalise) Then
strwhere = strwhere & " and [CodigoCentroAnalise] = """ & _
Me.CmbCentroAnalise & """ "
End If
If Not IsNull(Me.CmbFuncionario) Then
strwhere = strwhere & " and [Funcionario] = " & _
Me.CmbFuncionario
End If
If Not IsNull(DTPDataInicial) Then
strwhere = strwhere & " and [DataDados] >=#" & _
Me.DTPDataInicial & "# "
End If
If Not IsNull(DTPDataFinal) Then
strwhere = strwhere & " and [DataDados] <=#" & _
Me.DTPDataFinal & "# "
End If
strSQL = "DELECT * FROM QryPorSector_Dados WHERE " & strwhere
Debug.Print strSQL
Me.LstInqueritos.RowSource = strSQL
End Function
On design view, i changed the after update property of the 3 comboboxes to
=[BuildListRowSource]
i didn't do it to the DTPDataInicial as well to DTPDataFinal
--------------------------------------
Debug Print
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#
-------------------------------------------------
For the purpose of viewing the fields, i am going to do the following:
if ChkPerformance or ChkActividade..... = "No" then
SELECT * FROM QryPorSector_Dados WHERE ....
endif
if ChkPerformance = "Yes" then
SELECT CodSector, CodCentroAnalise, ... , Performance FROM
QryPorSector_Dados WHERE ....
endif
--------------------------------------------------------------------------------------
i hope that i have replyed to all the questions you made to find a solution
Once again , Many thanks to you
--
Adriano Santos
"Duane Hookom" escreveu:
Try code like:
Function BuildListRowSource()
Dim strSQL as String
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.CmbSector) Then
strWhere = strWhere & " And [CodSector]=""" & _
Me.CmbSector & """ "
End If
If Not IsNull(Me.CmbCentroAnalise) Then
strWhere = strWhere & " And [CodigoCentroAnalise]=""" & _
Me.CmbCentroAnalise & """ "
End If
If Not IsNull(Me.CmbFuncionario ) Then
strWhere = strWhere & " And [Funcionario]=" & _
Me.CmbFuncionario
End If
If not IsNull(Me.DTPDataInicial) Then
strWhere = strWhere & " And [DataDados] >=#" & _
Me.DTPDataInicial & "# "
End If
If not IsNull(Me.DTPDataFinal) Then
strWhere = strWhere & " And [DataDados] <=#" & _
Me.DTPDataFinal & "# "
End If
strSQL = "SELECT * FROM QryDados_PorData WHERE " & strWhere
debug.Print strSQL
Me.LstInqueritos.RowSource = strSQL
End Function
You then need to update the After Update property of each of the significant
controls to
After Update: =BuildListRowSource()
You can select all significant controls and set their properties at once.
If you really think you need to change the columns displayed, you will need
to add code that changes the "SELECT *..." to "SELECT FieldA, FieldB,
FieldC,..." as well as change the List boxes Number of columns and column
widths properties.
If you try this and it doesn't work. Press Ctrl+G to find out the value of
strSQL. Post this back to use along with your complete code.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Thanks for your prompty reply.
I will answer according to your questions:
CodSector text (string)
CodCentroAnalise text (string)
CodColaborador integer
------------------------
On ListBox they have the following names
ListBox
CodSector CodSector
CodCentroAnalise CodigoCentroAnalise
CodColaborador Funcionario
------------------------
Date Controls name in LstInqueritos
DTPDataInicial DataDados
DTPDataFinal DataDados
------------------------
Name of Check Boxes (they are not part of a option Group)
Yes. If none of checkboxes is selected, all the fields are viewed, otherwise
only
a few are viewed according to the checkbox is selected.
They are not used to filter the records, just to view the fields.
Names
ChkPerformance
ChkActividade
ChkOcupacao
ChkIntProd
ChkIntNProd
ChkIntTotal
--------------------------------------------------------------------------------------
i think i answered all your questions.
If by mistake i forgot something, please just let me know
Thanks again for your patient, help and support
------------------------
--
Adriano Santos
"Duane Hookom" escreveu:
You will need code (or a macro) to update the list box after you have changed
your control values.
What are the data types of CodSector, CodCentroAnalise, and CodColaborador?
Are the fields in LstInqueritos the same names ie: CodSector,
CodCentroAnalise, and CodColaborador?
What are the names of the two date controls? What is the name of the date
field in your LstInqueritos list box Row Source?
