×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Having trouble executing my SQL statement

Having trouble executing my SQL statement

Having trouble executing my SQL statement

(OP)
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

RE: Having trouble executing my SQL statement

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:

CODE --> vba

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

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

RE: Having trouble executing my SQL statement

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

CODE

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

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

CODE

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.

RE: Having trouble executing my SQL statement

(OP)
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",

RE: Having trouble executing my SQL statement

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

But to answer your WHY question:
You have a text box TxtLocation1 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:
Update Location SET Status-IN = false, Comment = TxtLocation1 Where ID = 1

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 TxtLocation1 (which would be "This is my text")

That's why:
dbs.Execute "Update Location SET Comment = """ & Me.TxtLocation1 & """ Where ID = 1"
works because you REPLACE whatever Me.TxtLocation1 controls holds with "This is my text" 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... smile

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.

RE: Having trouble executing my SQL statement

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

RE: Having trouble executing my SQL statement

Duane,

My version of your way:
strSQL = "Update Location SET Comment = """ & Me.TxtLocation1 & """ Where ID = 1"

would be:
strSQL = "Update Location SET Comment = '" & Me.TxtLocation1 & "' Where ID = 1"

so in my version a string is surrounded by single quotes. Triple double quotes are just confusing to me smile
Both ways are (probably) acceptable, right?


---- Andy

There is a great need for a sarcasm font.

RE: Having trouble executing my SQL statement

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):

CODE --> vba

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

RE: Having trouble executing my SQL statement

(OP)
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.

RE: Having trouble executing my SQL statement

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

CODE

strSQL = "Update Location SET Comment = '" & Replace(Me.TxtLocation1, "'", "''") & "' 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:
Update Location SET Status-IN = false


---- Andy

There is a great need for a sarcasm font.

RE: Having trouble executing my SQL statement

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

CODE --> vba

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

RE: Having trouble executing my SQL statement

(OP)
thanks guys, including Duane lol!!! You guys are great!!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close