If Then Else statement

[Guest]

Hi, I'm trying to write an 'If..then...else' statement (On Click event) to
look at the date in the last record in a table and compare it to a date keyed
into a text box on a form. If they are equal, show a message box. If they
are not equal, I want to use DateAdd and loop it for seven days. The table
is "Week of Table" and the field name with the date is "Week Ended" I was
trying 'DoCmd.GoToRecord...' and then starting the 'If...Then...Else", but I
couldn't get it to work. I kept getting an error message saying that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

Not sure I'm following what you want to do here? I assume that the form is
based on the table Week of Table, so perhaps this will get you started:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
If Me.TextBoxName.Value = rst.Fields("Week Ended").Value Then
MsgBox "They're equal."
Else
MsgBox DateAdd("d", 7, Me.TextBoxName.Value) & " is 7 days from the
entered date."
End If
Set rst = Nothing


Or if you are looking up the value from the table, because the form is not
based on the table, and assuming that "last record" would mean the most
recently entered date in the table:

If Me.TextBoxName.Value = DMax("Week Ended", "Week of Table") Then
MsgBox "They're equal."
Else
MsgBox DateAdd("d", 7, Me.TextBoxName.Value) & " is 7 days from the
entered date."
End If

 

[Guest]

Thanks Ken. This got me a little further, but still an error message. Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week Ended)'."

The 'else' piece will be: add one day to the latest date and update all
fields in the table, loop for 7 days. In other words, the latest date in the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate will be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05, 4/6/05, ...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other postings,
and I appreciate your help!

 

