Something wrong with MS sample code (VB) Access 2007

[jal]

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)

 

[piedpiper8]

I don't know why you'd get the same error. It worked fine for me, using the following test:



Dim strSql As String
strSql = "ContactName = 'John Smith' AND "
strSql = Left(strSql, Len(strSql) - 5)
MsgBox (strSql)

It only works, and does not produce an error if this line of code is added (without it, I get the error)
strSql = "ContactName = 'John Smith' AND "


This is how the code looks now.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If cboFilter1 <> "" Then strSql = strSql & " Employee = " & quote & cboFilter1 & quote & " AND "
If cboFilter2 <> "" Then strSql = strSql & " Company = " & quote & cboFilter2 & quote & " AND "
If cboFilter3 <> "" Then strSql = strSql & " Project = " & quote & cboFilter3 & quote & " AND "
If cboFilter4 <> "" Then strSql = strSql & " Task = " & quote & cboFilter4 & quote & " AND "
strSql = "Employee = 'Keith Beland' AND "
strSql = Left(strSql, Len(strSql) - 5)
MsgBox (strSql)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True
End Sub

 

[piedpiper8]

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)

Ok, that worked... I no longer get the error - however, nothing happens now, the report does not get filtered????

 

[piedpiper8]

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)

Ok it now works... I mis-titled the combo boxes... it was labled cboFilter1, but should be only "Filter1"

Im testing right now.

 

[piedpiper8]

Ok it now works... I mis-titled the combo boxes... it was labled cboFilter1, but should be only "Filter1"

Im testing right now.

OMG!!!!!! - It works.... I would like to thank you so much!!!

Now on to my next problem, I need to be able to filter the report by a dates, i.e. enter a range, example Oct 1 to Oct 10, and only show those results, of course with the variables seleted by the combo boxes.

How do I begin?

 

[jal]

OMG!!!!!! - It works.... I would like to thank you so much!!!

Now on to my next problem, I need to be able to filter the report by a dates, i.e. enter a range, example Oct 1 to Oct 10, and only show those results, of course with the variables seleted by the combo boxes.

How do I begin?

Basically the same thing you already did, wouldn't you say?

Probably have two textboxes, txtStartDate and txtEndDate.

And then suppose your dateColumn is called OrderDate

Dim startDate as Date, endDate as Date
If Len(txtstartDate) > 0 or Len(txtEndDate) > 0 Then
On Error Goto InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: if err.NumBer > 0 Then
MsgBox("Bad date.")
exit sub
end if
strSql = StrSql & " OrderDate >= " & quote & startDate & quote & " Order Date <= " & quote & endDate & Quote & " AND "
On Error Goto 0 'resets it to regular error mode

End if


Something like that, but I didn't test it.

 

[jal]

I made some changes. Hope I got it right this time.

 

[piedpiper8]

Basically the same thing you already did, wouldn't you say?

Probably have two textboxes, txtStartDate and txtEndDate.

And then suppose your dateColumn is called OrderDate

Dim startDate as Date, endDate as Date
If Len(txtstartDate) > 0 or Len(txtEndDate) > 0 Then
On Error Goto InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: if err.NumBer > 0 Then
MsgBox("Bad date.")
exit sub
end if
strSql = StrSql & " OrderDate >= " & quote & startDate & quote & " Order Date <= " & quote & endDate & Quote & " AND "
On Error Goto 0 'resets it to regular error mode

End if


Something like that, but I didn't test it.

Thank you... we are getting close...

Here is how the code looks like now.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If Filter1 <> "" Then strSql = strSql & " Employee = " & quote & Filter1 & quote & " AND "
If Filter2 <> "" Then strSql = strSql & " Company = " & quote & Filter2 & quote & " AND "
If Filter3 <> "" Then strSql = strSql & " Project = " & quote & Filter3 & quote & " AND "
If Filter4 <> "" Then strSql = strSql & " Task = " & quote & Filter4 & quote & " AND "
Dim startDate As Date, endDate As Date
If Len(txtStartDate) > 0 Or Len(txtEndDate) > 0 Then
On Error GoTo InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: If Err.Number > 0 Then
MsgBox ("Bad date.")
Exit Sub
End If
strSql = strSql & " Date >= " & quote & startDate & quote & " Date <= " & quote & endDate & quote & " AND "
On Error GoTo 0 'resets it to regular error mode
End If
If Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True



When I execute... I get the folowing error:

"Syntax error (missing operator) in querry expression '(Date >='01/10/2008' Date <='01/10/2008')' "

 

[jal]

strSql = strSql & " Date >= " & quote & startDate & quote & " Date <= " & quote & endDate & quote & " AND "

Sorry, I missed an AND.

Try this:

strSql = strSql & " Date >= " & quote & startDate & quote & " AND Date <= " & quote & endDate & quote & " AND "

 

[jal]

Also, if your column is called "Date" that's proabably a reserved keyword. Somehow you will need to get brackets in there so it reads as:

[Date]

otherwise it conflicts with reserved keyword

 

[boblarson]

For dates you have to use the # delimiter and you shouldn't have a field named date because that is an Access reserved word. So you have to put it in brackets.

strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "

 

[piedpiper8]

Also, if your column is called "Date" that's proabably a reserved keyword. Somehow you will need to get brackets in there so it reads as:

[Date]

otherwise it conflicts with reserved keyword

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

 

[jal]

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

Did you try Bob's advice?

 

[boblarson]

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

What error message?

 

[jal]

By the way I wasn't asking you to put brackets in the column design view. I was asking you to add them to your strSql.

 

[piedpiper8]

By the way I wasn't asking you to put brackets in the column design view. I was asking you to add them to your strSql.

I'm sorry, misunderstood.

 

[piedpiper8]

For dates you have to use the # delimiter and you shouldn't have a field named date because that is an Access reserved word. So you have to put it in brackets.

strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "

This worked... OMG thanks you both!!!!!

Only one small problem left.... Filter by date works like a charm except when I use single digits for the "Day", perhaps this might happen for month as well - I will test.

example.... I enter start date (dd/mm/yyyy) "10/10/2008" end date "31/10/2008" and the filter works great, but If I use the following, start date "01/10/2008 and end date "09/10/2008" - it blanks out the report.... as if there is no entry within those variables... I checked my database and data should be there in the report... weird eh?

I will do more testing, you do you think the problem is?


Again, thank you both for your help - you guys are awesome!

Also, I will study all the code you share, so I know how to do this on my own... later on today, after I get this resolved, I will purchase a book... any recommendations?

 

[boblarson]

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

 

[jal]

As far as books, I like Safari books online, because

(1) It's cheap. Order a monthly subscription and then cancel or renew at any time (about $10 per month).

(2) You can copy and paste code snippets into your notes (if you change the view mode to HTML).

 

[piedpiper8]

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

This line of code? :
strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "