[RESOLVED] syntax error missing in query expression

[salsa31]

hey
im getting a error every time i try to update a field " syntax error missing in query expression"
i checked the code i dont know whats wrong.
this is the code when i load the form

Private Sub Form_Load()
     Me.WindowState = 2
    LsVw.ColumnHeaders(1).Icon = "asc"
    Set RS = CN.Execute("SELECT * FROM Expenses ORDER BY ExpDate")
    While Not RS.EOF
        Set Itm = LsVw.ListItems.Add(, , Format(RS!ExpDate, "dd/mm/yyyy"), , "dolar")
        Itm.Tag = RS!ExpID
        Itm.SubItems(1) = RS!ExpType
        Itm.SubItems(2) = RS!ExpQuantity
        Itm.SubItems(3) = FormatCurrency(RS!ExpCost)
        RS.MoveNext
    Wend
    ListRTL LsVw
        mLVClrHdr.HookToLV LsVw.hwnd, True
    mLVClrHdr.glHdrBkClr = &HD1B499
    mLVClrHdr.glHdrTextClr = vbWhite
    m_hookedLV = True
End Sub

and this is when i edit , i get the error

 CN.Execute ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).text, ",", ".") & _
                " WHERE ExpID= " & .Tag)

tnx for the help

[Doogle]

I'd take a look at the completed SQL and see if it's anything obvious

Dim strSQL As String
strSQL = ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).Text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).Text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).Text, ",", ".") & _
                " WHERE ExpID= " & .Tag)
Debug.Print strSQL

that will print the sql statement to the Immediate Window.

How are columns ExpQuantity and ExpCost defined in the Table? (what type are they ?)

[salsa31]

ExpQuantity - number


ExpCost - Currency

[TysonLPrice]

I think the issue will be obvious if you follow Doogle's suggestion. What is in the string you are passing to SQL?

[salsa31]

the only thing that i changed is this

Itm.SubItems(3) = FormatCurrency(RS!ExpCost)

i added the formatcurrency

[salsa31]

I'd take a look at the completed SQL and see if it's anything obvious

Dim strSQL As String
strSQL = ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).Text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).Text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).Text, ",", ".") & _
                " WHERE ExpID= " & .Tag)
Debug.Print strSQL

that will print the sql statement to the Immediate Window.

How are columns ExpQuantity and ExpCost defined in the Table? (what type are they ?)

UPDATE Expenses SET ExpDate= #12/26/2010#, ExpType= 'computer disk', ExpQuantity= 11, ExpCost= ¤ 100.00 WHERE ExpID= 9

[TysonLPrice]

I'll try one more time...

This doesn't mean a thing to someone trying to see where the SQL error is:

UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).Text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).Text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).Text, ",", ".") & _
                " WHERE ExpID= " & .Tag)

What is being passed to SQL does. Show me that and I think I can help. Keep it a sercret and you are on your own as far as I am concerned.

[salsa31]

this is the save & update code

Private Sub BttnSave_Click()
 
    If Len(Trim$(txt(1).text)) = 0 Then
        MsgBox "empty field", vbInformation, ""
        txt(1).SetFocus
        Exit Sub
    ElseIf txt(2).text = 0 Then
        MsgBox "0 is not a option", vbInformation, ""
        txt(2).SetFocus
        Exit Sub
    ElseIf txt(3).text = 0 Then
        MsgBox "empty field", vbInformation, ""
        txt(3).SetFocus
        Exit Sub
    End If
    
    If NewRec Then
        Dim NewID As Long
        NewID = NextID("ExpID", "Expenses")
        CN.Execute "INSERT INTO Expenses " & _
            "(ExpID, ExpDate, ExpType, ExpQuantity, ExpCost) VALUES (" & _
            NewID & ", #" & _
            MyDate(PickDate.Value) & "#, '" & _
            RplS(txt(1).text) & "', " & _
            Replace$(txt(2).text, ",", ".") & ", " & _
            Replace$(txt(3).text, ",", ".") & ")"
        
        Set Itm = FrmExpenses.LsVw.ListItems.Add(, , Format(PickDate.Value, "dd/mm/yyyy"), , "dolar")
        Itm.Tag = NewID
        Itm.SubItems(1) = txt(1).text
        Itm.SubItems(2) = Replace$(txt(2).text, ",", ".")
        Itm.SubItems(3) = Replace$(txt(3).text, ",", ".")
        
        If ChkRepeat.Value Then
            PickDate.Value = Now
            txt(1).text = ""
            txt(2).text = 1
            txt(3).text = 0
            ChkRepeat.Value = 0
            PickDate.SetFocus
            Exit Sub
        End If
    Else
        With FrmExpenses.LsVw.SelectedItem
           Dim strSQL As String