[Ken Snell [MVP]]

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put [ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the inclusion
of the [ ] characters in this situation. But here, DMax (and probably DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. This got me a little further, but still an error message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week Ended)'."

The 'else' piece will be: add one day to the latest date and update all
fields in the table, loop for 7 days. In other words, the latest date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05, 4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other postings,
and I appreciate your help!

Hi, I'm trying to write an 'If..then...else' statement (On Click event)
to
look at the date in the last record in a table and compare it to a date
keyed
into a text box on a form. If they are equal, show a message box. If
they
are not equal, I want to use DateAdd and loop it for seven days. The
table
is "Week of Table" and the field name with the date is "Week Ended" I
was
trying 'DoCmd.GoToRecord...' and then starting the 'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Guest]

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else' piece
today, may have to wait until Monday, but I know where to go if I need help!!
Have a great weekend!

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put [ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the inclusion
of the [ ] characters in this situation. But here, DMax (and probably DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. This got me a little further, but still an error message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week Ended)'."

The 'else' piece will be: add one day to the latest date and update all
fields in the table, loop for 7 days. In other words, the latest date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05, 4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other postings,
and I appreciate your help!

Hi, I'm trying to write an 'If..then...else' statement (On Click event)
to
look at the date in the last record in a table and compare it to a date
keyed
into a text box on a form. If they are equal, show a message box. If
they
are not equal, I want to use DateAdd and loop it for seven days. The
table
is "Week of Table" and the field name with the date is "Week Ended" I
was
trying 'DoCmd.GoToRecord...' and then starting the 'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Guest]

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the UPDATE
line, and the cursor goes right to the "d". I've tried adding more (), [],
"", ect, and I don't know how to make this error message go away.

Also, do I actually have to build a query to perform the update or does this
statement build the query?

And, since I feel really dumb when I can't figure something out, is there
available a good book / cd / reference guide to help us 'newbies' learn how
to write simple code?

Thanks again!

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else' piece
today, may have to wait until Monday, but I know where to go if I need help!!
Have a great weekend!

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put [ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the inclusion
of the [ ] characters in this situation. But here, DMax (and probably DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. This got me a little further, but still an error message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week Ended)'."

The 'else' piece will be: add one day to the latest date and update all
fields in the table, loop for 7 days. In other words, the latest date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05, 4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click event)
to
look at the date in the last record in a table and compare it to a date
keyed
into a text box on a form. If they are equal, show a message box. If
they
are not equal, I want to use DateAdd and loop it for seven days. The
table
is "Week of Table" and the field name with the date is "Week Ended" I
was
trying 'DoCmd.GoToRecord...' and then starting the 'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

My typo -- I forgot to double-up the " characters because you're wanting to
have a single " character within the string:

strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd(""d"", 7, [Week
of Table] )"

--

Ken Snell
<MS ACCESS MVP>

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the UPDATE
line, and the cursor goes right to the "d". I've tried adding more (),
[],
"", ect, and I don't know how to make this error message go away.

Also, do I actually have to build a query to perform the update or does
this
statement build the query?

And, since I feel really dumb when I can't figure something out, is there
available a good book / cd / reference guide to help us 'newbies' learn
how
to write simple code?

Thanks again!

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else' piece
today, may have to wait until Monday, but I know where to go if I need
help!!
Have a great weekend!

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put
[ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of
Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the
inclusion
of the [ ] characters in this situation. But here, DMax (and probably
DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to
run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken. This got me a little further, but still an error
message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of
Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I
am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week
Ended)'."

The 'else' piece will be: add one day to the latest date and update
all
fields in the table, loop for 7 days. In other words, the latest
date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate
will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05,
4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other
postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click
event)
to
look at the date in the last record in a table and compare it to a
date
keyed
into a text box on a form. If they are equal, show a message box.
If
they
are not equal, I want to use DateAdd and loop it for seven days.
The
table
is "Week of Table" and the field name with the date is "Week Ended"
I
was
trying 'DoCmd.GoToRecord...' and then starting the
'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying
that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

Answers to the other questions inline....

--

Ken Snell
<MS ACCESS MVP>

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the UPDATE
line, and the cursor goes right to the "d". I've tried adding more (),
[],
"", ect, and I don't know how to make this error message go away.

(see answer in other post)

Also, do I actually have to build a query to perform the update or does
this
statement build the query?

The strSQL statement builds the query, and the dbs.Execute line runs the
query.

And, since I feel really dumb when I can't figure something out, is there
available a good book / cd / reference guide to help us 'newbies' learn
how
to write simple code?

See www.viescas.com for various book ideas. John Viescas has written good
books on ACCESS 2003 and on building ACCESS applications.

Thanks again!

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else' piece
today, may have to wait until Monday, but I know where to go if I need
help!!
Have a great weekend!

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put
[ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of
Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the
inclusion
of the [ ] characters in this situation. But here, DMax (and probably
DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to
run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken. This got me a little further, but still an error
message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of
Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I
am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week
Ended)'."

The 'else' piece will be: add one day to the latest date and update
all
fields in the table, loop for 7 days. In other words, the latest
date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate
will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05,
4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other
postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click
event)
to
look at the date in the last record in a table and compare it to a
date
keyed
into a text box on a form. If they are equal, show a message box.
If
they
are not equal, I want to use DateAdd and loop it for seven days.
The
table
is "Week of Table" and the field name with the date is "Week Ended"
I
was
trying 'DoCmd.GoToRecord...' and then starting the
'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying
that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Guest]

Ok....the extra "" made the error message go away....now I have another one
This one says "Two few parameters. Expected 1."
Also, there are other fields in this table to be updated...Policy Effective
Date and Quarter....Will they update if I add two more statements like strSQL
= UPDATE....SET [Policy Effective Date]..... and strSQL UPDATE....SET
[Quarter]....?

And thanks for the other info.....there are some coworkers that have also
been looking for some good publications, I will share with them.

Answers to the other questions inline....

--

Ken Snell
<MS ACCESS MVP>

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the UPDATE
line, and the cursor goes right to the "d". I've tried adding more (),
[],
"", ect, and I don't know how to make this error message go away.

(see answer in other post)

Also, do I actually have to build a query to perform the update or does
this
statement build the query?

The strSQL statement builds the query, and the dbs.Execute line runs the
query.

And, since I feel really dumb when I can't figure something out, is there
available a good book / cd / reference guide to help us 'newbies' learn
how
to write simple code?

See www.viescas.com for various book ideas. John Viescas has written good
books on ACCESS 2003 and on building ACCESS applications.

Thanks again!

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else' piece
today, may have to wait until Monday, but I know where to go if I need
help!!
Have a great weekend!

:

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to put
[ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of
Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the
inclusion
of the [ ] characters in this situation. But here, DMax (and probably
DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want to
run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d", 7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken. This got me a little further, but still an error
message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of
Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error message I
am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week
Ended)'."

The 'else' piece will be: add one day to the latest date and update
all
fields in the table, loop for 7 days. In other words, the latest
date in
the
table currently is 4/3/05. The date value in bxCurrentWeekEndedDate
will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05,
4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other
postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click
event)
to
look at the date in the last record in a table and compare it to a
date
keyed
into a text box on a form. If they are equal, show a message box.
If
they
are not equal, I want to use DateAdd and loop it for seven days.
The
table
is "Week of Table" and the field name with the date is "Week Ended"
I
was
trying 'DoCmd.GoToRecord...' and then starting the
'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying
that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

Sorry...Bad copy/paste on my part:

strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd(""d"", 7, [Week
Ended] )"
--

Ken Snell
<MS ACCESS MVP>

Ok....the extra "" made the error message go away....now I have another
one
This one says "Two few parameters. Expected 1."
Also, there are other fields in this table to be updated...Policy
Effective
Date and Quarter....Will they update if I add two more statements like
strSQL
= UPDATE....SET [Policy Effective Date]..... and strSQL UPDATE....SET
[Quarter]....?

And thanks for the other info.....there are some coworkers that have also
been looking for some good publications, I will share with them.

Answers to the other questions inline....

--

Ken Snell
<MS ACCESS MVP>

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the
UPDATE
line, and the cursor goes right to the "d". I've tried adding more (),
[],
"", ect, and I don't know how to make this error message go away.

(see answer in other post)

Also, do I actually have to build a query to perform the update or does
this
statement build the query?

The strSQL statement builds the query, and the dbs.Execute line runs the
query.

And, since I feel really dumb when I can't figure something out, is
there
available a good book / cd / reference guide to help us 'newbies' learn
how
to write simple code?

See www.viescas.com for various book ideas. John Viescas has written good
books on ACCESS 2003 and on building ACCESS applications.

Thanks again!

:

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else'
piece
today, may have to wait until Monday, but I know where to go if I need
help!!
Have a great weekend!

:

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to
put
[ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of
Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the
inclusion
of the [ ] characters in this situation. But here, DMax (and
probably
DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want
to
run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d",
7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken. This got me a little further, but still an error
message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of
Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error
message I
am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week
Ended)'."

The 'else' piece will be: add one day to the latest date and
update
all
fields in the table, loop for 7 days. In other words, the latest
date in
the
table currently is 4/3/05. The date value in
bxCurrentWeekEndedDate
will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05,
4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other
postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click
event)
to
look at the date in the last record in a table and compare it to
a
date
keyed
into a text box on a form. If they are equal, show a message
box.
If
they
are not equal, I want to use DateAdd and loop it for seven days.
The
table
is "Week of Table" and the field name with the date is "Week
Ended"
I
was
trying 'DoCmd.GoToRecord...' and then starting the
'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying
that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

You may be able to use a single SQL statement to update each of the fields
that you want to update if there is just the one record in the table.
Something like this:

strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd(""d"", 7, [Week
Ended]), [Policy Effective Date] = YourValue, [Quarter] = AValue"

and so on. Otherwise, you'll need to create an SQL statement for one field,
run that query, then create an SQL statement for a different field, run that
query, etc.

--

Ken Snell
<MS ACCESS MVP>

Ok....the extra "" made the error message go away....now I have another
one
This one says "Two few parameters. Expected 1."
Also, there are other fields in this table to be updated...Policy
Effective
Date and Quarter....Will they update if I add two more statements like
strSQL
= UPDATE....SET [Policy Effective Date]..... and strSQL UPDATE....SET
[Quarter]....?

And thanks for the other info.....there are some coworkers that have also
been looking for some good publications, I will share with them.

Answers to the other questions inline....

--

Ken Snell
<MS ACCESS MVP>

I'm baaaccckkkk!!!!

I'm getting the error message "Expected: End of Statement" on the
UPDATE
line, and the cursor goes right to the "d". I've tried adding more (),
[],
"", ect, and I don't know how to make this error message go away.

(see answer in other post)

Also, do I actually have to build a query to perform the update or does
this
statement build the query?

The strSQL statement builds the query, and the dbs.Execute line runs the
query.

And, since I feel really dumb when I can't figure something out, is
there
available a good book / cd / reference guide to help us 'newbies' learn
how
to write simple code?

See www.viescas.com for various book ideas. John Viescas has written good
books on ACCESS 2003 and on building ACCESS applications.

Thanks again!

:

That means you can go home for the day!!!

This worked! Thanks. I don't know if I'll get to try the 'else'
piece
today, may have to wait until Monday, but I know where to go if I need
help!!
Have a great weekend!

:

Huh... well, I learn something new every day....

Because the "Week Ended" field name has a space in it, you need to
put
[ ]
around the field name in the first argument:

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]", "Week of
Table")
Then


