Please help with this code...

R

Rick's News

I can't get this to work... It is a report that will have dates outside of
what I want to display.
It is supposed to move the text box TIMELINE width for the given days.
However, I only want to show the current days on the report, even if the
leavestart is before today's date or leave end is after today + 30 days.

Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes to
12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
 
M

Marshall Barton

Rick's News said:
I can't get this to work... It is a report that will have dates outside of
what I want to display.
It is supposed to move the text box TIMELINE width for the given days.
However, I only want to show the current days on the report, even if the
leavestart is before today's date or leave end is after today + 30 days.

Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes to
12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

You set lngStart and lngDuration to a date value in the true
part of the If, but you expect it to contain a number of
days.

I think you want to use

If Me.LeaveStart < Date Then
lngStart = 0
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If
 
D

Duane Hookom

I don't quite understand where your problem is. Do you know how to debug
code by setting breakpoints? That's how I wrote the original code was to set
breakpoints and test.
 
R

Rick's News

I am very new at this stuff... I am getting this done because a lot of
people like you helping me.

I don't know how to debug. I know how to get to that window and I know I
can highlight the line in purple to set the breakpoint. How do I run to
that breakpoint and then go to the next line and step through?

Thank you,
-Rick
 
M

Marshall Barton

Rick's News said:
I am getting a datatype mismatch error...

What does the code look like now and where are you getting
the error?
--
Marsh
MVP [MS Access]


 
R

Rick's News

Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display this...

The Report shows me personnel's vacation days on a continuous 30 rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the Me.[boxTimeLine]
starting with the current date. However I still need it to count from the
original [leavestart] to accurately show the [leaveend].

If leave start = 20 November and today is 22 November I need the leave start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause an
overflow error and there is know place to paint the days.

Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the original
day.

Thanks,

Rick
 
M

Marshall Barton

Rick's News said:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display this...

The Report shows me personnel's vacation days on a continuous 30 rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the Me.[boxTimeLine]
starting with the current date. However I still need it to count from the
original [leavestart] to accurately show the [leaveend].

You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?

If leave start = 20 November and today is 22 November I need the leave start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??

Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the original
day.

What do you mean by "adds from" ?
What is the original day?



 
R

Rick's News

Sorry Marshall,

This is an example of what I want:

Today = 22 Nov 03
LeaveStart = 20 Nov 03
Leave End = 28 Dec 03

If I print the report today and today is 22 Nov 03 then I want the report to
only color these days = 22 Nov 03 - 21 Dec 03

I don't want the extra days on this report... Just the current 30 day
forecast...

Hope this makes more sense...

Thanks Again,
Rick

Marshall Barton said:
Rick's News said:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display this...

The Report shows me personnel's vacation days on a continuous 30 rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the Me.[boxTimeLine]
starting with the current date. However I still need it to count from the
original [leavestart] to accurately show the [leaveend].

You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?

If leave start = 20 November and today is 22 November I need the leave start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??

Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the original
day.

What do you mean by "adds from" ?
What is the original day?
 
M

Marshall Barton

Rick's News said:
Sorry Marshall,
Rick's News said:
This is an example of what I want:

Today = 22 Nov 03
LeaveStart = 20 Nov 03
Leave End = 28 Dec 03

If I print the report today and today is 22 Nov 03 then I want the report to
only color these days = 22 Nov 03 - 21 Dec 03

I don't want the extra days on this report... Just the current 30 day
forecast...

I must be particularly dense today. I thought that

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If

did just what you're asking about. If you are using
different code, then please copy/paste the current code so I
can see what you have. If that is what you have, what is it
doing that you don't want it to do?
--
Marsh
MVP [MS Access]


Rick's News said:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display this...

The Report shows me personnel's vacation days on a continuous 30 rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the Me.[boxTimeLine]
starting with the current date. However I still need it to count from the
original [leavestart] to accurately show the [leaveend].
"Marshall Barton" wrote
You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?

Rick's News wrote:
If leave start = 20 November and today is 22 November I need the leave start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??

Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the original
day.

What do you mean by "adds from" ?
What is the original day?


Rick's News wrote:

