SQL Statement, VBA Too Few Parameters expected 1

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016
Commented:
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
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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 SuterSenior Software Developer

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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ernest GroggSecurity Management InfoSec

Author

Commented:
Ok... Thanks... I had to leave f I r the night... I will be back in the office in the morning....
Distinguished Expert 2017

Commented:
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 GroggSecurity Management InfoSec

Author

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...
Ernest GroggSecurity Management InfoSec

Author

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?
Distinguished Expert 2017

Commented:
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 GroggSecurity Management InfoSec

Author

Commented:
OK, I see ...thanks!
Ernest GroggSecurity Management InfoSec

Author

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] & "'"
Distinguished Expert 2017
Commented:
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()
Ernest GroggSecurity Management InfoSec

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial