Select statement in Recordsource 1

[checkOut]

The sql-statement below drives me crazy, i can't found what's wrong in it.
Prijs1 (10,3) is from a currency (euro) field on a form

SELECT STALEN_tblProduct.product_ID, STALEN_tblProduct.pr_beschrijving AS Product, STALEN_tblProduct.MP_Id, MARKTPARTIJ.bedrijfsnaam AS Fabrikant, STALEN_tblProduct.Staal_Prijs, STALEN_tblProduct.blnDelete FROM STALEN_tblProduct INNER JOIN MARKTPARTIJ ON STALEN_tblProduct.MP_Id = MARKTPARTIJ.MP_Id WHERE (Staal_prijs > '10,3') AND (STALEN_tblProduct.blnDelete = 0)

Thanx in advance

Gerard

 

[PHV]

Have you tried this ?
Staal_prijs > 10.3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SJMcAbney]

Also, put your fields and tables within square brackets as Access does not like fields and table names with 'special characters' such as the underscore.

i.e.

[My_Table].[My_Field]

Stewart J. McAbney | Talk History

 

[checkOut]

Thanx PHV for your reacion,
that was what I try.
But the value is coming from a field: Prijs1 (Currency;euro)
I can't format that field to ##0.00, if you can help me that way, its very useful!!

Thanx in advance,

Gerard

 

[PHV]

Are you creating the SQL by code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[checkOut]

Yes, I set the code below:

Private Function setZoekSql() As String
Dim tmpSQL As String, strWHERE As String
tmpSQL = "SELECT STALEN_tblProduct.product_ID, STALEN_tblProduct.pr_beschrijving " & _
"AS Product, STALEN_tblProduct.MP_Id, MARKTPARTIJ.bedrijfsnaam AS Fabrikant, " & _
"STALEN_tblProduct.Staal_Prijs, STALEN_tblProduct.blnDelete FROM STALEN_tblProduct " & _
"INNER JOIN MARKTPARTIJ ON STALEN_tblProduct.MP_Id = MARKTPARTIJ.MP_Id"
strWHERE = ""
If Nz(Me.lngFabrikant, 0) <> 0 Then
strWHERE = strWHERE & "(STALEN_tblProduct.MP_ID = " & Me.lngFabrikant & ") AND "
ElseIf Nz(Me.txtFabrikant, "") <> "" Then
strWHERE = strWHERE & "(Marktpartij.bedrijfsnaam LIKE " & adhHandleQuotes("*" & _
Me.txtFabrikant & "*") & ") AND "
End If
If Nz(Me.lngProduct, 0) <> 0 Then
strWHERE = strWHERE & "(STALEN_tblProduct.product_ID = " & Me.lngProduct & ") AND "
ElseIf Nz(Me.txtProduct, "") <> "" Then
strWHERE = strWHERE & "(pr_beschrijving LIKE " & adhHandleQuotes("*" & _
Me.txtProduct & "*") & ") AND "
End If
If Nz(Me.Prijs1, 0) <> 0 And Nz(Me.Prijs2, 0) <> 0 _
And Me.Prijs2.Visible And Nz(Me.cboKies, -1) = 2 Then
strWHERE = strWHERE & "(Staal_prijs Between '" & Me.Prijs1 & _
"' AND '" & Me.Prijs2 & "') AND "
ElseIf Nz(Me.Prijs1, 0) <> 0 And Nz(Me.cboKies, -1) = 0 Then
strWHERE = strWHERE & "(Staal_prijs > '" & Me.Prijs1 & "') AND "
ElseIf Nz(Me.Prijs1, 0) <> 0 And Nz(Me.cboKies, -1) = 1 Then
strWHERE = strWHERE & "(Staal_prijs < '" & Me.Prijs1 & "') AND "
End If
strWHERE = strWHERE & "(STALEN_tblProduct.blnDelete = " & Nz(Me.blnDelete, 0) & ") AND "
If strWHERE <> "" Then
strWHERE = " WHERE " & Mid(strWHERE, 1, Len(strWHERE) - 5)
End If
setZoekSql = tmpSQL & strWHERE
End Function

Prijs1 and Prijs2 gives the problems (are currency;euro fields)

Greetz
Gerard

 

[PHV]

You may try something like this:
strWHERE = strWHERE & "(Staal_prijs Between " & Format(CCur(Me.Prijs1), "0.00") & _
" AND " & Format(CCur(Me.Prijs2), "0.00") & ") AND "


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Jet expects numbers in US format. There's probably a function returning this, but one version could be something like this when concatenating the price into the string (i e, replace Me.Prijs1 with):

[tt]replace(ccur(Me.Prijs1),",",".")[/tt]

and as PHV has alredy stated, drop the single quotes (text delimiters)

Roy-Vidar

 

[checkOut]

SELECT STALEN_tblProduct.product_ID, STALEN_tblProduct.pr_beschrijving AS Product, STALEN_tblProduct.MP_Id, MARKTPARTIJ.bedrijfsnaam AS Fabrikant, STALEN_tblProduct.Staal_Prijs, STALEN_tblProduct.blnDelete FROM STALEN_tblProduct INNER JOIN MARKTPARTIJ ON STALEN_tblProduct.MP_Id = MARKTPARTIJ.MP_Id WHERE (Staal_prijs Between 103 AND 109) AND (STALEN_tblProduct.blnDelete = 0)

I put the ccur(replace..) in my code above.
it gives 103 and 109 for 10,3 and 10,9
What can be wrong?

Greetz

Gerard

 

[RoyVidar]

Then I'm sorry, this I don't understand. Could perhaps try adding a cstr within?

[tt]replace(cstr(ccur(Me.Prijs1)),",",".")[/tt]

Would this also be the return if you du a

[tt]debug.print replace(cstr(ccur(Me.Prijs1)),",",".")[/tt]

without assigning to the sql string?

Roy-Vidar

 

[checkOut]

Thanx, it works.
Strange but when I
dim cPrijs1 as currency, cPrijs2 as currency
it doesn't work.
When I give no property, thus
dim cPrijs1, cPrijs2
everything do correct!

Anyway: Thanx!

Greetz,

Gerard

 

[SJMcAbney]

Dimensioning a variable withoug any data type defaults it to a Variant data type. Your field is probably not a Currency field after all.

Stewart J. McAbney | Talk History