Other domain functions (DLookup, DCount, etc.) don't require the
inclusion
of the [ ] characters in this situation. But here, DMax (and
probably
DMin,
DAvg, etc.) does.


Regarding the "loop for 7 days" issue, it sounds as if you'll want
to
run an
Update query that updates the data in the table.

If Me.TextBoxName.Value = DMax("[Week Ended]", "Week of Table") Then
MsgBox "They're equal."
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
' build the query to update the table
strSQL = "UPDATE [Week of Table] SET [Week Ended] = DateAdd("d",
7,
[Week of Table] )"
' run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nohting
End If

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken. This got me a little further, but still an error
message.
Here
is the code (I copied and pasted from your reply):
If Me![bxCurrentWeekEndedDate].Value = DMax("Week Ended", "Week of
Table")
Then
MsgBox "Proceed to calculations!", vbOKOnly + vbExclamation
I haven't even started on the 'else' piece yet....the error
message I
am
getting is:
"Syntax error(missing operator) in query expression 'Max(Week
Ended)'."

The 'else' piece will be: add one day to the latest date and
update
all
fields in the table, loop for 7 days. In other words, the latest
date in
the
table currently is 4/3/05. The date value in
bxCurrentWeekEndedDate
will
be
4/10/05. At this point the loop begins, adding 4/4/05, 4/5/05,
4/6/05,
...
thru 4/10/05. And maybe end with a msgbox saying "Update complete"

