WHEEEEEEEEEEEEEEEEEEERE

[Guest]

I have a form that has in its header the following unbound
fields:"From","To","Language","Status", the row source of this form is a
query that opens through the citeria Between [Forms]![myformname]![From]
And [Forms]![myformname]![From]
i want to type dates in the fields "from" and "to" to display in the details
section on the same form.same time,to enable the user to select a certain
"language" to display and to select the "status". What's the WHERE condition
?,putting in consideration the user may not write any thing in the field
"language" to display all the languages, and my not write any thing in the
field "status" to display all the languages.

Regards and thanks for your usual help

 

[Steve Schapel]

Pietro,

I think you mean the form's Record Source rather than row source. And I
think you mean [Forms]![myformname]![To] rather than
[Forms]![myformname]![From]. And I think you mean Statuses rather than
languages. Apart from that, I think I understand what you are trying to
do

If you have unbound controls on the form named Language and Status, and
also fields in the form's underlying query also named Language and
Status, I don't think it will work out too well. I will assume the
unbound textboxes are named LanguageSel and StatusSel instead.

Easiest way is to put this in the criteria of the Language column in
your query...
[Forms]![myformname]![LanguageSel] Or
[Forms]![myformname]![LanguageSel] Is Null
.... and put this in the criteria of the Status column in your query...
[Forms]![myformname]![StatusSel] Or [Forms]![myformname]![StatusSel]
Is Null

Then, you probably need a command button to click, or some other event
to use, in order to run code like this...
Me.Requery
.... so the records displayed on the form are updated to reflect the
criteria entered in the unbound controls.

Another approach would be to use VBA to build the SQL of the query,
including the WHERE clause as applicable to the criteria entered, and
then your code will reset the Record Source of the form. Code something
like this...

Dim strSQL As String
If IsNull(Me.From + Me.To) Then
MsgBox "Dates required"
Else
strSQL = "SELECT * FROM YourTable" & _
" WHERE YourDateField Between " & CLng(Me.From) & " And
" & CLng(Me.To)
If Not IsNull(Me.LanguageSel) Then
strSQL = strSQL & " AND Language = '" & Me.LanguageSel & "'"
End If
If Not IsNull(Me.StatusSel) Then
strSQL = strSQL & " AND Status = '" & Me.StatusSel & "'"
End If
Me.RecordSource = strSQL
End If

There is a third way using the Filter property of the form, but... let's
stick with one of the above for now.

 

[Guest]

Hi Steve,

You'r great,you corrected the question and answered it !
Thank you very very much!

Pietro,

I think you mean the form's Record Source rather than row source. And I
think you mean [Forms]![myformname]![To] rather than
[Forms]![myformname]![From]. And I think you mean Statuses rather than
languages. Apart from that, I think I understand what you are trying to
do

If you have unbound controls on the form named Language and Status, and
also fields in the form's underlying query also named Language and
Status, I don't think it will work out too well. I will assume the
unbound textboxes are named LanguageSel and StatusSel instead.

Easiest way is to put this in the criteria of the Language column in
your query...
[Forms]![myformname]![LanguageSel] Or
[Forms]![myformname]![LanguageSel] Is Null
.... and put this in the criteria of the Status column in your query...
[Forms]![myformname]![StatusSel] Or [Forms]![myformname]![StatusSel]
Is Null

Then, you probably need a command button to click, or some other event
to use, in order to run code like this...
Me.Requery
.... so the records displayed on the form are updated to reflect the
criteria entered in the unbound controls.

Another approach would be to use VBA to build the SQL of the query,
including the WHERE clause as applicable to the criteria entered, and
then your code will reset the Record Source of the form. Code something
like this...

Dim strSQL As String
If IsNull(Me.From + Me.To) Then
MsgBox "Dates required"
Else
strSQL = "SELECT * FROM YourTable" & _
" WHERE YourDateField Between " & CLng(Me.From) & " And
" & CLng(Me.To)
If Not IsNull(Me.LanguageSel) Then
strSQL = strSQL & " AND Language = '" & Me.LanguageSel & "'"
End If
If Not IsNull(Me.StatusSel) Then
strSQL = strSQL & " AND Status = '" & Me.StatusSel & "'"
End If
Me.RecordSource = strSQL
End If

There is a third way using the Filter property of the form, but... let's
stick with one of the above for now.

--
Steve Schapel, Microsoft Access MVP

I have a form that has in its header the following unbound
fields:"From","To","Language","Status", the row source of this form is a
query that opens through the citeria Between [Forms]![myformname]![From]
And [Forms]![myformname]![From]
i want to type dates in the fields "from" and "to" to display in the details
section on the same form.same time,to enable the user to select a certain
"language" to display and to select the "status". What's the WHERE condition
?,putting in consideration the user may not write any thing in the field
"language" to display all the languages, and my not write any thing in the
field "status" to display all the languages.

Regards and thanks for your usual help