VBA filter records (1 Viewer)

[Vulcan1500]

In VBA I would like to filter a recordset using the comboboxes on a form. I have three comboboxes. One for the year, one for the quarter and one for VAT. The comboboxes are populated correctly. Afrte selecting a value for the year the recordset is filtered. This also is done when the quarter is selected. The problem starts when I select VAT. I believe that the cause of the problem is that VAT is a percentage. The statement in the WHERE clause is:

 strWhere = "qryBTWOverzichtFactuur.JaarKwartaal='" & Me.cboJaar & "-" & Me.cboKwartaal & "' AND " & "qryBTWOverzichtFactuur.BTWTarief = " & Me.cboBTWTarief
 strSql = strSql & "SELECT qryBTWOverzichtFactuur.pkFactuur, "
strSql = strSql & "qryBTWOverzichtFactuur.JaarKwartaal, "
strSql = strSql & "qryBTWOverzichtFactuur.Nummer, "
strSql = strSql & "qryBTWOverzichtFactuur.Datum, "
strSql = strSql & "qryBTWOverzichtFactuur.Bedrijfsnaam, "
strSql = strSql & "Format([qryBTWOverzichtFactuur].[TotaalexBTW],'Currency'), "
strSql = strSql & "Format([qryBTWOverzichtFactuur].[BTWTarief],'Percent'), "
strSql = strSql & "Format([qryBTWOverzichtFactuur].[TotaalBTW],'Currency'), "
strSql = strSql & "Format([qryBTWOverzichtFactuur].[TotaalinBTW],'Currency'), "
strSql = strSql & "qryBTWOverzichtFactuur.DatumBTWAfgedragen "
strSql = strSql & "FROM qryBTWOverzichtFactuur WHERE "
strSql = strSql & strWhere

After selections the strWhere is:
qryBTWOverzichtFactuur.JaarKwartaal='2015-1' AND qryBTWOverzichtFactuur.BTWTarief = 0,21
When I manualy change 0,21 (21%) to 0.21 it works, but I'm unable to force this format in the query.
Any advice on this is much appreciated.

 

[Ranman256]

In a continuous form that shows all records, when user clicks the Filter button,
Test all controls for a possible filter then build the where clause.

sub btnFilter_click()
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cbo2) then sWhere = sWhere & " and [Contact]=" & chkContact.value

if IsNull(cboState) and  IsNull(txtName) and  IsNull(cbo2) then
  me.filterOn = false
else
       'remove 1st And
    sWhere= mid(sWhere,4)

          'just use the filter
  me.filter = sWhere
  me.filterOn = true
endif
end sub

 

[sneuberg]

Maybe you could remove the % with the Val function, e.g.

...."qryBTWOverzichtFactuur.BTWTarief = " & Val(Me.cboBTWTarief)

 

[JHB]

You can use the replace function:

BTWTarief = " & Replace(Me.cboBTWTarief, ",", ".") & " "

 

[static]

You should always format displayed values rather than the data.
So take the formatting off the SQL and put it on the control. That way your WHERE (and any calculations on the form, etc) should always work ok.

 

[JHB]

It has to do with the local settings, some countries use "," as decimal separator instead of "." Actually the same problem as by dates.

 

[static]

Local settings apply text formatting to displayed values. The underlying data will be the same wherever you are.

 

[JHB]

Sorry I don't think you understand the problem, so I suppose that you are in country that use "." as decimal separators or the local settings in your computer is set up to use it.

 

[static]

Sorry I don't think you understand the problem

Indeed. Reading failure. Sorry.

It's still related though. The combo should be bound to the non formatted value.

 

[JHB]

The combo should be bound to the non formatted value.

The value isn't formatted, it is a decimal number value like shown in the picture.

 

[sneuberg]

I believe there's a way to do this with a querydef and avoid these issues that come up from concatenation. The combo box values are assign to querydef parameters. One of our forum members used to propose this method quite often. I thought I bookmarked one of the threads but now I can't find it. Anybody remember how this was done? Or who was proposing it?

 

[static]

Well I learned something today.

I can understand the value displaying like that in the table but didn't expect it to flow through the code as well
Debug.Print CDbl(List0)

But it does.

 

[Vulcan1500]

Thanks for all information and possible solutions. Val-function did stop the error, but didn't result in a recordset. Replace was the solution.