Date range on form

  • Thread starter Thread starter Annelie
  • Start date Start date
A

Annelie

I have created a form, where in the query, I put "Between Beginning and
Ending Date"
This of course comes up with the default access box, where you have to enter
the date with slashes and then the form displays only the data for the
selected range.

On the other hand, I have created some forms which have unbound text boxed
in which the user can enter preformatted beginning and ending dates and it
then opens a report with the selected date range. How Can I make this happen
for a form?

Annelie
 
I'm sorry, but I don't understand what you want to do here? Please provide
more details - perhaps include examples of what you want to do?
 
This is the form I want to open - but I want to add the date criteria like I
do for the report shown below, but I do not know where to insert the date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, , strWhere

End Sub
 
The argument in the OpenForm command that is currently filled by
stLinkCriteria is the same "WHERE" argument as the one in OpenReport where
you're using strWhere as the argument.

Therefore, just reproduce in the form opening code snippet the code from
your opening of the report where you're setting the value of strWhere,
except use stLinkCriteria instead of strWhere.


--
Ken Snell
<MS ACCESS MVP>

Annelie said:
This is the form I want to open - but I want to add the date criteria like I
do for the report shown below, but I do not know where to insert the date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, , strWhere

End Sub

Ken Snell said:
I'm sorry, but I don't understand what you want to do here? Please provide
more details - perhaps include examples of what you want to do?
Click to expand...
and
it
Click to expand...
Click to expand...
 
Hi Ken,
Your suggestion worked perfectly. Below is my code. The form to be opened is
set to open in datasheet mode. When I open the form directly, it opens in
datasheet mode - I set all other modes to: do not allow. But when I open it
from the form below, it defaults back to forms view. I don't understand. Is
there something in the code that says it should open in forms view?
Thank you,
Annelie


Private Sub OpenForm29_Click()
On Error GoTo Err_OpenForm29_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
stDocName = "FrmPoursforAnyDateRange"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm29_Click:
Exit Sub

Err_OpenForm29_Click:
MsgBox Err.Description
Resume Exit_OpenForm29_Click

End Sub

Ken Snell said:
The argument in the OpenForm command that is currently filled by
stLinkCriteria is the same "WHERE" argument as the one in OpenReport where
you're using strWhere as the argument.

Therefore, just reproduce in the form opening code snippet the code from
your opening of the report where you're setting the value of strWhere,
except use stLinkCriteria instead of strWhere.


--
Ken Snell
<MS ACCESS MVP>

Annelie said:
This is the form I want to open - but I want to add the date criteria
Click to expand...
like
I
do for the report shown below, but I do not know where to insert the date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, , strWhere

End Sub

Ken Snell said:
I'm sorry, but I don't understand what you want to do here? Please provide
more details - perhaps include examples of what you want to do?


--
Ken Snell
<MS ACCESS MVP>

I have created a form, where in the query, I put "Between Beginning and
Ending Date"
This of course comes up with the default access box, where you have to
enter
the date with slashes and then the form displays only the data for the
selected range.

On the other hand, I have created some forms which have unbound text boxed
in which the user can enter preformatted beginning and ending dates
Click to expand...
and
it
then opens a report with the selected date range. How Can I make this
happen
for a form?

Annelie
Click to expand...
Click to expand...
Click to expand...
 
In this code step:
DoCmd.OpenForm stDocName, , , stLinkCriteria

You are not specifying the view of the form, therefore it uses the default,
which is acNormal (meaning that it will open in the single form view). If
you want to open it in the datasheet view, use this instead:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

--
Ken Snell
<MS ACCESS MVP>



Annelie said:
Hi Ken,
Your suggestion worked perfectly. Below is my code. The form to be opened is
set to open in datasheet mode. When I open the form directly, it opens in
datasheet mode - I set all other modes to: do not allow. But when I open it
from the form below, it defaults back to forms view. I don't understand. Is
there something in the code that says it should open in forms view?
Thank you,
Annelie


Private Sub OpenForm29_Click()
On Error GoTo Err_OpenForm29_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
stDocName = "FrmPoursforAnyDateRange"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm29_Click:
Exit Sub

Err_OpenForm29_Click:
MsgBox Err.Description
Resume Exit_OpenForm29_Click

End Sub

Ken Snell said:
The argument in the OpenForm command that is currently filled by
stLinkCriteria is the same "WHERE" argument as the one in OpenReport where
you're using strWhere as the argument.

Therefore, just reproduce in the form opening code snippet the code from
your opening of the report where you're setting the value of strWhere,
except use stLinkCriteria instead of strWhere.


--
Ken Snell
<MS ACCESS MVP>

Annelie said:
This is the form I want to open - but I want to add the date criteria
Click to expand...
like
I
do for the report shown below, but I do not know where to insert the date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, , strWhere

End Sub

I'm sorry, but I don't understand what you want to do here? Please provide
more details - perhaps include examples of what you want to do?


--
Ken Snell
<MS ACCESS MVP>

I have created a form, where in the query, I put "Between
Click to expand...
Beginning
and
Ending Date"
This of course comes up with the default access box, where you
Click to expand...
Click to expand...
have
to dates
and
Click to expand...
Click to expand...
 
