Check boxes on a form, used in a query to give an output?

[Egglar]

Hi,
Ok, i know there has to be a way of doing it, but i really cant work it out, i been trying all night.

Basically i have 12 check boxes on a form, January to December.

I want to create a query, which a report will use to print out entries from a table, depending on what check boxes are selected, eg

if january check box is selected only, then the report will only show records dates from the 01/01/02 to 31/01/02.

if january and december is selected, then the report will only show records dates from the 01/01/02 to 31/01/02, and 01/12/02 to 31/12/02

My problem is the code used in the query to reconise if a check box is ticked and add the dates accordingly.

Do i use the expression builder or VB code (id rather not use sql as i dont know it! but i am a VB beginner)

any advice you can give and code examples you can is greatly apriceated. many thanks in advance.

 

[allanon]

Ok. I spent a little time and created a couple of functions for you.

step 1)
Add the following to a utility module or create a new one if you have to:

Dim SelectMonths(12) As Boolean  'global table at top of module

'function to set values in table
Public Function SetArray(iIndex As Integer, TheState As Boolean)
    SelectMonths(iIndex) = TheState
End Function

'function to use those values set above
Public Function CreateWhereString() As String
    Dim Year As Integer
    Dim WorkDate As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim iIndex As Integer
    
    On Error GoTo CreateWhereStringError
    
    CreateWhereString = ""
    For iIndex = 0 To 11
        If SelectMonths(iIndex) Then
            WorkDate = "01/" & Format(iIndex + 1, "00") & "/" & Format(Date, "yyyy")
            StartDate = CDate(WorkDate)
            EndDate = DateAdd("m", 1, StartDate)
            EndDate = DateAdd("d", -1, EndDate)
            If Len(CreateWhereString) = 0 Then
                CreateWhereString = "WHERE"
            Else
                CreateWhereString = CreateWhereString & " AND "
            End If
            CreateWhereString = CreateWhereString & " Table1.FieldDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
        End If
    Next iIndex
    Exit Function
CreateWhereStringError:
    MsgBox Err.Description
End Function

step 2)
create 12 checkboxes and place this into there click event expression builder area:
=SetArray(0,[chkJan].[Value])
Above line for jan checkbox
=SetArray(1,[chkFeb].[Value])
Above line for feb checkbox. note the different first and second values. see the pattern? continue for all checkboxes jan - dec

step 3)
in the click event for a button you will need to set up some code similar to this:

Private Sub MyButton_Click()
    Dim strSQL as string

    strSQL = "SELECT * FROM Table1 "
    strSQL = strSQL & CreateWhereString
'do whatever you want with this sql string
End Sub

step 4)
replace the "*" in step 3 with your field list
replace "Table1" in step 1 and step 3 with your table name
replace "FieldDate" in step 1 with your datefield name.



Have Fun

 

[allanon]

One other piece of advice.

The function that I posted for you above will work good except for one failing. I made it for the currenct year only. I think this would be a little limiting. You could easily change it so that you pass the year that you wish report on. This, I think, would be much more flexible and usefull for you.

 

[Egglar]

THanks so much, that must have taken a while!

I was going to worry about next year a bit later once i got this working!

Im slowly understanding your code etc, but, rather silly problem here, how do i get the finishing sql string into the query, so i can generate the report from the query?

 

[allanon]

Set Up a global string variable in the same module as the function above:

Global ReportSQL as string

After you have created the strSQL type

ReportSQL = strSQL

In the open event of the report type

me.recordsource = ReportSQL

Also, while you are changing things change this line:

Public Function CreateWhereString() As String

to be:

Public Function CreateWhereString(Year As Integer) As String


Then remove the line that says

Dim Year as Integer


Now this call:

CreateWhereString

should read:

CreateWhereString(2002)

for year 2002. or if current year then

