Having trouble executing my SQL statement

[Fitzy1012]

Hello,
Hopefully someone can help me. I am trying to execute an SQL statement in a Microsoft Access 2016 application. I'm am trying to update a database using the "Update statement" in my SQL but I get keep getting the same error message "Two few parameters. Expected 1"

My SQL Statement is dbs.Execute "Update Location SET Comment = Eval(TxtLocation1.Text) Where ID = 1;"

I'm thought that using the eval function it would force a variable from TxtLocation1.text but it failed to do so.

What am I doing wrong

Steve
Fitzy1012@aol.com

 

[dhookom]

First of all, Access rarely uses the Text property. It is only available when the control has the focus. You can use the Value property but it is the default property of a bound control so it is not necessary.

Can we assume the code is running in a form that has a control named txtLocation and it is a string? If so, try:

dbs.Execute "Update Location SET Comment = """ & Me.TxtLocation1 & """ Where ID = 1", dbFailOnError

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

To help you see what you actually execute, you may re-arrange Duane's code to do this:

Dim strSQL As String
strSQL = "Update Location SET Comment = """ & Me.TxtLocation1 & """ Where ID = 1"
Debug.Print strSQL  [green]'<--Comment this out if it is working OK[/green]
dbs.Execute strSQL, dbFailOnError

This way you would see why your way did not work:

Dim strSQL As String
strSQL = "Update Location SET Comment = Eval(TxtLocation1.Text) Where ID = 1;"
Debug.Print strSQL 
dbs.Execute strSQL, dbFailOnError

---- Andy

There is a great need for a sarcasm font.

 

[Fitzy1012]

Andy,

Think you so much for your help. I have not used access for 20 years. But Can you explain why your sql works and mine does not. originally I did not use the .text.;

MINE - dbs.Execute "Update Location SET Comment = TxtLocation1 Where ID = 1;"

yours - dbs.Execute "Update Location SET Comment = """ & Me.TxtLocation1 & """ Where ID = 1",

 

[Andrzejek]

Actually, Duane helped you, I just gave you a hint

But to answer your WHY question:
You have a text box [tt]TxtLocation1[/tt] that you want to put whatever is in that box into a field in your table. Let's say you typed: "This is my text" in it.

The database sees your SQL as:[tt]
Update Location SET Status-IN = false, Comment = TxtLocation1 Where ID = 1[/tt]
which the DB understands as: Update the table "Location" and put a word "TxtLocation1" (the word, NOT what this text box contains) in the field named "Comment". But since the field "Comment" is set up as text, I need quotes around whatever you put in it. DB does not know that you want to put whatever you have in the field [tt]TxtLocation1[/tt] (which would be "This is my text")

That's why:
dbs.Execute "Update Location SET Comment = """[blue] & Me.TxtLocation1 & [/blue]""" Where ID = 1"
works because you REPLACE whatever Me.TxtLocation1 controls holds with "[blue]This is my text[/blue]" and you have the quotes around the text.

As you know, Duane's code works, but you can try my 'modification' to his code to SEE what is going on...

The bottom line is: what YOU THINK should be executed, and what your data base SEES to be executed are two different 'animals'.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy is spot on with the Debug.Print suggestion. I've been writing Access code for about 25 years and use Debug.Print consistently while developing applications. When the code is running as expected I just comment out the line.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Duane,

My version of your way:
[tt]strSQL = "Update Location SET Comment = [blue]"""[/blue] & Me.TxtLocation1 & [blue]"""[/blue] Where ID = 1"
[/tt]
would be:
[tt]strSQL = "Update Location SET Comment = [blue]'"[/blue] & Me.TxtLocation1 & [blue]"'[/blue] Where ID = 1"
[/tt]
so in my version a string is surrounded by single quotes. Triple double quotes are just confusing to me
Both ways are (probably) acceptable, right?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I have gotten into the habit of using the 3 doubles because it is more common to insert values into fields that might have a single quote like names: O'Brien, O'Keefe. These imbedded single quotes will break your code. I expect the safest solution is to use CHR(34):

strSQL = "Update Location SET Comment = " & CHR(34) & Me.TxtLocation1 & CHR(34) & " Where ID = 1"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Fitzy1012]

then how would you update a boleen field in a database? I the same database I have a field called Status-IN which is booleen.

 

[Andrzejek]

You are right with O'Brien, O'Keefe, etc.
What I do is Replace any single quote with 2 single quotes:

strSQL = "Update Location SET Comment = '" & [blue]Replace([/blue]Me.TxtLocation1[blue], "'", "''")[/blue] & "' Where ID = 1"

As for Boolean values, I would be tempted to try 0 for False, and any other value for True, like -1
What you had before should also work:[tt]
Update Location SET Status-IN = [blue]false[/blue][/tt]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

A boolean field is numeric which don't require any delimiter. Dates use #.

strSQL = "Update Location SET [Status-IN] = " &  Me.NameOfControl & " Where ID = 1" 

strSQL = "Update Location SET [DateField] = #" &  Me.NameOfControl & "# Where ID = 1"

You can determine all of this if you create an update query and then look at the SQL view.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Fitzy1012]

thanks guys, including Duane lol!!! You guys are great!!!!