Constant Date Parameter in VBA Code

Hello'
I have a table with a column for each year.
I have a form that will be writing a date (from a cbo in a form) to
these columns.
I am trying to write a IF statement that will update these columns
depending on the year.
i.e. if the chosen date is in 2008, it will write to the 2008 column,
if 2009, the 2009 column...

Iv'e tried a couple different ways, but canot seem to get it to work.
The following code will write to the the first column, then try the
second but then gets halted as the qrydef get's duplicated or
something. Which leads me to beleive that the way I am writing the IF
parameter in the code is incorrect.

I want the code to run through these IF statements & write to the
appropriate column based on the year. The table's year columns are
formatted as 'Med Date', as is the cbobox these dates are coming
from.

(the strSQL line of code works fine on it's own..it's just the IF
statements I can't get to work)

If Forms![frmTraining_Selection]![cboCompDate] >= 1 / 1 / 7 <= 12 /
31 / 7 Then

strSQL = "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2007] =
Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
[Training Name]=Forms![frmTraining_Selection]![Training Name] AND
tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryCompDate"
Set db = Nothing
Set qdf = Nothing
End If

If Forms![frmTraining_Selection]![cboCompDate] >= 1 / 1 / 8 <= 12 /
31 / 8 Then

strSQL = "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2008] =
Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
[Training Name]=Forms![frmTraining_Selection]![Training Name] AND
tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryCompDate"
Set db = Nothing
Set qdf = Nothing
End If

FYI: I'm using Access 2000 SP2. Any Ideas?

Re: Constant Date Parameter in VBA Code

ryan.paquette@gmail.com wrote:

> Hello'
> I have a table with a column for each year.
> I have a form that will be writing a date (from a cbo in a form) to
> these columns.
> I am trying to write a IF statement that will update these columns
> depending on the year.
> i.e. if the chosen date is in 2008, it will write to the 2008 column,
> if 2009, the 2009 column...
>
> Iv'e tried a couple different ways, but canot seem to get it to work.
> The following code will write to the the first column, then try the
> second but then gets halted as the qrydef get's duplicated or
> something. Which leads me to beleive that the way I am writing the IF
> parameter in the code is incorrect.
>
> I want the code to run through these IF statements & write to the
> appropriate column based on the year. The table's year columns are
> formatted as 'Med Date', as is the cbobox these dates are coming
> from.
>
> (the strSQL line of code works fine on it's own..it's just the IF
> statements I can't get to work)
>
> If Forms![frmTraining_Selection]![cboCompDate] >= 1 / 1 / 7 <= 12 /
> 31 / 7 Then
>
> strSQL = "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2007] =
> Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
> [Training Name]=Forms![frmTraining_Selection]![Training Name] AND
> tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
> qdf.SQL = strSQL
> DoCmd.OpenQuery "qryCompDate"
> Set db = Nothing
> Set qdf = Nothing
> End If
>
> If Forms![frmTraining_Selection]![cboCompDate] >= 1 / 1 / 8 <= 12 /
> 31 / 8 Then
>
> strSQL = "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2008] =
> Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
> [Training Name]=Forms![frmTraining_Selection]![Training Name] AND
> tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
> qdf.SQL = strSQL
> DoCmd.OpenQuery "qryCompDate"
> Set db = Nothing
> Set qdf = Nothing
> End If
>
> FYI: I'm using Access 2000 SP2. Any Ideas?

Dim datComp As Date
datComp = Cdate(Forms![frmTraining_Selection]![cboCompDate])
If datComp >= #1/1/07# And datComp <= #12/31/07# Then

Korn
http://www.youtube.com/watch?v=k4X0JohCzsQ

Re: Constant Date Parameter in VBA Code

Perfect!

On Jan 8, 10:20=A0am, Salad wrote:
> ryan.paque...@gmail.com wrote:
> > Hello'
> > I have a table with a column for each year.
> > I have a form that will be writing a date (from a cbo in a form) to
> > these columns.
> > I am trying to write a IF statement that will update these columns
> > depending on the year.
> > i.e. if the chosen date is in 2008, it will write to the 2008 column,
> > if 2009, the 2009 column...
>
> > Iv'e tried a couple different ways, but canot seem to get it to work.
> > The following code will write to the the first column, then try the
> > second but then gets halted as the qrydef get's duplicated or
> > something. Which leads me to beleive that the way I am writing the IF
> > parameter in the code is incorrect.
>
> > I want the code to run through these IF statements & write to the
> > appropriate column based on the year. The table's year columns are
> > formatted as 'Med Date', as is the cbobox these dates are coming
> > from.
>
> > (the strSQL line of code works fine on it's own..it's just the IF
> > statements I can't get to work)
>
> > If Forms![frmTraining_Selection]![cboCompDate] >=3D 1 / 1 / 7 <=3D 12 /
> > 31 / 7 Then
>
> > =A0 =A0strSQL =3D "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2007]=
=3D
> > Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
> > [Training Name]=3DForms![frmTraining_Selection]![Training Name] AND
> > tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
> > =A0 =A0qdf.SQL =3D strSQL
> > =A0 =A0DoCmd.OpenQuery "qryCompDate"
> > =A0 =A0Set db =3D Nothing
> > =A0 =A0Set qdf =3D Nothing
> > =A0 =A0End If
>
> > If Forms![frmTraining_Selection]![cboCompDate] >=3D 1 / 1 / 8 <=3D 12 /
> > 31 / 8 Then
>
> > =A0 =A0strSQL =3D "UPDATE tblNext_and_Dates SET tblNext_and_Dates.[2008]=
=3D
> > Forms![frmTraining_Selection]![cboCompDate] Where [tblNext_and_Dates].
> > [Training Name]=3DForms![frmTraining_Selection]![Training Name] AND
> > tblNext_and_Dates.[Employee Name] IN(" & strCriteria & ");"
> > =A0 =A0qdf.SQL =3D strSQL
> > =A0 =A0DoCmd.OpenQuery "qryCompDate"
> > =A0 =A0Set db =3D Nothing
> > =A0 =A0Set qdf =3D Nothing
> > =A0 =A0End If
>
> > FYI: I'm using Access 2000 SP2. Any Ideas?
>
> Dim datComp As Date
> datComp =3D Cdate(Forms![frmTraining_Selection]![cboCompDate])
> If datComp >=3D #1/1/07# And datComp <=3D #12/31/07# Then
>
> Kornhttp://www.youtube.com/watch?v=3Dk4X0JohCzsQ- Hide quoted text -
>
> - Show quoted text -