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
|
|||
|
|||
Newbie Question
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 |
#2
|
|||
|
|||
Newbie Question
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 |
#3
|
|||
|
|||
Newbie Question
Thanks alot.....great help
Ill get onto it now! Cheers Ben "strive4peace" wrote: 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 |
#4
|
|||
|
|||
Newbie Question
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 "strive4peace" wrote: 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 |
#5
|
|||
|
|||
Newbie Question
Crystal missed a quote and ampersand.
strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =#" & me.Date1 & "#;" She also assumed that you use the U.S. date format of mm/dd/yyyy. I would make one small modification to her SQL statement. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =" _ & Format(me.Date1,"\#yyyy-dd-mm\#") The last line above forces the date format into year month day format which is consistently recognized. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County bmacrow wrote: 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 "strive4peace" wrote: 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 |
#6
|
|||
|
|||
Newbie Question
Its completely working now!
Thanks so much everyone! Ben "John Spencer" wrote: Crystal missed a quote and ampersand. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =#" & me.Date1 & "#;" She also assumed that you use the U.S. date format of mm/dd/yyyy. I would make one small modification to her SQL statement. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =" _ & Format(me.Date1,"\#yyyy-dd-mm\#") The last line above forces the date format into year month day format which is consistently recognized. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County bmacrow wrote: 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 "strive4peace" wrote: 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 |
#7
|
|||
|
|||
Newbie Question
ok, i know im being a real pain now, but how would i modify that code
slightly so instead of a date i was looking for specific text, ie say i wanted to archive a specific contract number, with the contract number being recorded by the tables ID field. Sorry for being a pain. "John Spencer" wrote: Crystal missed a quote and ampersand. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =#" & me.Date1 & "#;" She also assumed that you use the U.S. date format of mm/dd/yyyy. I would make one small modification to her SQL statement. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =" _ & Format(me.Date1,"\#yyyy-dd-mm\#") The last line above forces the date format into year month day format which is consistently recognized. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County bmacrow wrote: 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 "strive4peace" wrote: 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 |
#8
|
|||
|
|||
Newbie Question
thank you, John!!! ... and good point about the date comparison
Warm Regards, Crystal * (: have an awesome day * John Spencer wrote: Crystal missed a quote and ampersand. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =#" & me.Date1 & "#;" She also assumed that you use the U.S. date format of mm/dd/yyyy. I would make one small modification to her SQL statement. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =" _ & Format(me.Date1,"\#yyyy-dd-mm\#") The last line above forces the date format into year month day format which is consistently recognized. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County bmacrow wrote: 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 "strive4peace" wrote: 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 |
#9
|
|||
|
|||
Newbie Question
Hi Ben,
no problem at all -- we are here to help smile "say i wanted to archive a specific contract number" In that case you would use the same code and just change the WHERE clause. & " WHERE [contractID_fieldname] = " & me.ContractID & ";" the last semi-colon is not really necessary in Access (so people often leave it off) -- it is the SQL termination character This is assuming you have a combobox on the form: Name -- ContractID RowSource -- SELECT ContractID, ClientName, ContractDate FROM Contracts INNER JOIN Clients ON Contracts.ClientID = Clients.ClientID ORDER BY ClientName, ContractDate desc ColumnCount -- 3 Columnwidths -- 0;1.5;1 ListWidth -- 2.7 ListRows -- 24 WHERE - Contracts is the name of the table with contract info - Clients is the name of the table with client info - Contracts has a primary key field called ContractID - Contracts has a foreign key field called ClientID - you have Client info in a table called Clients with primary key ClientID - ClientName is the name of your client field in Clients - ContractDate is the name of your date field in Contracts - the list will sort by ClientName and then show the most recent contracts first ~~~~~~~~~~~~~~~~ first, make another command button to run the code to select a ContractID. Once you get that working also, we will show you how to use just one command button and have the code test to see what is filled out. ~~ for more information on SQL, read the SQL section of this document: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal remote programming and training * (: have an awesome day * bmacrow wrote: ok, i know im being a real pain now, but how would i modify that code slightly so instead of a date i was looking for specific text, ie say i wanted to archive a specific contract number, with the contract number being recorded by the tables ID field. Sorry for being a pain. "John Spencer" wrote: Crystal missed a quote and ampersand. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =#" & me.Date1 & "#;" She also assumed that you use the U.S. date format of mm/dd/yyyy. I would make one small modification to her SQL statement. strSQL = "UPDATE [Tablename] " _ & " SET [archive_fieldname] = True " _ & " WHERE [date_fieldname] =" _ & Format(me.Date1,"\#yyyy-dd-mm\#") The last line above forces the date format into year month day format which is consistently recognized. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County bmacrow wrote: 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 "strive4peace" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|