I can't get this to work... It is a report that will have dates
outside of what I want to display.
It is supposed to move the text box TIMELINE width for the given days.
However, I only want to show the current days on the report, even if
the leavestart is before today's date or leave end is after today + 30 days.

Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes
to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

You set lngStart and lngDuration to a date value in the true
part of the If, but you expect it to contain a number of
days.

I think you want to use

If Me.LeaveStart < Date Then
lngStart = 0
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If
 
R

Rick's News

The problem is if I declare that LngStart = 0 and the difference in days
between current day and four days ago gets tacked on to [leaveend] on the
report because it starts from 0 and moves over from 0 instead of - 4 days...

Pasted code:

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes to
12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.[LeaveStart] < Date Then
lngStart = 0
lngDuration = DateDiff("d", (Me.[LeaveStart] - Date), Me.[LeaveEnd]) + 1
Else
lngStart = DateDiff("d", Date, Me.[LeaveStart])
End If
If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30) + 1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If
Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False


Marshall Barton said:
Rick's News said:
Sorry Marshall,
Rick's News said:
This is an example of what I want:

Today = 22 Nov 03
LeaveStart = 20 Nov 03
Leave End = 28 Dec 03

If I print the report today and today is 22 Nov 03 then I want the report to
only color these days = 22 Nov 03 - 21 Dec 03

I don't want the extra days on this report... Just the current 30 day
forecast...

I must be particularly dense today. I thought that

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If

did just what you're asking about. If you are using
different code, then please copy/paste the current code so I
can see what you have. If that is what you have, what is it
doing that you don't want it to do?
--
Marsh
MVP [MS Access]


Rick's News wrote:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display this...

The Report shows me personnel's vacation days on a continuous 30 rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the Me.[boxTimeLine]
starting with the current date. However I still need it to count from the
original [leavestart] to accurately show the [leaveend].
"Marshall Barton" wrote
You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?


Rick's News wrote:
If leave start = 20 November and today is 22 November I need the leave start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will
cause
an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??


Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the original
day.

What do you mean by "adds from" ?
What is the original day?



Rick's News wrote:

I can't get this to work... It is a report that will have dates
outside of what I want to display.
It is supposed to move the text box TIMELINE width for the given days.
However, I only want to show the current days on the report,
even
if
the leavestart is before today's date or leave end is after
today +
30 days.
Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes
to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

You set lngStart and lngDuration to a date value in the true
part of the If, but you expect it to contain a number of
days.

I think you want to use

If Me.LeaveStart < Date Then
lngStart = 0
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If
 
M

Marshall Barton

Rick's News said:
The problem is if I declare that LngStart = 0 and the difference in days
between current day and four days ago gets tacked on to [leaveend] on the
report because it starts from 0 and moves over from 0 instead of - 4 days...

Yes, of course. I really am being dense, not just today,
but all week :-( I should have caught that right at the
beginning. Sorry to cause so much confusion.

It looks like we need to expand on what you started with and
keep track of the adjusted LeaveStart date to use in the
duration calculation. Anyway, here's the code I came up
with for a test form to do at least some of what I think
you're trying to do:

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim dteStartLeave As Date
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and
goes to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.[LeaveStart] > Date + 29 _
Or Me.LeaveEnd < Date Then
Me.txtname.Visible = False
Exit Sub
End If

If Me.[LeaveStart] < Date Then
dteStartLeave = Date
lngStart = 0
Else
dteStartLeave = Me.[LeaveStart]
lngStart = DateDiff("d", Date, dteStartLeave)
End If
If Me.LeaveEnd > Date + 29 Then
lngDuration = DateDiff("d", _
dteStartLeave, Date + 29) + 1
Else
lngDuration = DateDiff("d", _
dteStartLeave, Me.LeaveEnd) + 1
End If
Me.txtname.Visible = True
Me.txtname.Width = 0
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = lngDuration * dblFactor
--
Marsh
MVP [MS Access]


Pasted code:

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes to
12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.[LeaveStart] < Date Then
lngStart = 0
lngDuration = DateDiff("d", (Me.[LeaveStart] - Date), Me.[LeaveEnd]) + 1
Else
lngStart = DateDiff("d", Date, Me.[LeaveStart])
End If
If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30) + 1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If
Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

Rick's News said:
Sorry Marshall,
Rick's News said:
This is an example of what I want:

Today = 22 Nov 03
LeaveStart = 20 Nov 03
Leave End = 28 Dec 03

If I print the report today and today is 22 Nov 03 then I want the report to
only color these days = 22 Nov 03 - 21 Dec 03

I don't want the extra days on this report... Just the current 30 day
forecast...
"Marshall Barton" wrote
I must be particularly dense today. I thought that

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If

did just what you're asking about. If you are using
different code, then please copy/paste the current code so I
can see what you have. If that is what you have, what is it
doing that you don't want it to do?
--
Marsh
MVP [MS Access]


Rick's News wrote:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display
this...

The Report shows me personnel's vacation days on a continuous 30
rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the
Me.[boxTimeLine]
starting with the current date. However I still need it to count from
the
original [leavestart] to accurately show the [leaveend].

You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?


Rick's News wrote:
If leave start = 20 November and today is 22 November I need the leave
start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause
an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??


Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the
original
day.

What do you mean by "adds from" ?
What is the original day?



Rick's News wrote:

I can't get this to work... It is a report that will have dates
outside of what I want to display.
It is supposed to move the text box TIMELINE width for the given
days.
However, I only want to show the current days on the report, even
if
the leavestart is before today's date or leave end is after today +
30 days.

Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and
goes
to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

You set lngStart and lngDuration to a date value in the true
part of the If, but you expect it to contain a number of
days.

I think you want to use

If Me.LeaveStart < Date Then
lngStart = 0
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If
 
R

Rick's News

You did it Marshall...
Thanks for sticking with me all day! I appreciate ALL your help to me and
others. I have learned a lot from you ghosting this newsgroup.

One more question? How do I append all records in a table with this info...

Dim rs As DAO.Recordset 'define a DAO recordset
Set rs = CurrentDb.OpenRecordset("Appointments") 'set the recordset to your
table
rs.AddNew 'add a new record

*** rs![SSN] = Me.SSN*** This where I need help. It will add just theone
record but I want to add the appointment for everyone in the TBase table.

rs![AppointmentType] = Me.[AppointmentType] 'set the field in the memo table
to the text on your form
rs![SchedDate] = Me.SchedDate 'set the field in the memo table to the text
on your form
rs![SchedStart] = Me.SchedStart 'set the field in the memo table to the text
on your form
rs![SchedEnd] = Me.SchedEnd 'set the field in the memo table to the text on
your form

rs.Update 'update the record with the new data

rs.Close 'close the recordset
Set rs = Nothing 'destroy the reference

Thanks again,
Rick

Marshall Barton said:
Rick's News said:
The problem is if I declare that LngStart = 0 and the difference in days
between current day and four days ago gets tacked on to [leaveend] on the
report because it starts from 0 and moves over from 0 instead of - 4
days...

Yes, of course. I really am being dense, not just today,
but all week :-( I should have caught that right at the
beginning. Sorry to cause so much confusion.

It looks like we need to expand on what you started with and
keep track of the adjusted LeaveStart date to use in the
duration calculation. Anyway, here's the code I came up
with for a test form to do at least some of what I think
you're trying to do:

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim dteStartLeave As Date
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and
goes to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.[LeaveStart] > Date + 29 _
Or Me.LeaveEnd < Date Then
Me.txtname.Visible = False
Exit Sub
End If

If Me.[LeaveStart] < Date Then
dteStartLeave = Date
lngStart = 0
Else
dteStartLeave = Me.[LeaveStart]
lngStart = DateDiff("d", Date, dteStartLeave)
End If
If Me.LeaveEnd > Date + 29 Then
lngDuration = DateDiff("d", _
dteStartLeave, Date + 29) + 1
Else
lngDuration = DateDiff("d", _
dteStartLeave, Me.LeaveEnd) + 1
End If
Me.txtname.Visible = True
Me.txtname.Width = 0
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = lngDuration * dblFactor
--
Marsh
MVP [MS Access]


Pasted code:

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and goes to
12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.[LeaveStart] < Date Then
lngStart = 0
lngDuration = DateDiff("d", (Me.[LeaveStart] - Date), Me.[LeaveEnd]) + 1
Else
lngStart = DateDiff("d", Date, Me.[LeaveStart])
End If
If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30) + 1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd) + 1
End If
Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

