Select between dates in parameter form

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi,

I am using a parameters form to select the criteria for a query. The query
currently looks something like:

Select *
from tblInvoices
Where tblInvoice.InvPeriod between forms.frmsParams.txtCriteria1 and
forms.frmsParams.txtCriteria2;

This works fine but what I would liek is an "If .... Then" statement that if
txtCriteria2 is Null then ([tblInvoices].[InvPeriod] = (Select
Max(InvPeriod) from tblInvoices))

I've tried this with no joy:

WHERE (IF (IsNull(forms.frmsParams.txtCriteria2)=False Then
(((tblInvoices.InvPeriod) Between tblInvoice.InvPeriod between
forms.frmsParams.txtCriteria1 and forms.frmsParams.txtCriteria2
Else ([tblInvoices].[InvPeriod] = (Select Max(InvPeriod) from tblInvoices))

Any suggestions?

Thanks

Paul
 
This starts to get messy with parameters.

Where is this query going?

If to a report, it may be easier to omit the WHERE clause from the query,
and use the WhereCondition of the OpenReport action instead. If to a form,
it may be easier to generate the entire query statement dynamically and
assign it to the RecordSource of the form. (Alternatively, you could apply a
Filter to the form.)

In any case the WHERE clause gets built like this:

Const strcJetDate = "\#mm\/dd\/yyyy\#" 'String format for JET SQL.

If IsNull(Me.txtCriteria1) Then
If Not IsNull(Me.txtCriteria2) Then 'End, no Start
strWhere = "tblInvoices.InvPeriod <= " & _
Format(Me.txtCriteria2, strcJetDate)
End If
Else
If IsNull(Me.txtCriteria2) Then 'Start, no End.
strWhere = "tblInvoices.InvPeriod >= " & _
Format(Me.txtCriteria1, strcJetDate)
Else 'Both Start and End
strWhere = "tblInvoices.InvPeriod Between " & _
Format(Me.txtCriteria1, strcJetDate) & " And " & _
Format(Me.txtCriteria2, strcJetDate)
End If
End If
 
Thanks Allen. That worked a treat.


Allen Browne said:
This starts to get messy with parameters.

Where is this query going?

If to a report, it may be easier to omit the WHERE clause from the query,
and use the WhereCondition of the OpenReport action instead. If to a form,
it may be easier to generate the entire query statement dynamically and
assign it to the RecordSource of the form. (Alternatively, you could apply a
Filter to the form.)

In any case the WHERE clause gets built like this:

Const strcJetDate = "\#mm\/dd\/yyyy\#" 'String format for JET SQL.

If IsNull(Me.txtCriteria1) Then
If Not IsNull(Me.txtCriteria2) Then 'End, no Start
strWhere = "tblInvoices.InvPeriod <= " & _
Format(Me.txtCriteria2, strcJetDate)
End If
Else
If IsNull(Me.txtCriteria2) Then 'Start, no End.
strWhere = "tblInvoices.InvPeriod >= " & _
Format(Me.txtCriteria1, strcJetDate)
Else 'Both Start and End
strWhere = "tblInvoices.InvPeriod Between " & _
Format(Me.txtCriteria1, strcJetDate) & " And " & _
Format(Me.txtCriteria2, strcJetDate)
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PC said:
Hi,

I am using a parameters form to select the criteria for a query. The query
currently looks something like:

Select *
from tblInvoices
Where tblInvoice.InvPeriod between forms.frmsParams.txtCriteria1 and
forms.frmsParams.txtCriteria2;

This works fine but what I would liek is an "If .... Then" statement
Click to expand...
that
if
txtCriteria2 is Null then ([tblInvoices].[InvPeriod] = (Select
Max(InvPeriod) from tblInvoices))

I've tried this with no joy:

WHERE (IF (IsNull(forms.frmsParams.txtCriteria2)=False Then
(((tblInvoices.InvPeriod) Between tblInvoice.InvPeriod between
forms.frmsParams.txtCriteria1 and forms.frmsParams.txtCriteria2
Else ([tblInvoices].[InvPeriod] = (Select Max(InvPeriod) from tblInvoices))

Any suggestions?

Thanks

Paul
Click to expand...
Click to expand...
 
Hi Allen,

Could I ask you to maybo look at this code and let me know whats wrong. It's
much the same as the code you supplied below but the specified field names
are different. The query report displays all cheques between ChqNo1 and
ChqNo2.

Part 1 and 2 don't work i.e. if txtChqNo1 is empty it doesn't display all
above the entry in txtChqNo2 and vice versa.
Part 3 works fine i.e. if the 2 parameter fields have input the results
display as expected.

Thanks in advance for any suggestions

If IsNull(Me.txtChqNo1) Then
If Not IsNull(Me.txtChqNo2) Then 'End, no Start
strWhere = "tblInvoices.ChqNo <= " & _
(Me.txtChqNo2)
End If
Else
If IsNull(Me.txtChqNo2) Then ' Start, No End
strWhere = "tblInvoices.ChqNo >= " & _
(Me.txtChqNo1)
Else
'Both present
strWhere = "tblInvoices.ChqNo Between " & _
(Me.txtChqNo1) & " And " & _
(Me.txtChqNo2)
End If
End If


Allen Browne said:
This starts to get messy with parameters.

Where is this query going?

If to a report, it may be easier to omit the WHERE clause from the query,
and use the WhereCondition of the OpenReport action instead. If to a form,
it may be easier to generate the entire query statement dynamically and
assign it to the RecordSource of the form. (Alternatively, you could apply a
Filter to the form.)

In any case the WHERE clause gets built like this:

Const strcJetDate = "\#mm\/dd\/yyyy\#" 'String format for JET SQL.

If IsNull(Me.txtCriteria1) Then
If Not IsNull(Me.txtCriteria2) Then 'End, no Start
strWhere = "tblInvoices.InvPeriod <= " & _
Format(Me.txtCriteria2, strcJetDate)
End If
Else
If IsNull(Me.txtCriteria2) Then 'Start, no End.
strWhere = "tblInvoices.InvPeriod >= " & _
Format(Me.txtCriteria1, strcJetDate)
Else 'Both Start and End
strWhere = "tblInvoices.InvPeriod Between " & _
Format(Me.txtCriteria1, strcJetDate) & " And " & _
Format(Me.txtCriteria2, strcJetDate)
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PC said:
Hi,

I am using a parameters form to select the criteria for a query. The query
currently looks something like:

Select *
from tblInvoices
Where tblInvoice.InvPeriod between forms.frmsParams.txtCriteria1 and
forms.frmsParams.txtCriteria2;

This works fine but what I would liek is an "If .... Then" statement
Click to expand...
that
if
txtCriteria2 is Null then ([tblInvoices].[InvPeriod] = (Select
Max(InvPeriod) from tblInvoices))

I've tried this with no joy:

WHERE (IF (IsNull(forms.frmsParams.txtCriteria2)=False Then
(((tblInvoices.InvPeriod) Between tblInvoice.InvPeriod between
forms.frmsParams.txtCriteria1 and forms.frmsParams.txtCriteria2
Else ([tblInvoices].[InvPeriod] = (Select Max(InvPeriod) from tblInvoices))

Any suggestions?

Thanks

Paul
Click to expand...
Click to expand...
 
What is the data type of the ChqNo field?
If it's Text, then 11, 199, 1234567 are all less than 2, because they start
with 1.

Further, you need additional quote marks around the values.

If you are still having difficulties, add the line:
Debug.Print strWhere
after the code. Open the Immediate Window (Ctrl+G), and see if the result
makes sense.
 
Allen,

Sorry for pestering you about this but I'm hoping this is a simple syntax
problem.

The Data type is Integer.

When I debug the result appears in the immediate window as expected when
only one text box is used i.e.
tblInvoices.ChqNo >= 2345 or
tblInvoices.ChqNo <=2350 for example. But no records are returned.

When both text boxes are used the result is
tblInvoices.ChqNo Between 2345 And 2350. 6 records are returned to the
report as expected.

Any ideas.
 
The results of the Debug.Print look correct if ChqNo is in fact a field of
type Number.

Try creating a query with exactly the same criteria, and see what happens.
 
Thanks again Allen.

Allen Browne said:
The results of the Debug.Print look correct if ChqNo is in fact a field of
type Number.

Try creating a query with exactly the same criteria, and see what happens.
Click to expand...
 
Back
Top