strSQL = ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).text, ",", ".") & _
                " WHERE ExpID= " & .Tag)
Debug.Print strSQL
            
            .text = Format(PickDate.Value, "dd/mm/yyyy")
            .SubItems(1) = txt(1).text
            .SubItems(2) = txt(2).text
            .SubItems(3) = txt(3).text
             
 
 
 

 
        End With
    End If
    Unload Me
End Sub

[TysonLPrice]

In:
UPDATE Expenses SET ExpDate= #12/26/2010#, ExpType= 'computer disk', ExpQuantity= 11, ExpCost= ¤ 100.00 WHERE ExpID= 9

What is ¤ right before 100.00?

[Zvoni]

UPDATE Expenses SET ExpDate= #12/26/2010#, ExpType= 'computer disk', ExpQuantity= 11, ExpCost=¤ 100.00 WHERE ExpID= 9

Is it just me, or is he trying to include the currency-symbol in the SQL-Update?

[salsa31]

its the currency symbol yes

[SamOscarBrown]

So,....that begs the question, Why?
If your ExpCost is a Currency field, you don't need to try that....
If, when you RETURN a value from the Currency field (ExpCost), you can prepend a dollar sign ($) in whatever control you display it..if you desire.

[salsa31]

beacuse i need to show the currency symbol in the listview

[si_the_geek]

So what?

That doesn't mean you need to store the currency symbol in the database (which is not supported in this manner).

You can follow the previous advice, and just add the currency symbol when displaying it in the listview.

[salsa31]

So what?

That doesn't mean you need to store the currency symbol in the database (which is not supported in this manner).

You can follow the previous advice, and just add the currency symbol when displaying it in the listview.

which advice?
im not trying to store it in the database. in the listview its already there
the thing is when i edit the value it shows the symbol next to the price and when i press update then i get that error

[si_the_geek]

which advice?

This advice:

If, when you RETURN a value from the Currency field (ExpCost), you can prepend a dollar sign ($) in whatever control you display it..if you desire.

im not trying to store it in the database. in the listview its already there

You aren't removing it, so you are (accidentally) trying to store it in the database... and you knew that back in post #6

Use Replace (or another method) to remove it.

[salsa31]

look at post #5

i added the formatcurrency just to display it in the listview not to store it ok?
when i hit the edit button and update the i get that error.
so my question is how do i show it in the listview with out gertting errors.

[si_the_geek]

Adding the currency character to the listview is fine, but you need to remove the currency character from the value you put in to the SQL statement.

One way is to use replace (to change the currency character to an empty string: "").

[salsa31]

where do i put this string or replace it?

"").

[Zvoni]

I find it kinda sad, that people always seem to forget that all items in a Listview are Strings!

The moment you add the currency symbol before showing the value in the ListView you're changing it de facto into a string. (Without the currency-symbol, i think, you could still get by with auto-conversion).
So Si_the_geek told you exactly what to do: remove the currency-symbol before assigning it to the SQL-Update

[TysonLPrice]

I find it kinda sad, that people always seem to forget that all items in a Listview are Strings!

What about images or items in tags?

[salsa31]

Adding the currency character to the listview is fine, but you need to remove the currency character from the value you put in to the SQL statement.

One way is to use replace (to change the currency character to an empty string: "").

you meen like this

  CN.Execute ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).text, "", "") & _
                " WHERE ExpID= " & .Tag)

[si_the_geek]

Almost... you need to put the currency character in: Replace$(txt(3).text, "here", "")

Of course, you need to change the word here to whatever the currency symbol is.

[salsa31]

i still get a error of the syntax