Rick's News wrote:

Sorry Marshall,
Rick's News wrote:
This is an example of what I want:

Today = 22 Nov 03
LeaveStart = 20 Nov 03
Leave End = 28 Dec 03

If I print the report today and today is 22 Nov 03 then I want the
report
to
only color these days = 22 Nov 03 - 21 Dec 03

I don't want the extra days on this report... Just the current 30 day
forecast...
"Marshall Barton" wrote
I must be particularly dense today. I thought that

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If

did just what you're asking about. If you are using
different code, then please copy/paste the current code so I
can see what you have. If that is what you have, what is it
doing that you don't want it to do?
--
Marsh
MVP [MS Access]



Rick's News wrote:
Sorry, The error was in the Query behind the report.

But now that I can view the report I need to find a way to display
this...

The Report shows me personnel's vacation days on a continuous 30
rotation.
If I open the report today I should see everyone On leave where the leave
start date started before 22 November, but only "paint" the
Me.[boxTimeLine]
starting with the current date. However I still need it to count from
the
original [leavestart] to accurately show the [leaveend].

You're losing me here. Does this mean that the problem with
positioning the text is OK, or are we still working on the
same issuers?

If this is a different problem, what result are you looking
for and what are you getting?


Rick's News wrote:
If leave start = 20 November and today is 22 November I need the leave
start
to show 22 November.
If leave end = 24 December the stop at 22 December because it will cause
an
overflow error and there is know place to paint the days.

But LeaveStart is the number of days after today when the
vacation starts, isn't it??


Thanks for helping me. I hope I explained enough details to you.
Let me know if I need to explain this further.

The code works but it adds from [leavestart] = 0 and not from the
original
day.

What do you mean by "adds from" ?
What is the original day?



Rick's News wrote:

I can't get this to work... It is a report that will have dates
outside of what I want to display.
It is supposed to move the text box TIMELINE width for the given
days.
However, I only want to show the current days on the report, even
if
the leavestart is before today's date or leave end is after today +
30 days.

Can someone please help me...

Dim lngDuration As Long 'days of leave
Dim lngStart As Long 'start date of leave
Dim lngLMarg As Long
Dim dblFactor As Double
'put a line control in your page header that starts 1/1 and
goes
to 12/31
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 30

If Me.LeaveStart < Date Then
lngStart = Date
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = Date + 30
Else
lngDuration = DateDiff("d", Me.LeaveStart,
Me.LeaveEnd)
+ 1
End If

Me.txtname.BorderColor = 255
Me.txtname.BackColor = 255
Me.txtname.Width = 5 'avoid the positioning error
Me.txtname.Left = (lngStart * dblFactor) + lngLMarg
Me.txtname.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

You set lngStart and lngDuration to a date value in the true
part of the If, but you expect it to contain a number of
days.

I think you want to use

If Me.LeaveStart < Date Then
lngStart = 0
Else
lngStart = DateDiff("d", Date, Me.LeaveStart)
End If

If Me.LeaveEnd > Date + 30 Then
lngDuration = DateDiff("d", Me.LeaveStart, Date + 30)+1
Else
lngDuration = DateDiff("d", Me.LeaveStart, Me.LeaveEnd)+1
End If
 
M

Marshall Barton

Rick's News said:
How do I append all records in a table with this info...

Dim rs As DAO.Recordset 'define a DAO recordset
Set rs = CurrentDb.OpenRecordset("Appointments") 'set the recordset to your
table
rs.AddNew 'add a new record

*** rs![SSN] = Me.SSN*** This where I need help. It will add just theone
record but I want to add the appointment for everyone in the TBase table.

rs![AppointmentType] = Me.[AppointmentType] 'set the field in the memo table
to the text on your form
rs![SchedDate] = Me.SchedDate 'set the field in the memo table to the text
on your form
rs![SchedStart] = Me.SchedStart 'set the field in the memo table to the text
on your form
rs![SchedEnd] = Me.SchedEnd 'set the field in the memo table to the text on
your form

rs.Update 'update the record with the new data

This doesn't sound right. You don't really want to add
records with all that data for the same appointment, do you?
That will violate the normalization rule about duplicated
data.