CreateWhereString(cint(format(Date(),"yyyy&quot))

This will allow you to run this report for any year. Trust me, someone is going to want that eventually.

Let me know how it goes.

 

[allanon]

One more thing. In the function change the line:


WorkDate = "01/" & Format(iIndex + 1, "00&quot & "/" & Format(Date, "yyyy&quot

To be:

WorkDate = "01/" & Format(iIndex + 1, "00&quot & "/" & format(year,"0000&quot

One other thing. I put almost no error checking into this. That's up to you.

 

[Egglar]

I changed the line CreateWhereString to CreateWhereString(2002)

Now every time i press the cmd button on the form i get
------------------------
Complie Error

Function call on the left hand side of assignment must return varient or object.
------------------------

And CreateWhereString(2002) is highlighted.

any ideas? im out of my depth here

 

[allanon2]

Did you change the function deffinition to be:

Public Function CreateWhereString(Year As Integer) As String

The compile error is telling you that there is an error in your module. Most likely a mis-spelled field name or you forgot to delete the line 'Dim Year as Integer' from the function. If you check these and they are ok then compile the app. Let me know what the error message is. It will be easy to fix.

 

[Egglar]

I checked it out and it all seems to be ok. here it is

------------------------------------------------------
Public Function CreateWhereString(Year As Integer) As String
Dim WorkDate As String
Dim StartDate As Date
Dim EndDate As Date
Dim iIndex As Integer

On Error GoTo CreateWhereStringError

CreateWhereString(2002) = ""
For iIndex = 0 To 11
If SelectMonths(iIndex) Then
WorkDate = "01/" & Format(iIndex + 1, "00&quot & "/" & Format(Year, "0000&quot
StartDate = CDate(WorkDate)
EndDate = DateAdd("m", 1, StartDate)
EndDate = DateAdd("d", -1, EndDate)
If Len(CreateWhereString(2002)) = 0 Then
CreateWhereString(2002) = "WHERE"
Else
CreateWhereString(2002) = CreateWhereString & " AND "
End If
CreateWhereString(2002) = CreateWhereString & " tblSalesLedger.PaidDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
End If
Next iIndex
Exit Function
CreateWhereStringError:
MsgBox Err.Description
End Function
----------------------------------------------------------

im still getting the same error tho

 

[allanon]

Inside the function you refer to CreateWhereString by itself.

Therefor this line: CreateWhereString(2002) = ""

Should Be: CreateWhereString = ""

The only place that you would use this line "CreateWhereString(2002) = """ is when calling the CreateWhereString from a different procedure.

So you Function should look like this:

------------------------------------------------------
Public Function CreateWhereString(Year As Integer) As String
    Dim WorkDate As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim iIndex As Integer
    
    On Error GoTo CreateWhereStringError
    
    CreateWhereString = ""
    For iIndex = 0 To 11
        If SelectMonths(iIndex) Then
            WorkDate = "01/" & Format(iIndex + 1, "00") & "/" & Format(Year, "0000")
            StartDate = CDate(WorkDate)
            EndDate = DateAdd("m", 1, StartDate)
            EndDate = DateAdd("d", -1, EndDate)
            If Len(CreateWhereString) = 0 Then
                CreateWhereString = "WHERE"
            Else
                CreateWhereString = CreateWhereString & " AND "
            End If
                CreateWhereString = CreateWhereString & " tblSalesLedger.PaidDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
        End If
    Next iIndex
    Exit Function
CreateWhereStringError:
    MsgBox Err.Description
End Function
----------------------------------------------------------

 

[Egglar]

very sorry for the delayed response, ive been checking my mail waiting for a response, how ever it seems the email notifcation isnt working.

any way, it all works now, apart from a slight problem! which i cant work out.

if i select january, and open the sales ledger, it shows all of januarys, however, if i select febuary, it shows all of january and febuary, when only febuary is selected.

if i select march, it shows some of febuary, and march and april. its very strange

any ideas?

 

[allanon]

Sounds like you are using the wrong date to search on.

Place a debug.print after you make the call to the function. Print out the sql that is created. Hilite the output in the immediate window. This is the sql call being made. Create a new query, paste this call into the query SQL window. Run it a see what you get. If it is not working post the sql call here and I will looka at it.

 

[Egglar]

ok i placed debug.print here:
Private Sub cmdPrintSpecific_Click()
Dim strSQL As String
strSQL = "SELECT tblSalesLedger.InvoiceNumber, tblSalesLedger.InvoiceDate, tblSalesLedger.CustomerName, tblSalesLedger.InvoiceAmount, tblSalesLedger.PaidDate FROM tblSalesLedger "
strSQL = strSQL & CreateWhereString(2002)
Debug.Print
ReportSQL = strSQL
End Sub

i take it this is right? as i have never used debug.print before.

I then opend up the immediate window in visual basic, and pressed the button on the form, nothing appeard in the immediate window, did i do this correctly??

 

[allanon]

Yes except it has to be:

debug.print strSQL

 

[Egglar]

silly me

This is for january
SELECT tblSalesLedger.InvoiceNumber, tblSalesLedger.InvoiceDate, tblSalesLedger.CustomerName, tblSalesLedger.InvoiceAmount, tblSalesLedger.PaidDate FROM tblSalesLedger WHERE tblSalesLedger.PaidDate BETWEEN #01/01/2002# AND #31/01/2002#
------------------------------
this is for january and febuary selected
WHERE tblSalesLedger.PaidDate BETWEEN #01/01/2002# AND #31/01/2002# AND tblSalesLedger.PaidDate BETWEEN #01/02/2002# AND #28/02/2002#
-----------------------------
this is for january and dec
WHERE tblSalesLedger.PaidDate BETWEEN #01/01/2002# AND #31/01/2002#
-----------------------------
This is all months
WHERE tblSalesLedger.PaidDate BETWEEN #01/01/2002# AND #31/01/2002# AND tblSalesLedger.PaidDate BETWEEN #01/02/2002# AND #28/02/2002# AND tblSalesLedger.PaidDate BETWEEN #01/04/2002# AND #30/04/2002# AND tblSalesLedger.PaidDate BETWEEN #01/05/2002# AND #31/05/2002# AND tblSalesLedger.PaidDate BETWEEN #01/06/2002# AND #30/06/2002# AND tblSalesLedger.PaidDate BETWEEN #01/07/2002# AND #31/07/2002# AND tblSalesLedger.PaidDate BETWEEN #01/08/2002# AND #31/08/2002# AND tblSalesLedger.PaidDate BETWEEN #01/09/2002# AND #30/09/2002# AND tblSalesLedger.PaidDate BETWEEN #01/10/2002# AND #31/10/2002# AND tblSalesLedger.PaidDate BETWEEN #01/11/2002# AND #30/11/2002# AND tblSalesLedger.PaidDate BETWEEN #01/12/2002# AND #31/12/2002#

I didnt paste any sql before the WHERE statment apart from on the first one as its all the same

 

[allanon]

Can you list your output data for the Jan query. Run the query, hilite all the rows and press cntl-c and then post to here with cntl-v

 

[allanon]

Sorry. Do it for a feb run. That is the one you are having problems with.

 

[Egglar]

ok i made a mess up, i labled march as setarray3, when its actually 2, therefore all the other months were one higher than they should be. sorry to waste ur time
im just gunna fully test it now

 

[allanon]

No Problem. Let me know how it goes.

 

[Egglar]

the sql seems to be correct now, by that i mean its doing all the months. but the report isnt showing the correct dates, i will post the query outputs here:

Jan only
InvoiceNumber InvoiceDate CustomerName InvoiceAmount PaidDate
9000 10/05/2002 MRS £0.00 02/01/2002
9001 10/05/2002 MAN £0.00 02/01/2002
9002 02/05/111 MRS £0.00 03/01/2002

Feb only
InvoiceNumber InvoiceDate CustomerName InvoiceAmount PaidDate
9000 10/05/2002 MRS £0.00 02/01/2002
9001 10/05/2002 MAN £0.00 02/01/2002
9002 02/05/111 MRS £0.00 03/01/2002

Dec only
Shows random dates in random order