SQL to Update Record - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL to Update Record

Jerry Fowler

4 years ago

Adam, this is an answer to a question I have been googling and not getting what I thought I wanted. If I should not ask here, let me know and I'll open a new question.

I have a Table called BillingT and when I send a statement I want to update the member's record with today's date.

For example I want to update [SecondNotice] with today's date
where [MemID] = the MemID from my open form AND
DuesYear = the current year.

Here is what I have been trying but getting a mis-matched data type

CurrentDb.Execute "UPDATE BillingT SET BillingT.SecondNotice = " & Date & " WHERE (((BillingT.MemID)="" & ""[MemID]"" & "") AND ((BillingT.DuesYear)=" & 2022 & "));"

the data types are

[SecondNotice] Date type
[MemID] Short Text
[DuesYear] Number

One other thing what I would like to do once I have this working is get the current year from todays date for the [DuesYear]

Thanks again

	Scott Axton @Reply 4 years ago
Jerry - I moved this to your own post instead of under the other one

Scott Axton @Reply

4 years ago

Jerry -
Is this a direct "lift" of the SQL from the query designer?  Just curious because of all the extra parentheses.

Simplify your expression as much as you can.  Because you are only working with a single table you can get rid of the BillingT. and the parens. You need to indicate to the SQL that Date is a date by using the # symbol.
MemID is really Short Text? - can there be letters there?

UPDATE BillingT SET SecondNotice = #" & Date & "# WHERE MemID=""" & [MemID] & """ AND DuesYear=2022;"

If I didn't goof this should work.  If it doesn't work, I've found the best way to get things to work is to hard code the SQL with a known value the swap out one at a time until you have it working with your variables.

BTW your DuesYear will always be 2022.  What happens in 2023 and beyond?  Is that what you really desire?
If the dues year is always the current year you would want to use the Year() function.

	Scott Axton @Reply 4 years ago
So you would want to use Year(Date()) You will learn a lot of the String and Date functions in Expert 11 Seriously consider looking in to the SQL Seminars Part 1 & Part 2 at least. Since you are a gold member you get a substantial discount.

	Scott Axton @Reply 4 years ago
Missed a quote in front of UPDATE. should be "UPDATE ...

	Jerry Fowler OP @Reply 4 years ago
Scott, yes this one is a direct port from a query. My other one was close to what you put in this answer (which yours work great, thanks) but I missed the second set double quotes around the [MemID]. You guys are the best.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View