Checkbox problems in a form

[Martijn]

Hello all,

Need help with:

I've got a table (tblInput)with a date field (Date) some
other fields and a query (qryInput) .
I've also got a form (frmInputFilter) which has two
checkbox, four text boxes and a commandbutton
(printpreview).

chkbox 1 txtStartdate1 txtEnddate1
chkbox 2 txtStartdate2 txtEnddate2


Printpreview

What I need is the following:
The entered dates in the txtBoxes (start and end) need to
be used in the query to filter the dates in the table.
This is only accepted when the related chkbox is checked.
When the dates are entered and the chkbox is checked the
commandbutton has to be used to give a command to open the
query related to the entered dates.

So if dates are entered and the chkbox is checked the
commandbutton has to send a command to open the related
query if not checked the entered dates aren't to be send.

How do I do this?

Thanks,

Martijn

 

[Ronald Dodge]

Here's one way, but see below for the few assumptions that I'm using.

Dim X as Long, strSQLWHERE, strCond1, strCond2
X = 0
If chkbox1.value = -1 Then
X = X + 1
strCond1 = "tblACCTREC.fldTRANS => " & txtStartdate1.Value & _
" AND tblACCTREC.fldTRANS =< " & txtEnddate1.Value
End If
If chkbox2.Value = -1 Then
X = X + 2
strCond1 = "tblACCTREC.fldPOST => " & txtStartdate2.Value & _
" AND tblACCTREC.fldPOST =< " & txtEnddate2.Value
End if
Select Case X
Case 0
strSQLWHERE = ""
Case 1
strSQLWHERE = strCond1
Case 2
strSQLWHERE = strCond2
Case 3
strSQLWHERE = " WHERE " & strCond1 & " AND " & strCond2
End Select

ASSUMPTIONS:

You are using unbound forms, as I do not use bound forms due to issues that
I have with bound forms.

You are using SQL code to query on the data. The above is geared towards
Jet Dialect of SQL Statements.

The date text boxes are setup in String format, but if it's setup in
Date/Time format, you will need to use the FORMAT Function to convert the
value from the Date value to the Date string equivalent since SQL statements
requires all parts to be in String format.

Example of FORMAT Function

FORMAT(txtStartdate1.Value,"MM/DD/YYYY")

The above would go in place of the location where you see
"txtStartdate1.Value"

This particular code only sets the string for the Where part of the SQL
Statements.

This particular code is in line with using DAO coding.

Note above how I have used both, the table name and the field name within
the conditions. This example in particular follows after a transaction
history sort of like how credit card billing statements may work, as you
have an actual transaction date, then the date that the transaction was
posted against the account.