Thank you so much for all your help
Annelie


Ken Snell said:
In this code step:
DoCmd.OpenForm stDocName, , , stLinkCriteria

You are not specifying the view of the form, therefore it uses the default,
which is acNormal (meaning that it will open in the single form view). If
you want to open it in the datasheet view, use this instead:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

--
Ken Snell
<MS ACCESS MVP>



Annelie said:
Hi Ken,
Your suggestion worked perfectly. Below is my code. The form to be
Click to expand...
opened
is
set to open in datasheet mode. When I open the form directly, it opens in
datasheet mode - I set all other modes to: do not allow. But when I open it
from the form below, it defaults back to forms view. I don't understand. Is
there something in the code that says it should open in forms view?
Thank you,
Annelie


Private Sub OpenForm29_Click()
On Error GoTo Err_OpenForm29_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
stDocName = "FrmPoursforAnyDateRange"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm29_Click:
Exit Sub

Err_OpenForm29_Click:
MsgBox Err.Description
Resume Exit_OpenForm29_Click

End Sub

Ken Snell said:
The argument in the OpenForm command that is currently filled by
stLinkCriteria is the same "WHERE" argument as the one in OpenReport where
you're using strWhere as the argument.

Therefore, just reproduce in the form opening code snippet the code from
your opening of the report where you're setting the value of strWhere,
except use stLinkCriteria instead of strWhere.


--
Ken Snell
<MS ACCESS MVP>

This is the form I want to open - but I want to add the date
Click to expand...
criteria
like
I
do for the report shown below, but I do not know where to insert the date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, , strWhere

End Sub

I'm sorry, but I don't understand what you want to do here? Please
provide
more details - perhaps include examples of what you want to do?


--
Ken Snell
<MS ACCESS MVP>

I have created a form, where in the query, I put "Between Beginning
and
Ending Date"
This of course comes up with the default access box, where you
Click to expand...
have
to
enter
the date with slashes and then the form displays only the data
Click to expand...
for
the
selected range.

On the other hand, I have created some forms which have unbound text
boxed
in which the user can enter preformatted beginning and ending dates
and
it
then opens a report with the selected date range. How Can I make this
happen
for a form?

Annelie
Click to expand...
Click to expand...
Click to expand...
 
You're welcome.

--
Ken Snell
<MS ACCESS MVP>

Annelie said:
Thank you so much for all your help
Annelie


Ken Snell said:
In this code step:
DoCmd.OpenForm stDocName, , , stLinkCriteria

You are not specifying the view of the form, therefore it uses the default,
which is acNormal (meaning that it will open in the single form view). If
you want to open it in the datasheet view, use this instead:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

--
Ken Snell
<MS ACCESS MVP>



Annelie said:
Hi Ken,
Your suggestion worked perfectly. Below is my code. The form to be
Click to expand...
opened
is
set to open in datasheet mode. When I open the form directly, it opens in
datasheet mode - I set all other modes to: do not allow. But when I
Click to expand...
open
it
from the form below, it defaults back to forms view. I don't
Click to expand...
understand.
Is
there something in the code that says it should open in forms view?
Thank you,
Annelie


Private Sub OpenForm29_Click()
On Error GoTo Err_OpenForm29_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
stLinkCriteria = stLinkCriteria & " AND [Pour Date] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
stDocName = "FrmPoursforAnyDateRange"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm29_Click:
Exit Sub

Err_OpenForm29_Click:
MsgBox Err.Description
Resume Exit_OpenForm29_Click

End Sub

The argument in the OpenForm command that is currently filled by
stLinkCriteria is the same "WHERE" argument as the one in OpenReport where
you're using strWhere as the argument.

Therefore, just reproduce in the form opening code snippet the code from
your opening of the report where you're setting the value of strWhere,
except use stLinkCriteria instead of strWhere.


--
Ken Snell
<MS ACCESS MVP>

This is the form I want to open - but I want to add the date criteria
like
I
do for the report shown below, but I do not know where to insert the
date
from and date to like in the report request below.

Private Sub OpenForm0_Click()
On Error GoTo Err_OpenForm0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ENTER date for NEW POURS AND ADD PAYROLL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenForm0_Click:
Exit Sub

Err_OpenForm0_Click:
MsgBox Err.Description
Resume Exit_OpenForm0_Click

End Sub
--------------------------------------------------------------
Private Sub Print_Click()

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5B-YTDbyJobSEformatcboDates", acViewPreview, ,
strWhere

End Sub

I'm sorry, but I don't understand what you want to do here? Please
provide
more details - perhaps include examples of what you want to do?


--
Ken Snell
<MS ACCESS MVP>

I have created a form, where in the query, I put "Between Beginning
and
Ending Date"
This of course comes up with the default access box, where you have
to
enter
the date with slashes and then the form displays only the data for
the
selected range.

On the other hand, I have created some forms which have
Click to expand...
unbound
text
boxed
in which the user can enter preformatted beginning and ending dates
and
it
then opens a report with the selected date range. How Can I make
this
happen
for a form?

Annelie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top