WHERE clause (1 Viewer)

[radek225]

There is some error in where clause, but I don't know what is wrong

strSQL = "SELECT [Odpad], [LiczbaUzytkow], [ProcentZcalosci] FROM [TblRozklad] WHERE [Odpad]=" & liczbaarkuszy1 & " ORDER BY [Odpad] DESC;"

 

[pr2-eugin]

What is the "SOME" Error? Where/What have you declared the variable liczbaarkuszy1?

 

[namliam]

strSQL = " SELECT [Odpad], [LiczbaUzytkow], [ProcentZcalosci] " & _
         " FROM [TblRozklad] " & _
         " WHERE [Odpad]=" & liczbaarkuszy1 & _ 
         " ORDER BY [Odpad] DESC;"

Yes readable code is maintainable code.

If oddpad is a number it should work as is.
Assuming it is text you need:

         " WHERE [Odpad]=""" & liczbaarkuszy1 & """" & _

Assuming it is a date, you need:

         " WHERE [Odpad]=#" & liczbaarkuszy1 & "#" & _

 

[radek225]

dim liczbaarkuszy_1 as double
liczbaarkuszy1 = DMax("LiczbaArkuszy", "tblRozklad")
strSQL = "SELECT [Odpad], [LiczbaUzytkow], [ProcentZcalosci] FROM [TblRozklad] WHERE [Odpad]=" & liczbaarkuszy1 & " ORDER BY [Odpad] DESC;"

e.g. liczbaarkuszy1 = 20,833333333333333

 

[pr2-eugin]

What is the error. Mainly where is the error?

 

[radek225]

My code is

Dim strSQL As String
Dim recIn As Recordset
dim liczbaarkuszy1 as double

strSQL = "SELECT [Odpad], [LiczbaUzytkow], [ProcentZcalosci] FROM [TblRozklad] WHERE [Odpad]=" & liczbaarkuszy1 & " ORDER BY [Odpad] DESC;"
Set recIn = CurrentDb.OpenRecordset(strSQL)
While Not recIn.EOF
recIn.Edit
 recIn!LiczbaUzytkow = Int(recIn!ProcentZcalosci) + 1
 recIn.Update
 recIn.MoveNext
  Wend
  recIn.Close

 

[radek225]

syntax error in query expression (comma)
'[Odpad]=20,833333333333333'.

 

[pr2-eugin]

How about.

strSQL = " SELECT [Odpad], [LiczbaUzytkow], [ProcentZcalosci] " & _
         " FROM [TblRozklad] " & _
         " WHERE [Odpad]=" & CDbl(liczbaarkuszy1) & _ 
         " ORDER BY [Odpad] DESC;"

 

[radek225]

I wrote one line above. that is translated from my language - error message

 

[namliam]

probably needs a point instead of a comma as the decimal point?

 

[radek225]

pr2-eugin - the same problem

 

[radek225]

namliam - maybe, but why? value of "liczbaarkuszy1" is result of count with MS Access

 

[DonkeyKong]

lol. radek225 you should take a look at your table name/ field names and ask yourself if anyone who needs to work on the database in the future will be able to interpret the names you've used. I think that is the main problem. typical convention is something like... tbl_whatever aka tbl_(something understandable here would be really nice for everyone including yourself).

ALSO

a variable defined as DOUBLE would be a decimal point, NOT a comma.

Here's what I would STRONGLY recommend.
1.) CREATE -> QUERY DESIGN in the non-vba database view.
2.) Go to the SQL VIEW for the query.
3.) Now, past your query in there and replace your variable values with values from your table (from the correct columns, of course). You will want these values to be corresponding values that can bring up AT LEAST one record to be sure that it works.
4.) RUN the query using the run button in the DESIGN tab.
5.) Often times the SQL view will highlight the part of the query that is not correct.
6.) Change it up and keep running it til it works.

Good luck and do yourself a favor and do some reading on proper variable naming conventions.

 

[spikepl]

In SQL decimal numbers always use "." as separator. To output decimal numbers to SQL string with "." you can use
Str(myNumber)

which will work in any country setting.

 

[namliam]

Much like sql needs US date formatting, not euro date formatting (MM/DD/YYYY instead of DD/MM/YYYY)
It also needs a US dot as a decimal instead of a euro comma
I solve/prevent this by doing "* 100" in the vba code and doing " / 100 " in the sql (or also do * 100 in the sql which ever you fancy)

 

[spikepl]

@Namliam - put decimal numbers to be output into SQL in the function Str - then your code is international.

 

[radek225]

spikepl - I don't understand. So I need to convert number to string If I want to use variable in SQL Code, when my number from variable has decimal? I think something is wrong with it, conversion from number to string isn't efficient.

 

[gemma-the-husky]

how easy is it to do that?

it's one thing changing a dd/mm/yy to be mm/dd/yy

but how do you change a regional format like 12,34 to 12.34 for SQL purposes

 

[namliam]

Much like sql needs US date formatting, not euro date formatting (MM/DD/YYYY instead of DD/MM/YYYY)
It also needs a US dot as a decimal instead of a euro comma
I solve/prevent this by doing "* 100" in the vba code and doing " / 100 " in the sql (or also do * 100 in the sql which ever you fancy)

Just to make sure Radek sees my post since it disappeared to the previous page.

 

[spikepl]

@Radek225 Yes you do not understand!

You ARE converting a number to string when you create the SQL statement. The entire statmenet is a string!

But the built-in implicit conversion uses "," as decimal separator, which SQL hates. Whereas the function Str is designed for this very purpose, and preduces a decimal number with "." as separator no matter what country settings you have.