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]

What type of field is PaidDate? InvoiceDate is not a datetime field. If it were then your third entry would be a ploblem as it is not a valid date.

 

[allanon]

Ok. I tested it out. The reason is that access is expecting the date in month/day/year and the query is in /day/month/year. Try changing the one line in the function that I sent you. Change this line:

CreateWhereString = CreateWhereString & " tblSalesLedger.PaidDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#"

To read:

CreateWhereString = CreateWhereString & " tblSalesLedger.PaidDate BETWEEN #" & Format(StartDate, "mmm/dd/yyyy&quot & "# AND #" & format(EndDate, "mmm/dd/yyyy&quot & "#"

Either that or maybe change the system setting for your computer to match access. If the above change does not work let me know. I have not actually tested it. Should work tho.

 

[Egglar]

it all worked perfectly for any month, as long as you only select one month, the problem comes when i select two or more months now. I get no return. I selected jan and feb as example - here is the sql generated

SELECT tblSalesLedger.InvoiceNumber, tblSalesLedger.InvoiceDate, tblSalesLedger.CustomerName, tblSalesLedger.InvoiceAmount, tblSalesLedger.PaidDate FROM tblSalesLedger WHERE tblSalesLedger.PaidDate BETWEEN #Jan/01/2002# AND #Jan/31/2002# AND tblSalesLedger.PaidDate BETWEEN #Feb/01/2002# AND #Feb/28/2002#

i ran this in a query and i get no records returned.

any ideas?

 

[allanon2]

Opps. Logic erro on my part. Change the line:

CreateWhereString = CreateWhereString & " AND "

To Read:

CreateWhereString = CreateWhereString & " OR "

 

[Egglar]

yes!

every thing works fine now, this is great!
Can I just take this opportunity to thankyou for all your help, its probably been a bit of an effort trying to get this working, as im not the most experience user in the world

I truly appreciate the time u have spent helping me. Thanks again.

Ive got to add a few more things now, such as ORDER BY, and so forth, but I will muddle on my own, and modify what you have helped me do already

Can I just ask what happens / will happen when 2003 comes around?

 

[allanon]

If you kept up with the changes that we did then it will be no problem. When we put in that change to pass a year to the function like this:
strSQL = strSQL & CreateWhereString(2002)

we made it so that it will work with any year past, present or future just by changeing the parameter. ie

strSQL = strSQL & CreateWhereString(2000)

for 2000 data;

strSQL = strSQL & CreateWhereString(2020)

for year 2020 data. Because the month end day is a caculated value it will always work be it a leap year, etc. The only thing that we did not do is put a lot of error checking in it. You may need to do that eventually but I doubt it.

 

[allanon]

One other thing. Get used to passing values to functions with variables. It is fine to test with a value BUT when you go live with the database it is better to do this:

DIM MyYear as integer

MyYear = 2002

strSQL = strSQL & CreateWhereString(MyYear)

Than this:
strSQL = strSQL & CreateWhereString(2002)

The reason for this is it make the program easier to change. For instance if you call the CreateWhereString 12 times in your program with the code like this:
strSQL = strSQL & CreateWhereString(2002)
you would have to change the program in 12 places to change the year and even then you would not be sure to get them all. If you use a variable like MyYear you would only have to change the line:

MyYear = 2002
to whatever year you need and then you are sure to get them all.

Have Fun

 

[Egglar]

ah ok, i will do that now i think.

Ive just been testing etc, trying to do an ORDER BY statment to add to the createwherestring

this is what ive done so far, to put into the function.

If cboPrintOrder = "Customer Name" Then
CreateWhereString = CreateWhereString & " ORDER BY tblSalesLedger.CustomerName ASC"
Else
CreateWhereString = CreateWhereString & " duh"
End If


as you can guess im using the "duh" to test to see if the code is picking up the value from the combo box and adding it to the end of the CreateWhereString.

and its not. what ever is selected in cboPrintOrder "duh" still gets added to the end of the sql string. ive tried four differnt ways of getting the IF statment to take the value from the combo box, none have worked, they are:

If cboPrintOrder = "Customer Name" Then
If cboPrintOrder.text = "Customer Name" Then
If frmSalesLedgerPrint.cboPrintOrder = "Customer Name" then
If frmSalesLedgerPrint.cboPrintOrder.text = "Customer Name" then

When ever .text is used then i press the command button, i get "object required" error box appear.

am i doing this right?

 

[allanon2]

What displays in a combobox is not always what is returned. Before your "If cboPrintOrder = "Customer Name" Then" type this: debug.print me.cboPrintOrder. You may be surprised at the result. Look in the immediate window for the result.

 

[Egglar]

i get an error message "invalid use of Me keyword"

i put it just above the begining of the if statment

 

[allanon2]

Oh. Ok. Where are you putting the statement "If cboPrintOrder = "Customer Name" Then"? What event? What Module? What Form? Etc.

 

[Egglar]

the IF statemnt is going in the same module and function as the one above (createwherestring)
i am putting the code just below Next iIndex and just above Exit Function.

Is this right?

 

[allanon2]

TI would not do that. I would leave the testing of the cbo in the same routinve as the call to your function. It should go in this procedure:

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

 

[Egglar]

i put debug.print me.cboPrintOrder into the Private Sub cmdPrintSpecific_Click() and clicked the button, in the immdeiate window, the result was "Customer Name" which is exactly what the IF statment askes for. Strange that its not working?

 

[allanon2]

The if has to be in your click event as well.

 

[Egglar]

that works now, thankyou! i paste the sql from the immediate window into the query and it orders by the field selected in the combo box, how ever, it doesent order it in the report?

SELECT tblSalesLedger.InvoiceNumber, tblSalesLedger.InvoiceDate, tblSalesLedger.CustomerName, tblSalesLedger.InvoiceAmount, tblSalesLedger.PaidDate FROM tblSalesLedger WHERE tblSalesLedger.PaidDate BETWEEN #Jan/01/2002# AND #Jan/31/2002# OR tblSalesLedger.PaidDate BETWEEN #Feb/01/2002# AND #Feb/28/2002# OR tblSalesLedger.PaidDate BETWEEN #Mar/01/2002# AND #Mar/31/2002# ORDER BY tblSalesLedger.CustomerName ASC

thats the sql being generated.

 

[allanon2]

Check the 'Order By On' to make sure it is yes. It is on the data property tab for the report. There is also an 'Order By' property. You may have to set that.

 

[Egglar]

hmm ive tried a few differnt things in the "data" property tab for the report and i still cant get it to order from the sql statemnt.

the only other thing i can think of is putting the sql string into a query, then generating the report from that query. But im sure u probably have a better idea than that

 

[allanon]

What are you doing now. It is not usual for a report to not sort. After you have created you sql string do a debug.print strsql. Copy it from the immidiate window and paste it into the sql window of a new query. Run it and see if it is sorted. Also post it here so I can look at it. Tell me what you are doing with the sql string after you have it.