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

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Try this:

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

Open in new window


»bp
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Russ SuterCommented:
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

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Ernest GroggSecurity Management InfoSecAuthor Commented:
Ok... Thanks... I had to leave f I r the night... I will be back in the office in the morning....
0
PatHartmanCommented:
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.
0
Ernest GroggSecurity Management InfoSecAuthor Commented:
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...
0
Ernest GroggSecurity Management InfoSecAuthor Commented:
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?
0
PatHartmanCommented:
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.
0
Ernest GroggSecurity Management InfoSecAuthor Commented:
OK, I see ...thanks!
0
Ernest GroggSecurity Management InfoSecAuthor Commented:
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] & "'"
0
PatHartmanCommented:
Now() returns the current date and time.  This would almost never provide any matches when comparing "+" to another date field.

Date() returns the current date.

To know how to resolve the problem, we need to know what is in LogOffTimeStamp.  If that field holds date and time, you probably only want to consider the date part of the field.  So the solution might be:

DateValue(UserActivityLog.LogOffTimeStamp) = Date()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggSecurity Management InfoSecAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.