This looks like it's a many to many relationship between
people and the event they're supposed to attend. Many
people can attend the same event and each person may attend
more than one event (at differnt times).

Normalization rules dictate that you should have a table for
events that has one record for data of the type you
described. Then you should have another table (often called
a junction table) for the appointments that just has one
field containing the PK for a person and another for the PK
of the event. Each record in the junction table then
indicates the person and the event. A query for the event
PK would then produce a list of the attendees for that
event.

To answer your specific question, you need to loop through
the records in the TBase table adding a record to the
appointments table for each TBase record.

Dim rs As DAO.Recordset
Dim rsPerson As DAO.Recordset
Set rsPerson = CurrentDb.OpenRecordset("TBase")
Set rs = CurrentDb.OpenRecordset("Appointments")
Do Until rsPerson.EOF
rs.AddNew 'add a new record
rs![SSN] = rsPerson.SSN
. . .
rs.Update
rsPerson.MoveNext
Loop
rs.Close: Set rs = Nothing
rsPerson.Close: Set rsPerson = Nothing
 
R

Rick's News

Are you still up?

I'm getting a method or object not recognized on this line:

rs![SSN] = rsPerson.SSN

Where does it find the SSN on the tbase table?
I sorry so many questions, just trying to learn...

Thanks,
-Rick

Marshall Barton said:
Rick's News said:
How do I append all records in a table with this info...

Dim rs As DAO.Recordset 'define a DAO recordset
Set rs = CurrentDb.OpenRecordset("Appointments") 'set the recordset to your
table
rs.AddNew 'add a new record

*** rs![SSN] = Me.SSN*** This where I need help. It will add just theone
record but I want to add the appointment for everyone in the TBase table.

rs![AppointmentType] = Me.[AppointmentType] 'set the field in the memo table
to the text on your form
rs![SchedDate] = Me.SchedDate 'set the field in the memo table to the text
on your form
rs![SchedStart] = Me.SchedStart 'set the field in the memo table to the text
on your form
rs![SchedEnd] = Me.SchedEnd 'set the field in the memo table to the text on
your form

rs.Update 'update the record with the new data

This doesn't sound right. You don't really want to add
records with all that data for the same appointment, do you?
That will violate the normalization rule about duplicated
data.

This looks like it's a many to many relationship between
people and the event they're supposed to attend. Many
people can attend the same event and each person may attend
more than one event (at differnt times).

Normalization rules dictate that you should have a table for
events that has one record for data of the type you
described. Then you should have another table (often called
a junction table) for the appointments that just has one
field containing the PK for a person and another for the PK
of the event. Each record in the junction table then
indicates the person and the event. A query for the event
PK would then produce a list of the attendees for that
event.

To answer your specific question, you need to loop through
the records in the TBase table adding a record to the
appointments table for each TBase record.

Dim rs As DAO.Recordset
Dim rsPerson As DAO.Recordset
Set rsPerson = CurrentDb.OpenRecordset("TBase")
Set rs = CurrentDb.OpenRecordset("Appointments")
Do Until rsPerson.EOF
rs.AddNew 'add a new record
rs![SSN] = rsPerson.SSN
. . .
rs.Update
rsPerson.MoveNext
Loop
rs.Close: Set rs = Nothing
rsPerson.Close: Set rsPerson = Nothing
 
R

Rick's News

I found it...
rs![SSN] = rsPerson!SSN

Thanks for ALL your help today.

Good Night...

-Rick

Rick's News said:
Are you still up?

I'm getting a method or object not recognized on this line:

rs![SSN] = rsPerson.SSN

Where does it find the SSN on the tbase table?
I sorry so many questions, just trying to learn...

Thanks,
-Rick

Marshall Barton said:
Rick's News said:
How do I append all records in a table with this info...

Dim rs As DAO.Recordset 'define a DAO recordset
Set rs = CurrentDb.OpenRecordset("Appointments") 'set the recordset to your
table
rs.AddNew 'add a new record

*** rs![SSN] = Me.SSN*** This where I need help. It will add just theone
record but I want to add the appointment for everyone in the TBase table.