CN.Execute ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).text, "$", "") & _
                " WHERE ExpID= " & .Tag)

[SamOscarBrown]

Does this help?

("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " & Replace$(txt(3).text, "$", "") & _
                " WHERE ExpID= " & .Tag & ")"

[salsa31]

no
still get the same message

[SamOscarBrown]

what is this: RplS(txt(1).text) ?

[salsa31]

name of the item.
rpls is if a user types a charector lik ' or " then it dosnt get a error

[SamOscarBrown]

OK---but confused. 'name of the item'? Is RPLS a function? Try removing this from your Update statement and see if you still get the same syntax error.
If RPLS is a function in your program, then you are simply passing it a textbox text value. What would it return? A value (string) that removes single and double quotes?

Whenever I first create a query (even simple ones, generally), I place a msgbox() check of the query before running it...that gives me the opportunity to see exactly what is being sent to the db.

If you do this with your statement, kindly post what the msgbox returns to you (as long as it is not sensitive information).

Out to lunch...be back in a bit.

[salsa31]

tnx for trying to help sam but the rpls has nothing to do with it.
it worked before i added the formatcurrency
this worked

Itm.SubItems(3) = RS!ExpCost

this dosnt work

Itm.SubItems(3) = FormatCurrency(RS!ExpCost)

[SamOscarBrown]

Well, if Itm.SubItems(3) = FormatCurrency (RS!ExpCost), then
Itm.SubItems(3) would equal "$45,324.98" if RS!ExpCost equaled 4532498. (Stored/viewed in the DB as 45,324.98).

BUT, the real issue is NOT what Itm.SubItems(3) is, but what is the STRING value of txt(3).text?

As was stated by others above, you can use the FormatCurrency to show the $ sign in the listview, but in order to insert/update your database, you need to make sure that sign is absent when you do your queries. As you are using textbox values to update your DB, you must somehow have taken the listview items and put them in textboxes....IOW you should NOT have a $ sign in the textbox, JUST in your listview.

This line you showed earlier (probably done in a msgbox) indicates that txt(3).text includes a $ sign. If so, replace it with a "". :
UPDATE Expenses SET ExpDate= #12/26/2010#, ExpType= 'computer disk', ExpQuantity= 11, ExpCost=¤ 100.00 WHERE ExpID= 9

[Doogle]

I can't believe there's been 30 replies ................and it's not resolved.

At the risk of repeating what's already been said. The first character in txt(3).text is a Currency symbol. When using the value of txt(3).Text to update a column in a Table, you must remove the currency symbol. A simple method would be:

CN.Execute ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " CCur(Mid$(txt(3).Text, 2) ) & _
                " WHERE ExpID= " & .Tag)

which should ignore the currency symbol and format the value to be assigned to ExpCost to whatever the regional settings require for a Currency Type.

I don't see where the "$" came into the discussion. It looks to me as if the currentcy symbol is "¤", perhaps OP missed Si's post

you need to put the currency character in: Replace$(txt(3).text, "here", "")

Of course, you need to change the word here to whatever the currency symbol is.

[salsa31]

hey doogle
see post #24
i changed replaced and nothing

[SamOscarBrown]

Right Doog....had just ASSUMED the $....whatever the funky looking symbol IS, that needs to be removed...whether he uses cCur() or Replace()

[salsa31]

let me declare something to all people here
i am not a pro like you people ok?
everytime i ask its beacuse i dont know and i wanna learn.

so if someone dont want to help save your remarks to your self.

[salsa31]

doogle look at post # 24

i did add the symbol $ but still got error.

[salsa31]

i get syntax error

CN.Execute ("UPDATE Expenses SET " & _
                "ExpDate= #" & MyDate(PickDate.Value) & "#, " & _
                "ExpType= '" & RplS(txt(1).text) & "', " & _
                "ExpQuantity= " & Replace$(txt(2).text, ",", ".") & ", " & _
                "ExpCost= " CCur(Mid$(txt(3).Text, 2) ) & _
                " WHERE ExpID= " & .Tag

[salsa31]

Attachment 101325

[SamOscarBrown]

Did you read Doog's post?

[salsa31]

yes i did i changed the symbol and did everthing still get error
look in the picture