Hello there!
Ok, so im getting this all great, but when i paste the code in i get a error
Stating "Compile error: Expected: end of statement"
access highlights the following inred
strSQL = "UPDATE [Tablename] " _
& " SET [archive_fieldname] = True " _
& " WHERE [date_fieldname] <=# & me.Date1 & "#;"
and then selects the # on the final line
am i doing something wrong?
Cheers
Ben
:
set Archive flag
~~~
Hi Ben,
make a form to collect the Date from the user
for example: form name --> f_menu_update
textbox:
Name --> Date1
command button:
Name --> cmdUpdateArchive
Caption --> Archive Contracts
On Click --> [Event Procedure]
'~~~~~~~~~~~~~~
if not IsDate(me.Date1) then
msgbox "You have not entered a date",,"Cannot archive records"
me.Date1.SetFocus
exit sub
end if
dim strSQL as string
strSQL = "UPDATE [Tablename] " _
& " SET [archive_fieldname] = True " _
& " WHERE [date_fieldname] <=# & me.Date1 & "#;"
debug.print strSQL
currentdb.execute strSQL
msgbox "Done updating Archive flag",,"Done"
'~~~~~~~~~~~~~
WHERE
Tablename is the name of your table
archive_fieldname is the name of the field for the archive flag
date_fieldname is the name of the field that has the date you want to
compare
** debug.print ***
debug.print strSQL
--> this prints a copy of the SQL statement to the debug window (CTRL-G)
After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement
If the SQL statement has an error
1. Make a new query (design view)
2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window
(select, CTRL-X)
4. paste into the SQL window of the Query
(CTRL-V)
5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL
'~~~~~~~~~ Compile ~~~~~~~~~
Whenever you change code, references, or switch versions, you should
always compile before executing.
from the menu in a VBE (module) window: Debug, Compile
fix any errors on the yellow highlighted lines
keep compiling until nothing happens (this is good!)
~~
if you run code without compiling it, you risk corrupting your database
~~~~~~~~~~~~~~~
you can design a query that asks for the date too -- but, IMO, it is
better to make a form and run code to do it. You will probably have
other things you can use this same technique for -- and you can also use
the same form <smile>
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
bmacrow wrote:
Hello all,
I am fairly new to this database creation thing, having been thrown in the
deep end by work. My question is I am currently trying to get the database to
archive data with a contract end date previous to a date entered by the user.
To achive this in my contracts table I have added an archive feild that is a
YES/NO checkbox that is set to no by default. All my forms reference queries
that will filter out data that is or isnt checked in this box.
Ok so thats all good, what i now need to do is create a query that asks the
user for a date, and would then set the archive checkbox to TRUE for all
records with a contract end date on or before the date entered. Is this
possible/practical?
Thanks in Advance
Ben