rs![AppointmentType] = Me.[AppointmentType] 'set the field in the memo table
to the text on your form
rs![SchedDate] = Me.SchedDate 'set the field in the memo table to the text
on your form
rs![SchedStart] = Me.SchedStart 'set the field in the memo table to the text
on your form
rs![SchedEnd] = Me.SchedEnd 'set the field in the memo table to the
text
on
your form

rs.Update 'update the record with the new data

This doesn't sound right. You don't really want to add
records with all that data for the same appointment, do you?
That will violate the normalization rule about duplicated
data.

This looks like it's a many to many relationship between
people and the event they're supposed to attend. Many
people can attend the same event and each person may attend
more than one event (at differnt times).

Normalization rules dictate that you should have a table for
events that has one record for data of the type you
described. Then you should have another table (often called
a junction table) for the appointments that just has one
field containing the PK for a person and another for the PK
of the event. Each record in the junction table then
indicates the person and the event. A query for the event
PK would then produce a list of the attendees for that
event.

To answer your specific question, you need to loop through
the records in the TBase table adding a record to the
appointments table for each TBase record.

Dim rs As DAO.Recordset
Dim rsPerson As DAO.Recordset
Set rsPerson = CurrentDb.OpenRecordset("TBase")
Set rs = CurrentDb.OpenRecordset("Appointments")
Do Until rsPerson.EOF
rs.AddNew 'add a new record
rs![SSN] = rsPerson.SSN
. . .
rs.Update
rsPerson.MoveNext
Loop
rs.Close: Set rs = Nothing
rsPerson.Close: Set rsPerson = Nothing
 
M

Marshall Barton

Rick's News said:
I found it...
rs![SSN] = rsPerson!SSN

Good! Told you I was having a bad week. That was a silly
typo on my part.

Thanks for ALL your help today.

I guess after all the trouble I caused, it's nice to hear
that you are actually making progress.

Good Night...

Excellent suggestion, which I will immediately follow.
--
Marsh
MVP [MS Access]

Rick's News said:
Are you still up?

I'm getting a method or object not recognized on this line:

rs![SSN] = rsPerson.SSN

Where does it find the SSN on the tbase table?
I sorry so many questions, just trying to learn...

Thanks,
-Rick
Rick's News wrote:
How do I append all records in a table with this info...

Dim rs As DAO.Recordset 'define a DAO recordset
Set rs = CurrentDb.OpenRecordset("Appointments") 'set the recordset to your
table
rs.AddNew 'add a new record

*** rs![SSN] = Me.SSN*** This where I need help. It will add just theone
record but I want to add the appointment for everyone in the TBase table.

rs![AppointmentType] = Me.[AppointmentType] 'set the field in the memo table
to the text on your form
rs![SchedDate] = Me.SchedDate 'set the field in the memo table to the text
on your form
rs![SchedStart] = Me.SchedStart 'set the field in the memo table to the text
on your form
rs![SchedEnd] = Me.SchedEnd 'set the field in the memo table to the
text
on
your form

rs.Update 'update the record with the new data
"Marshall Barton" wrote
This doesn't sound right. You don't really want to add
records with all that data for the same appointment, do you?
That will violate the normalization rule about duplicated
data.

This looks like it's a many to many relationship between
people and the event they're supposed to attend. Many
people can attend the same event and each person may attend
more than one event (at differnt times).

Normalization rules dictate that you should have a table for
events that has one record for data of the type you
described. Then you should have another table (often called
a junction table) for the appointments that just has one
field containing the PK for a person and another for the PK
of the event. Each record in the junction table then
indicates the person and the event. A query for the event
PK would then produce a list of the attendees for that
event.

To answer your specific question, you need to loop through
the records in the TBase table adding a record to the
appointments table for each TBase record.

Dim rs As DAO.Recordset
Dim rsPerson As DAO.Recordset
Set rsPerson = CurrentDb.OpenRecordset("TBase")
Set rs = CurrentDb.OpenRecordset("Appointments")
Do Until rsPerson.EOF
rs.AddNew 'add a new record
rs![SSN] = rsPerson.SSN
. . .
rs.Update
rsPerson.MoveNext
Loop
rs.Close: Set rs = Nothing
rsPerson.Close: Set rsPerson = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top