If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
I have used this similar construction to do update from text boxes to a table but this one doesn't seem to work right and I am not sure what exactly is wrong.
CurrentDb.Execute "UPDATE DISTINCTROW Screeners SET Screeners.LoginID = """ & Me![Text37] & """ AND Screeners.[First Name] = """ & Me![Text35] & """ And Screeners.[Last Name] = """ & Me![Text31] & """ And Screeners.[SSN] = """ & Me![Text46] & """ And Screeners.[Position] = """ & Me![Text44] & """ And Screeners.[Gender] = """ & Me![Text42] & """ Where ((Screeners.LoginID = """ & Me![Text52] & """));" What is basically is supposed to do is take the values in 6 text boxes and assign them to the values from the record in six seperate fields (LoginID, First Name, Last Name, SSN, Position, Gender), and it does this where the value of "LoginID" is equal to the value of a 7th text box. When I run the above code I get "Data Type Mismatch In Criteria Expression". Waht Exactly do I have wrong here, am an not sure what I need to change ? Any help would be appreciated |
#2
|
|||
|
|||
Update Query
You cannot use AND in the SET statement
The correct syntax for updating multiple columns is SET column1 = 'value1', column2 = value2 (value1 is a text, value2 is a number) Hope This Helps Gerald Stanley MCSD -----Original Message----- I have used this similar construction to do update from text boxes to a table but this one doesn't seem to work right and I am not sure what exactly is wrong. CurrentDb.Execute "UPDATE DISTINCTROW Screeners SET Screeners.LoginID = """ & Me![Text37] & """ AND Screeners.[First Name] = """ & Me![Text35] & """ And Screeners.[Last Name] = """ & Me![Text31] & """ And Screeners.[SSN] = """ & Me![Text46] & """ And Screeners.[Position] = """ & Me![Text44] & """ And Screeners.[Gender] = """ & Me![Text42] & """ Where ((Screeners.LoginID = """ & Me![Text52] & """));" What is basically is supposed to do is take the values in 6 text boxes and assign them to the values from the record in six seperate fields (LoginID, First Name, Last Name, SSN, Position, Gender), and it does this where the value of "LoginID" is equal to the value of a 7th text box. When I run the above code I get "Data Type Mismatch In Criteria Expression". Waht Exactly do I have wrong here, am an not sure what I need to change ? Any help would be appreciated . |
#3
|
|||
|
|||
Update Query
Thanks for the info, that worked great.
----- Gerald Stanley wrote: ----- You cannot use AND in the SET statement The correct syntax for updating multiple columns is SET column1 = 'value1', column2 = value2 (value1 is a text, value2 is a number) Hope This Helps Gerald Stanley MCSD -----Original Message----- I have used this similar construction to do update from text boxes to a table but this one doesn't seem to work right and I am not sure what exactly is wrong. CurrentDb.Execute "UPDATE DISTINCTROW Screeners SET Screeners.LoginID = """ & Me![Text37] & """ AND Screeners.[First Name] = """ & Me![Text35] & """ And Screeners.[Last Name] = """ & Me![Text31] & """ And Screeners.[SSN] = """ & Me![Text46] & """ And Screeners.[Position] = """ & Me![Text44] & """ And Screeners.[Gender] = """ & Me![Text42] & """ Where ((Screeners.LoginID = """ & Me![Text52] & """));" What is basically is supposed to do is take the values in 6 text boxes and assign them to the values from the record in six seperate fields (LoginID, First Name, Last Name, SSN, Position, Gender), and it does this where the value of "LoginID" is equal to the value of a 7th text box. When I run the above code I get "Data Type Mismatch In Criteria Expression". Waht Exactly do I have wrong here, am an not sure what I need to change ? Any help would be appreciated . |
Thread Tools | |
Display Modes | |
|
|