Link to home
Get AccessLog in
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

SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
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.
Avatar of Russ Suter
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

Avatar of Ernest Grogg

ASKER

Ok... Thanks... I had to leave f I r the night... I will be back in the office in the morning....
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.
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...
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?
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.
OK, I see ...thanks!
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] & "'"
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
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