VBA SQL Query Issue (1 Viewer)

Learn2010

Registered User.
Local time
Today, 11:33
Joined
Sep 15, 2010
Messages
415
I can't seem to get the following to work. Can anyone help?

Code:
DoCmd.RunSQL "UPDATE tblLogins INNER JOIN tblLogin ON tblLogins.UserID = tblLogin.UserID" _
& "SET tblLogins.UserPasswordChanged = 'Y', tblLogins.FirstLoginDate = Date(), tblLogins.FirstLoginTime = Time()" _
& "WHERE tblLogins.UserPasswordChanged ='N'"

Thank you.
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:33
Joined
Jul 9, 2003
Messages
16,245
You might be better off explaining your problem better. However looking at it, my first guess is that your time function isn't returning what you think, so I would suggest running a query with just the time in, to see if you get the expected results. When you're stuck it's always a good idea to simplify and check the smaller parts first and then you'll have a better idea if the whole thing should work. Basic bug chasing really.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:33
Joined
Feb 19, 2013
Messages
16,555
I can't seem to get the following to work
doesn't help us to help you - you get an error message? the code doesn't compile? the update doesn't happen? the update happens but with the wrong values? something else?

In addition to Unc's comments you are also missing spaces before SET and WHERE

Always better to assign the code to a string, then debug.print so you can see what it looks like, then assign the string to the runsql command

and always better to show the whole function/sub code - for all we know the code is not running in the first place

Finally, ensure you have Option Explicit at the top of every module
 

isladogs

MVP / VIP
Local time
Today, 15:33
Joined
Jan 14, 2017
Messages
18,186
Saying something doesn't work isn't helpful. Always state what does happen e.g. error message / wrong results.

However in this case you've forgotten what I told you in yesterday's thread.
You need a space before both SET & WHERE

Code:
DoCmd.RunSQL "UPDATE tblLogins INNER JOIN tblLogin ON tblLogins.UserID = tblLogin.UserID" & _
        " SET tblLogins.UserPasswordChanged = 'Y', tblLogins.FirstLoginDate = Date(), tblLogins.FirstLoginTime = Time()" & _
        " WHERE tblLogins.UserPasswordChanged)='N';"

However, I recommend storing date & time together as a single field as it will simplify your code later on

Code:
DoCmd.RunSQL "UPDATE tblLogins INNER JOIN tblLogin ON tblLogins.UserID = tblLogin.UserID" & _
        " SET tblLogins.UserPasswordChanged = 'Y', tblLogins.FirstLogin = Now()" & _
        " WHERE tblLogins.UserPasswordChanged)='N';"

You can further simplify the code if the field names aren't in both tables

Code:
DoCmd.RunSQL "UPDATE tblLogins INNER JOIN tblLogin ON tblLogins.UserID = tblLogin.UserID" & _
        " SET UserPasswordChanged = 'Y', FirstLogin = Now()" & _
        " WHERE UserPasswordChanged)='N';"

BTW I recommend you don't use two such similar table names - easy to get confused
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:33
Joined
Sep 21, 2011
Messages
14,056
Also ignored the tip to build the SQL in a string, so as to easily see such errors?
Now that CJ_London has also advised this, perhaps the O/P will take note, and try it out.?
 

Users who are viewing this thread

Top Bottom