By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Constant Date Parameter in VBA Code

P: n/a
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?
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ry***********@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
Jan 8 '08 #2

P: n/a
Perfect!

On Jan 8, 10:20*am, Salad <o...@vinegar.comwrote:
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] >= 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

Kornhttp://www.youtube.com/watch?v=k4X0JohCzsQ- Hide quoted text -

- Show quoted text -
Jan 8 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.