Avatar of Ernest Grogg
Ernest Grogg
 asked on

SQL Statement, VBA Too Few Parameters expected 1

Failure on SQL Statement:  Too Few Parameters expected 1  

Dim strSQL As String
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = ""LogOff"" AND UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
"WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]))"


CurrentDb.Execute strSQL, dbFailOnError

Open in new window

Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Ernest Grogg

8/22/2022 - Mon
SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Dettman (EE MVE)

strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = 'LogOff' AND UserActivityLog.LogOffTimeStamp = #" & Format(Date(),"mm/dd/yy") & "# WHERE UserActivityLog.Activity2 Is Null  AND UserActivityLog.UserName = '" & [Forms]![frmLogin]![Name1] & "'"

Jim.
Russ Suter

I'm very rusty on my Access query syntax (been using MSSQL for years now) but typically if you're updating more than one column at a time you'd use a comma, not an AND operator. Try this...
Dim strSQL As String
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = ""LogOff"", UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
"WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]))"


CurrentDb.Execute strSQL, dbFailOnError

Open in new window

Ernest Grogg

ASKER
Ok... Thanks... I had to leave f I r the night... I will be back in the office in the morning....
Your help has saved me hundreds of hours of internet surfing.
fblack61
PatHartman

If you are unfamiliar with Access SQL syntax, there is no shame in using the QBE to build the query and no advantage to embed the SQL rather than to use a saved querydef.  But to explain the error.  It is caused primarily by two things.
1. You have a typo in the string and have misspelled some column name.
2. You actually have a parameter in the query - either a prompt or a reference to a form field or tempvar - and are running the query using DAO/ADO.  In this case, you would either fix the string (if you embedded the SQL in your code) to expand the actual value of the variable and concatenate that OR if you are running a saved querydef, you must use the .parameters!yourparmname property to set the value before you run the query.

If you are used to using compiled languages, you may not understand the overhead caused by embedded SQL.  Each time you execute embedded SQL, Access will have to create a new execution play.  However, when you execute a querydef, Access saves the calculated execution plan the first time the query runs and then reuses it.  This calculation doesn't take much time so you are not likely to suffer delays but if you are doing this inside a loop, you might want to reconsider.
Ernest Grogg

ASKER
Pat,

Thanks for the explanation.  Recently we moved to SQL Server Backend and in the next 6 months to 1 year we are moving from an Access frontend to the Web based front end and changing from querydef's to SQL statements seems to be the best bet for when we convert to the different frontend.

You are right, I am not familiar with building these statements and as usual I am the only one on the project, yet again.  So  that is my issue...I get that.

perhaps I need some clear reading that gives me the basics and lays out the "how to" guide...
Ernest Grogg

ASKER
for everyone else, these statements don't work, but the querydef does:

UPDATE UserActivityLog SET UserActivityLog.LogOffTimeStamp = Now(), UserActivityLog.Activity2 = "LogOff"
WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]));


is there are conversion for this?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

Did you try the syntax change suggested by Bill?  I didn't give you any syntax because others "fixed" the problem.  All I did was to explain the problem so you would know how to fix other queries.  Look at #2 again.

changing from querydef's to SQL statements seems to be the best bet for when we convert to the different frontend
You won't have a choice.  Access is a Rapid Application Development (RAD) tool and it does a great deal of work for you.  When you switch FE's, you'll be coding all that yourself.  The only reason you would need to switch to a web based FE is because you need to support anonymous users.  If all users are on your LAN, Access is a great tool.  You can easily support remote users with Access by using Citrix or RDP.

Another suggestion is to put a stop in the code so when you get to the line AFTER the one that builds the string, you can print the string to the immediate window.  That very often makes it clear what you did wrong.
Ernest Grogg

ASKER
OK, I see ...thanks!
Ernest Grogg

ASKER
Bill,  I looked at it and needed to make a couple of changes and this was the output:

UPDATE UserActivityLog SET UserActivityLog.Activity2 =  'LogOff' AND UserActivityLog.LogOffTimeStamp=Now()
 WHERE ((UserActivityLog.Activity2) Is Null) AND (UserActivityLog.UserName)='My Name'

but nothing entered into the table

here is the code I changed yours to allow it to go through..

strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 =  'LogOff' AND UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
" WHERE ((UserActivityLog.Activity2) Is Null) AND (UserActivityLog.UserName)='" & [Forms]![frmLogin]![Name1] & "'"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ernest Grogg

ASKER
Got it work finally...see below...Thanks for everyone for all the help and patience...Used a comma "," between LogOff and UserActivity instead of "AND"

Dim strSQL As String
MyDateValue = Date + Time
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 =  'LogOff' , UserActivityLog.LogOffTimeStamp='" & MyDateValue & "' " & vbCrLf & _
" WHERE ((UserActivityLog.Activity2)Is Null) AND (UserActivityLog.UserName)='" & [Forms]![frmLogin]![Name1] & "'"
CurrentDb.Execute strSQL, dbFailOnError

Open in new window



Here was the output:

UPDATE UserActivityLog SET UserActivityLog.Activity2 =  'LogOff' , UserActivityLog.LogOffTimeStamp='9/24/2018 11:37:38 AM' 
 WHERE ((UserActivityLog.Activity2)Is Null) AND (UserActivityLog.UserName)='My Name'

Open in new window