Help with between dates code

G

Guest

Could I please have some help with this code.

I have the below code so far but it don't do what I would like.

I want it to check if there are [RosterDate] bewteen Me!LeaveStartDate and
Me!LeaveEndDate
but I also want to include the first day of leave LeaveStartDate and the
last day of leave LeaveEndDate not just dates between them
and if there are [RosterDate] within that period then display the message.

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " & rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If

End Sub


Thank you

Rodney
 
F

fredg

Could I please have some help with this code.

I have the below code so far but it don't do what I would like.

I want it to check if there are [RosterDate] bewteen Me!LeaveStartDate and
Me!LeaveEndDate
but I also want to include the first day of leave LeaveStartDate and the
last day of leave LeaveEndDate not just dates between them
and if there are [RosterDate] within that period then display the message.

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " & rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If

End Sub

Thank you

Rodney
Click to expand...

Nowhere in your code do you go from one record to the next to see what
the [rosterdate] value is.

Adapting your code, the new lines have a * after them:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
rst.MoveFirst ' *
Do While Not rst.EOF ' *
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " &
rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If
rst.MoveNext ' *
Loop ' *

Exit_This_Sub: "*
Set rst = Nothing '*
Set Dbs = Nothing ' *
Exit Sub ' *
End Sub

However wouldn't this be simpler?

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then
MsgBox "This trainer is already on roster on that date."
Cancel = true
End If

End Sub
 
G

Guest

Fred,

Thank you for your help.
I have tryed your suggestions and the first I get a syntax error on the line

Exit_This_Sub:

And the second one get a syntax error on the line

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and

Than you again

Rodney


fredg said:
Could I please have some help with this code.

I have the below code so far but it don't do what I would like.

I want it to check if there are [RosterDate] bewteen Me!LeaveStartDate and
Me!LeaveEndDate
but I also want to include the first day of leave LeaveStartDate and the
last day of leave LeaveEndDate not just dates between them
and if there are [RosterDate] within that period then display the message.

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " & rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If

End Sub

Thank you

Rodney
Click to expand...

Nowhere in your code do you go from one record to the next to see what
the [rosterdate] value is.

Adapting your code, the new lines have a * after them:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
rst.MoveFirst ' *
Do While Not rst.EOF ' *
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " &
rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If
rst.MoveNext ' *
Loop ' *

Exit_This_Sub: "*
Set rst = Nothing '*
Set Dbs = Nothing ' *
Exit Sub ' *
End Sub

However wouldn't this be simpler?

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then
MsgBox "This trainer is already on roster on that date."
Cancel = true
End If

End Sub
Click to expand...
 
F

fredg

Fred,

Thank you for your help.
I have tryed your suggestions and the first I get a syntax error on the line

Exit_This_Sub:

And the second one get a syntax error on the line

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and

Than you again

Rodney

fredg said:
Could I please have some help with this code.

I have the below code so far but it don't do what I would like.

I want it to check if there are [RosterDate] bewteen Me!LeaveStartDate and
Me!LeaveEndDate
but I also want to include the first day of leave LeaveStartDate and the
last day of leave LeaveEndDate not just dates between them
and if there are [RosterDate] within that period then display the message.

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " & rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If

End Sub

Thank you

Rodney
Click to expand...

Nowhere in your code do you go from one record to the next to see what
the [rosterdate] value is.

Adapting your code, the new lines have a * after them:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
rst.MoveFirst ' *
Do While Not rst.EOF ' *
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " &
rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If
rst.MoveNext ' *
Loop ' *

Exit_This_Sub: "*
Set rst = Nothing '*
Set Dbs = Nothing ' *
Exit Sub ' *
End Sub

However wouldn't this be simpler?

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then
MsgBox "This trainer is already on roster on that date."
Cancel = true
End If

End Sub
Click to expand...
Click to expand...

1) This line ...
Exit_This_Sub:
should be OK. I have no idea why you are getting a syntax error on it.
It should be on a line all by itself, with the underscore's and colon.

1a) It would have been best if you had posted your complete exact
code.


2) Email messages are word wrapped if he line length is too long.
This should all be on one line.

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then

When using VBA, if you wish to break a line you would use the SPACE
UNDERSCORE combination, i.e.

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " _
and [RosterDate] >= #" & Me.LeaveStartDate & "# and _
[RosterDate] <= #" & Me!LeaveEndDate & "#") > 0 then

2a) See # 1a above.
 
G

Guest

Fred,
Thank you for your help, I never considered the word wrap (sorry for that).
It all works great now, I apologise for taking so long to reply but I am in
Australia and the time difference makes it difficult but great work and thank
you again.


Rodney

fredg said:
Fred,

Thank you for your help.
I have tryed your suggestions and the first I get a syntax error on the line

Exit_This_Sub:

And the second one get a syntax error on the line

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and

Than you again

Rodney

fredg said:
On Thu, 16 Aug 2007 22:38:00 -0700, Rodney wrote:

Could I please have some help with this code.

I have the below code so far but it don't do what I would like.

I want it to check if there are [RosterDate] bewteen Me!LeaveStartDate and
Me!LeaveEndDate
but I also want to include the first day of leave LeaveStartDate and the
last day of leave LeaveEndDate not just dates between them
and if there are [RosterDate] within that period then display the message.

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " & rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If

End Sub

Thank you

Rodney

Nowhere in your code do you go from one record to the next to see what
the [rosterdate] value is.

Adapting your code, the new lines have a * after them:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblRoster", dbOpenDynaset)
rst.MoveFirst ' *
Do While Not rst.EOF ' *
If rst![TrainerID] = Me!TrainerID Then
If rst![RosterDate] >= Me!LeaveStartDate Then
If rst![RosterDate] <= Me!LeaveEndDate Then
Cancel = True
MsgBox "The trainer is rostered on for " &
rst![RosterDate]
& vbCrLf & _
"please Remove trainer from roster first."
End If
End If
End If
rst.MoveNext ' *
Loop ' *

Exit_This_Sub: "*
Set rst = Nothing '*
Set Dbs = Nothing ' *
Exit Sub ' *
End Sub

However wouldn't this be simpler?

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)
If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then
MsgBox "This trainer is already on roster on that date."
Cancel = true
End If

End Sub
Click to expand...
Click to expand...

1) This line ...
Exit_This_Sub:
should be OK. I have no idea why you are getting a syntax error on it.
It should be on a line all by itself, with the underscore's and colon.

1a) It would have been best if you had posted your complete exact
code.


2) Email messages are word wrapped if he line length is too long.
This should all be on one line.

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " and
[RosterDate] >= #" & Me.LeaveStartDate & "# and [RosterDate] <= #" &
Me!LeaveEndDate & "#") > 0 then

When using VBA, if you wish to break a line you would use the SPACE
UNDERSCORE combination, i.e.

If DCount("*","tblRoster","[TrainerID] = " & Me!TrainerID & " _
and [RosterDate] >= #" & Me.LeaveStartDate & "# and _
[RosterDate] <= #" & Me!LeaveEndDate & "#") > 0 then

2a) See # 1a above.
Click to expand...
 

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

Similar Threads

Form code and message box help 5
Form code and message box help continued 3
Canceling an OnClick Event 3
Loop Loopy 2
Access Switchboard not Working - Private Function HandleButtonClick 1
Message from Access 2007 on sub function 3
VBA for report detail data record source 4
Switchboard form no longer fills 1

Top