Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2018, 03:25 AM   #1
Learn2010
Newly Registered User
 
Join Date: Sep 2010
Posts: 384
Thanks: 69
Thanked 0 Times in 0 Posts
Learn2010 is on a distinguished road
VBA SQL Query Issue

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 Uncle Gizmo; 10-12-2018 at 05:11 AM. Reason: added code tags
Learn2010 is offline   Reply With Quote
Old 10-12-2018, 03:30 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,458
Thanks: 358
Thanked 773 Times in 738 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: VBA SQL Query Issue

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.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 10-12-2018 at 04:08 AM. Reason: idea simplify >>> idea to simplify
Uncle Gizmo is online now   Reply With Quote
Old 10-12-2018, 03:57 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,326
Thanks: 39
Thanked 3,339 Times in 3,234 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: VBA SQL Query Issue

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-12-2018, 03:58 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: VBA SQL Query Issue

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-12-2018 at 05:38 AM. Reason: Fixed typos
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Learn2010 (10-12-2018)
Old 10-12-2018, 04:30 AM   #5
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: VBA SQL Query Issue

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-12-2018, 05:29 AM   #6
Learn2010
Newly Registered User
 
Join Date: Sep 2010
Posts: 384
Thanks: 69
Thanked 0 Times in 0 Posts
Learn2010 is on a distinguished road
Re: VBA SQL Query Issue

Thanks for your help.

Learn2010 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Seems like a query issue to me pokemasterflex Queries 11 01-02-2014 12:17 PM
Issue with iif in query Angel69 Queries 3 12-18-2013 07:10 AM
Table Issue & Query Issue Jgr4ng3 Tables 1 03-18-2013 09:19 PM
Minor Query issue - Opening Query from Two Forms mcgilla Queries 1 03-05-2012 01:25 PM
Another Query Issue novice Queries 1 01-15-2010 10:23 AM




All times are GMT -8. The time now is 04:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World