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

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

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

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

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

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

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

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

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