I am very new to VBA, but I am learning a lot from reading other
postings,
and I appreciate your help!

:

Hi, I'm trying to write an 'If..then...else' statement (On Click
event)
to
look at the date in the last record in a table and compare it to
a
date
keyed
into a text box on a form. If they are equal, show a message
box.
If
they
are not equal, I want to use DateAdd and loop it for seven days.
The
table
is "Week of Table" and the field name with the date is "Week
Ended"
I
was
trying 'DoCmd.GoToRecord...' and then starting the
'If...Then...Else",
but I
couldn't get it to work. I kept getting an error message saying
that the
field name didn't exist, or was spelled wrong, etc.

Thanks in advance!!

 

[Ken Snell [MVP]]

You're welcome.

 

[Douglas J. Steele]

strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ), [Week Ended] = " &
Format$([bxCurrentWeekEndedDate], "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Ken,
Sorry to be a pain.....
Because of the fields and data types in the table I was trying to update,
I
couldn't get this to work, it ran, but in such a way that it corrupted my
data . I have redesigned the table to have two fields:
PolicyEffectiveDate (as a date/time type) and Week Ended (also as a
date/time
type) and I have removed Quarter and have it calculating somewhere else.

My new code looks like this:
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
'''build the query to update the table
strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ),[Week Ended] =
[bxCurrentWeekEndedDate]"
'''run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End If

Currently, if bxCurrentWeekEndedDate = [Week Ended} my message box pops
up.
If bxCurrentWeekEndedDate = 04/17/2005, I get the error message "Too few
parameters. Expected 1." again. My assumption here is that if
bxCurrentWeekEndedDate = 04/17/2005, then the PolicyEffectiveDate should
update to add the next day, and Week Ended update to be 04/17/2005. Then
I
guess my loop for 7 days would be something like "when PolicyEffectiveDate
=
Week Ended, stop the loop?
Thanks!

You're welcome.

 

[Ken Snell [MVP]]

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement ("INSERT
INTO ...") for inserting a single record and then loop through that part of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the "new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Hi Doug,
Thanks for your help here....I tried this, and it ran correctly. The
oldest
date in PolicyEffectiveDate was removed and a new date was added, plus it
changed all the dates in Week Ended to 4/17/05. I'm sorry if I didn't
articulate clear enough, chalk it it up to inexperience... I don't want
to
change or delete anything, I just want to add 7 new records if the date in
bxCurrentWeekEndedDate doesn't match the latest Week Ended date. For
example, if the latest week ended date is 04/10/2005, then I want to add
04/11/2005, 04/12/2005, 04/13/2005, 04/14/2005, 04/15/2005, 04/16/2005,
04/17/2005 and have the Week Ended field for these new dates be
04/17/2005.
Thanks for your patience!

strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ), [Week Ended] = " &
Format$([bxCurrentWeekEndedDate], "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Ken,
Sorry to be a pain.....
Because of the fields and data types in the table I was trying to
update,
I
couldn't get this to work, it ran, but in such a way that it corrupted
my
data . I have redesigned the table to have two fields:
PolicyEffectiveDate (as a date/time type) and Week Ended (also as a
date/time
type) and I have removed Quarter and have it calculating somewhere
else.

My new code looks like this:
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
'''build the query to update the table
strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ),[Week Ended] =
[bxCurrentWeekEndedDate]"
'''run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End If

Currently, if bxCurrentWeekEndedDate = [Week Ended} my message box pops
up.
If bxCurrentWeekEndedDate = 04/17/2005, I get the error message "Too
few
parameters. Expected 1." again. My assumption here is that if
bxCurrentWeekEndedDate = 04/17/2005, then the PolicyEffectiveDate
should
update to add the next day, and Week Ended update to be 04/17/2005.
Then
I
guess my loop for 7 days would be something like "when
PolicyEffectiveDate
=
Week Ended, stop the loop?
Thanks!


:

You're welcome.

message
Thank you! You have been most helpful!

 

[Guest]

My appologies. VBA is definetly a foreign language to me, but you all have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for figuring that
one out!). After clicking the button, 7 new records were added as follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by one each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of the 7
records. Why did it give time and not date, since I Dimed As Date?

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement ("INSERT
INTO ...") for inserting a single record and then loop through that part of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the "new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Hi Doug,
Thanks for your help here....I tried this, and it ran correctly. The
oldest
date in PolicyEffectiveDate was removed and a new date was added, plus it
changed all the dates in Week Ended to 4/17/05. I'm sorry if I didn't
articulate clear enough, chalk it it up to inexperience... I don't want
to
change or delete anything, I just want to add 7 new records if the date in
bxCurrentWeekEndedDate doesn't match the latest Week Ended date. For
example, if the latest week ended date is 04/10/2005, then I want to add
04/11/2005, 04/12/2005, 04/13/2005, 04/14/2005, 04/15/2005, 04/16/2005,
04/17/2005 and have the Week Ended field for these new dates be
04/17/2005.
Thanks for your patience!

strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ), [Week Ended] = " &
Format$([bxCurrentWeekEndedDate], "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi Ken,
Sorry to be a pain.....
Because of the fields and data types in the table I was trying to
update,
I
couldn't get this to work, it ran, but in such a way that it corrupted
my
data . I have redesigned the table to have two fields:
PolicyEffectiveDate (as a date/time type) and Week Ended (also as a
date/time
type) and I have removed Quarter and have it calculating somewhere
else.

My new code looks like this:
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
'''build the query to update the table
strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ),[Week Ended] =
[bxCurrentWeekEndedDate]"
'''run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End If

Currently, if bxCurrentWeekEndedDate = [Week Ended} my message box pops
up.
If bxCurrentWeekEndedDate = 04/17/2005, I get the error message "Too
few
parameters. Expected 1." again. My assumption here is that if
bxCurrentWeekEndedDate = 04/17/2005, then the PolicyEffectiveDate
should
update to add the next day, and Week Ended update to be 04/17/2005.
Then
I
guess my loop for 7 days would be something like "when
PolicyEffectiveDate
=
Week Ended, stop the loop?
Thanks!


:

You're welcome.

message
Thank you! You have been most helpful!

 

[Ken Snell [MVP]]

Declaring a variable as Date means it will hold date and time data. Dates
and times are stored as a floating point number, where the "integer" portion
is the number of days since December 30, 1899, and the "decimal" portion is
the percentage of a 24-hour day represented by the time.

Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this number
is what is actually stored in the variable.

Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a negative
number as the second argument (note my use of -lngLoop there). From where or
how does bxCurrentWeekEndedDate get its value? What was the value that it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate is a
textbox, what is the Format property for it in the form's design view?

Also, what is the format setting for the two fields in the table's design
view?

--

Ken Snell
<MS ACCESS MVP>

My appologies. VBA is definetly a foreign language to me, but you all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by one each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of the 7
records. Why did it give time and not date, since I Dimed As Date?

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that part
of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the "new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Hi Doug,
Thanks for your help here....I tried this, and it ran correctly. The
oldest
date in PolicyEffectiveDate was removed and a new date was added, plus
it
changed all the dates in Week Ended to 4/17/05. I'm sorry if I didn't
articulate clear enough, chalk it it up to inexperience... I don't
want
to
change or delete anything, I just want to add 7 new records if the date
in
bxCurrentWeekEndedDate doesn't match the latest Week Ended date. For
example, if the latest week ended date is 04/10/2005, then I want to
add
04/11/2005, 04/12/2005, 04/13/2005, 04/14/2005, 04/15/2005, 04/16/2005,
04/17/2005 and have the Week Ended field for these new dates be
04/17/2005.
Thanks for your patience!

:

strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ), [Week Ended] = " &
Format$([bxCurrentWeekEndedDate], "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Ken,
Sorry to be a pain.....
Because of the fields and data types in the table I was trying to
update,
I
couldn't get this to work, it ran, but in such a way that it
corrupted
my
data . I have redesigned the table to have two fields:
PolicyEffectiveDate (as a date/time type) and Week Ended (also as a
date/time
type) and I have removed Quarter and have it calculating somewhere
else.

My new code looks like this:
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
'''build the query to update the table
strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ),[Week Ended] =
[bxCurrentWeekEndedDate]"
'''run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End If

Currently, if bxCurrentWeekEndedDate = [Week Ended} my message box
pops
up.
If bxCurrentWeekEndedDate = 04/17/2005, I get the error message
"Too
few
parameters. Expected 1." again. My assumption here is that if
bxCurrentWeekEndedDate = 04/17/2005, then the PolicyEffectiveDate
should
update to add the next day, and Week Ended update to be 04/17/2005.
Then
I
guess my loop for 7 days would be something like "when
PolicyEffectiveDate
=
Week Ended, stop the loop?
Thanks!


:

You're welcome.

message
Thank you! You have been most helpful!

 

[Guest]

Hi Ken,
bxCurrentWeekEndedDate gets its value when a date is typed into the box,
which is formatted as Short Date on the text box properties. The value
entered in that box prior to running the code was 04/17/2005 (which, my
desired results are to add 04/11/2005, 04/12/2005.....04/17/2005 -- 7 new
records, since the latest date in the PolicyEffectiveDate field is
04/10/2005). The fields PolicyEffectiveDate and Week Ended are both
formatted as date/time in the data type.

Declaring a variable as Date means it will hold date and time data. Dates
and times are stored as a floating point number, where the "integer" portion
is the number of days since December 30, 1899, and the "decimal" portion is
the percentage of a 24-hour day represented by the time.

Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this number
is what is actually stored in the variable.

Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a negative
number as the second argument (note my use of -lngLoop there). From where or
how does bxCurrentWeekEndedDate get its value? What was the value that it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate is a
textbox, what is the Format property for it in the form's design view?

Also, what is the format setting for the two fields in the table's design
view?

--

Ken Snell
<MS ACCESS MVP>

My appologies. VBA is definetly a foreign language to me, but you all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by one each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of the 7
records. Why did it give time and not date, since I Dimed As Date?

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that part
of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the "new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Hi Doug,
Thanks for your help here....I tried this, and it ran correctly. The
oldest
date in PolicyEffectiveDate was removed and a new date was added, plus
it
changed all the dates in Week Ended to 4/17/05. I'm sorry if I didn't
articulate clear enough, chalk it it up to inexperience... I don't
want
to
change or delete anything, I just want to add 7 new records if the date
in
bxCurrentWeekEndedDate doesn't match the latest Week Ended date. For
example, if the latest week ended date is 04/10/2005, then I want to
add
04/11/2005, 04/12/2005, 04/13/2005, 04/14/2005, 04/15/2005, 04/16/2005,
04/17/2005 and have the Week Ended field for these new dates be
04/17/2005.
Thanks for your patience!

:

strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ), [Week Ended] = " &
Format$([bxCurrentWeekEndedDate], "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Ken,
Sorry to be a pain.....
Because of the fields and data types in the table I was trying to
update,
I
couldn't get this to work, it ran, but in such a way that it
corrupted
my
data . I have redesigned the table to have two fields:
PolicyEffectiveDate (as a date/time type) and Week Ended (also as a
date/time
type) and I have removed Quarter and have it calculating somewhere
else.

My new code looks like this:
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
'''build the query to update the table
strSQL = "UPDATE [tblWeekOfTable] SET [PolicyEffectiveDate] =
DateAdd(""d"", 1, [PolicyEffectiveDate] ),[Week Ended] =
[bxCurrentWeekEndedDate]"
'''run the query that updates the table
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

End If

Currently, if bxCurrentWeekEndedDate = [Week Ended} my message box
pops
up.
If bxCurrentWeekEndedDate = 04/17/2005, I get the error message
"Too
few
parameters. Expected 1." again. My assumption here is that if
bxCurrentWeekEndedDate = 04/17/2005, then the PolicyEffectiveDate
should
update to add the next day, and Week Ended update to be 04/17/2005.
Then
I
guess my loop for 7 days would be something like "when
PolicyEffectiveDate
=
Week Ended, stop the loop?
Thanks!


:

You're welcome.

message
Thank you! You have been most helpful!

 

[Ken Snell [MVP]]

Nothing appears to be incorrect in the code nor what you have for
settings/formats. So I am at a loss for how to explain what you are seeing
as the resulting records' values for the inserted records after the code
runs.

Can you post the entire procedure that you used (starting with "Private Sub
...." through the "End Sub" lines)?
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
bxCurrentWeekEndedDate gets its value when a date is typed into the box,
which is formatted as Short Date on the text box properties. The value
entered in that box prior to running the code was 04/17/2005 (which, my
desired results are to add 04/11/2005, 04/12/2005.....04/17/2005 -- 7 new
records, since the latest date in the PolicyEffectiveDate field is
04/10/2005). The fields PolicyEffectiveDate and Week Ended are both
formatted as date/time in the data type.

Declaring a variable as Date means it will hold date and time data. Dates
and times are stored as a floating point number, where the "integer"
portion
is the number of days since December 30, 1899, and the "decimal" portion
is
the percentage of a 24-hour day represented by the time.

Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this
number
is what is actually stored in the variable.

Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a negative
number as the second argument (note my use of -lngLoop there). From where
or
how does bxCurrentWeekEndedDate get its value? What was the value that it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate is
a
textbox, what is the Format property for it in the form's design view?

Also, what is the format setting for the two fields in the table's design
view?

--

Ken Snell
<MS ACCESS MVP>

My appologies. VBA is definetly a foreign language to me, but you all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by one
each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of the 7
records. Why did it give time and not date, since I Dimed As Date?

:

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that
part
of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the
"new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

 

[Guest]

Good morning! Here is the code....
Private Sub btnUpdateWeekOfTable_Click()
On Error GoTo Err_btnUpdateWeekOfTable_Click

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Dim datWeekEnded As Date
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & "VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing
MsgBox "Update complete!", vbOKOnly + vbExclamation
End If

Exit_btnUpdateWeekOfTable_Click:
Exit Sub

Err_btnUpdateWeekOfTable_Click:
MsgBox Err.Description
Resume Exit_btnUpdateWeekOfTable_Click

End Sub


Thanks, again!

Nothing appears to be incorrect in the code nor what you have for
settings/formats. So I am at a loss for how to explain what you are seeing
as the resulting records' values for the inserted records after the code
runs.

Can you post the entire procedure that you used (starting with "Private Sub
...." through the "End Sub" lines)?
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
bxCurrentWeekEndedDate gets its value when a date is typed into the box,
which is formatted as Short Date on the text box properties. The value
entered in that box prior to running the code was 04/17/2005 (which, my
desired results are to add 04/11/2005, 04/12/2005.....04/17/2005 -- 7 new
records, since the latest date in the PolicyEffectiveDate field is
04/10/2005). The fields PolicyEffectiveDate and Week Ended are both
formatted as date/time in the data type.

Declaring a variable as Date means it will hold date and time data. Dates
and times are stored as a floating point number, where the "integer"
portion
is the number of days since December 30, 1899, and the "decimal" portion
is
the percentage of a 24-hour day represented by the time.

Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this
number
is what is actually stored in the variable.

Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a negative
number as the second argument (note my use of -lngLoop there). From where
or
how does bxCurrentWeekEndedDate get its value? What was the value that it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate is
a
textbox, what is the Format property for it in the form's design view?

Also, what is the format setting for the two fields in the table's design
view?

--

Ken Snell
<MS ACCESS MVP>


My appologies. VBA is definetly a foreign language to me, but you all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by one
each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of the 7
records. Why did it give time and not date, since I Dimed As Date?

:

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that
part
of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the
"new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing

 

[Ken Snell [MVP]]

I have been tied up on some things and haven't had chance to prepare a
reply, but I will....

--

Ken Snell
<MS ACCESS MVP>

Good morning! Here is the code....
Private Sub btnUpdateWeekOfTable_Click()
On Error GoTo Err_btnUpdateWeekOfTable_Click

If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Dim datWeekEnded As Date
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & "VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing
MsgBox "Update complete!", vbOKOnly + vbExclamation
End If

Exit_btnUpdateWeekOfTable_Click:
Exit Sub

Err_btnUpdateWeekOfTable_Click:
MsgBox Err.Description
Resume Exit_btnUpdateWeekOfTable_Click

End Sub


Thanks, again!

Nothing appears to be incorrect in the code nor what you have for
settings/formats. So I am at a loss for how to explain what you are
seeing
as the resulting records' values for the inserted records after the code
runs.

Can you post the entire procedure that you used (starting with "Private
Sub
...." through the "End Sub" lines)?
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
bxCurrentWeekEndedDate gets its value when a date is typed into the
box,
which is formatted as Short Date on the text box properties. The value
entered in that box prior to running the code was 04/17/2005 (which, my
desired results are to add 04/11/2005, 04/12/2005.....04/17/2005 -- 7
new
records, since the latest date in the PolicyEffectiveDate field is
04/10/2005). The fields PolicyEffectiveDate and Week Ended are both
formatted as date/time in the data type.

:

Declaring a variable as Date means it will hold date and time data.
Dates
and times are stored as a floating point number, where the "integer"
portion
is the number of days since December 30, 1899, and the "decimal"
portion
is
the percentage of a 24-hour day represented by the time.

Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this
number
is what is actually stored in the variable.

Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a
negative
number as the second argument (note my use of -lngLoop there). From
where
or
how does bxCurrentWeekEndedDate get its value? What was the value that
it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate
is
a
textbox, what is the Format property for it in the form's design view?

Also, what is the format setting for the two fields in the table's
design
view?

--

Ken Snell
<MS ACCESS MVP>


message
My appologies. VBA is definetly a foreign language to me, but you
all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for
figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by
one
each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of
the 7
records. Why did it give time and not date, since I Dimed As Date?

:

A completely different thing than what I thought you wanted to do.

To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that
part
of
the code 7 times.

Assuming that you have a variable named datWeekEnded that holds the
"new"
final value for WeekEnded field:

Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate,
[Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing