Two Dates

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

I need to get two dates (for a range) and store in a table. I want to do
this so I don't have to reenter each time I need the range. The date range
would be used for querys and reports. I want to enter the dates from a form.

Last, I only want two dates stored in the table. Either two of the same or a
range. These would be updated each time I opened and entered into the form.
How do I keep the table from growing past 2 entries?
thanks.
 
rob p said:
I need to get two dates (for a range) and store in a table. I want to do
this so I don't have to reenter each time I need the range. The date range
would be used for querys and reports. I want to enter the dates from a form.

Last, I only want two dates stored in the table. Either two of the same or a
range. These would be updated each time I opened and entered into the form.
How do I keep the table from growing past 2 entries?
thanks.
Click to expand...

Create a table with two fields, for example called tblDefaults with the
date fields StartDateDef and EndDateDef. Create one record in this table and
set each field to nominal values.

On your ReportOptions form create two unbound text boxes , for example
called , StartDate and EndDate. Set their formats to some kind of date, and
their default values to GetStartDateDef() and GetEndDateDef() respectively.
You could also set their "After Update" events to the code I've supplied
below.

Put the four functions supplied below in the form's code section.

Good luck

Jack


Public Function GetEndDateDef() As Date

' sets the default End date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheEDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the end date
TheEDate = rst!EndDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetEndDateDef = TheEDate

End Function

Public Function GetStartDateDef() As Date

' Gets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheSDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordGet

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the start date
TheSDate = rst!StartDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetStartDateDef= TheSDate
End Function

Public Function SetDefStartDate(TheSDate As Date)

' sets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the start date field
rst!StartDateDef = TheSDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Public Function SetDefEndDate(TheEDate As Date)

' sets the default end date in the defaults table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the end date field
rst!EndDateDef = TheEDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Private Sub StartDate_AfterUpdate()
' stores the latest start date that the user enters
If IsDate(Me.StartDate) Then
SetDefStartDate Me.StartDate
End If
End Sub

Private Sub EndDate_AfterUpdate()
' stores the latest end date that the user enters
If IsDate(Me.EndDate) Then
SetDefEndDate Me.EndDate
End If
End Sub
 
Thank for the detailed answer.
rob

Treebeard said:
or

Create a table with two fields, for example called tblDefaults with the
date fields StartDateDef and EndDateDef. Create one record in this table and
set each field to nominal values.

On your ReportOptions form create two unbound text boxes , for example
called , StartDate and EndDate. Set their formats to some kind of date, and
their default values to GetStartDateDef() and GetEndDateDef() respectively.
You could also set their "After Update" events to the code I've supplied
below.

Put the four functions supplied below in the form's code section.

Good luck

Jack


Public Function GetEndDateDef() As Date

' sets the default End date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheEDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the end date
TheEDate = rst!EndDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetEndDateDef = TheEDate

End Function

Public Function GetStartDateDef() As Date

' Gets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheSDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordGet

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the start date
TheSDate = rst!StartDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetStartDateDef= TheSDate
End Function

Public Function SetDefStartDate(TheSDate As Date)

' sets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the start date field
rst!StartDateDef = TheSDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Public Function SetDefEndDate(TheEDate As Date)

' sets the default end date in the defaults table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the end date field
rst!EndDateDef = TheEDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Private Sub StartDate_AfterUpdate()
' stores the latest start date that the user enters
If IsDate(Me.StartDate) Then
SetDefStartDate Me.StartDate
End If
End Sub

Private Sub EndDate_AfterUpdate()
' stores the latest end date that the user enters
If IsDate(Me.EndDate) Then
SetDefEndDate Me.EndDate
End If
End Sub
Click to expand...
 
Thats seems like a lot of work to store 2 pieces of info. why not just
create a table with 2 fields and just link the form to these fields with
AllowDeletion and AllowAdditions off. That way there can only ever be 1
record. You can then just refer to these dates using a simple select
statement.

Select [StartDate] from tblDataRange;
and
Select [EndDate] from tblDataRange;

Since there is only 1 entry, it will only return 1 value, the date needed.

Kelvin
 
Doesn't it depend whether the form already has a record source? In other
words, if the form already has a different table/query for its record
source, isn't it impossible to add another record source?

(this is a real question, not a rhetorical one)

Jack



Kelvin said:
Thats seems like a lot of work to store 2 pieces of info. why not just
create a table with 2 fields and just link the form to these fields with
AllowDeletion and AllowAdditions off. That way there can only ever be 1
record. You can then just refer to these dates using a simple select
statement.

Select [StartDate] from tblDataRange;
and
Select [EndDate] from tblDataRange;

Since there is only 1 entry, it will only return 1 value, the date needed.

Kelvin

same
or

Create a table with two fields, for example called tblDefaults with the
date fields StartDateDef and EndDateDef. Create one record in this table and
set each field to nominal values.

On your ReportOptions form create two unbound text boxes , for example
called , StartDate and EndDate. Set their formats to some kind of date, and
their default values to GetStartDateDef() and GetEndDateDef() respectively.
You could also set their "After Update" events to the code I've supplied
below.

Put the four functions supplied below in the form's code section.

Good luck

Jack


Public Function GetEndDateDef() As Date

' sets the default End date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheEDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the end date
TheEDate = rst!EndDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetEndDateDef = TheEDate

End Function

Public Function GetStartDateDef() As Date

' Gets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheSDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordGet

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the start date
TheSDate = rst!StartDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetStartDateDef= TheSDate
End Function

Public Function SetDefStartDate(TheSDate As Date)

' sets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the start date field
rst!StartDateDef = TheSDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Public Function SetDefEndDate(TheEDate As Date)

' sets the default end date in the defaults table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the end date field
rst!EndDateDef = TheEDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Private Sub StartDate_AfterUpdate()
' stores the latest start date that the user enters
If IsDate(Me.StartDate) Then
SetDefStartDate Me.StartDate
End If
End Sub

Private Sub EndDate_AfterUpdate()
' stores the latest end date that the user enters
If IsDate(Me.EndDate) Then
SetDefEndDate Me.EndDate
End If
End Sub
Click to expand...
Click to expand...
 
From the description, it sounded to me like he wanted to have a dedicated
form to enter these 2 dates and that this form would only be used for this
purpose. Your method would be good if he wanted to be able to enter the
dates from any form, but it didn't sound that way to me. There wasn't much
detail in the post.

Kelvin

Treebeard said:
Doesn't it depend whether the form already has a record source? In other
words, if the form already has a different table/query for its record
source, isn't it impossible to add another record source?

(this is a real question, not a rhetorical one)

Jack



Kelvin said:
Thats seems like a lot of work to store 2 pieces of info. why not just
create a table with 2 fields and just link the form to these fields with
AllowDeletion and AllowAdditions off. That way there can only ever be 1
record. You can then just refer to these dates using a simple select
statement.

Select [StartDate] from tblDataRange;
and
Select [EndDate] from tblDataRange;

Since there is only 1 entry, it will only return 1 value, the date needed.

Kelvin

Treebeard said:
I need to get two dates (for a range) and store in a table. I want
Click to expand...
Click to expand...
to
do same table
and date,
and
Click to expand...
Click to expand...
 
Hi. I only want one record in tblDataRange - just the ability to keep
changing the date ranges. AllowAdditions off seems to keep it at one.

Assuming the table and fields are named exactly as your sample below, where
do I put the Select statement? I tried in the criteria line also in the SQL
view and got syntax errors. Is it two SELECT statements or one with an AND?
I can't get it to work either way.
Thanks.
rob



Kelvin said:
Thats seems like a lot of work to store 2 pieces of info. why not just
create a table with 2 fields and just link the form to these fields with
AllowDeletion and AllowAdditions off. That way there can only ever be 1
record. You can then just refer to these dates using a simple select
statement.

Select [StartDate] from tblDataRange;
and
Select [EndDate] from tblDataRange;

Since there is only 1 entry, it will only return 1 value, the date needed.

Kelvin

same
or

Create a table with two fields, for example called tblDefaults with the
date fields StartDateDef and EndDateDef. Create one record in this table and
set each field to nominal values.

On your ReportOptions form create two unbound text boxes , for example
called , StartDate and EndDate. Set their formats to some kind of date, and
their default values to GetStartDateDef() and GetEndDateDef() respectively.
You could also set their "After Update" events to the code I've supplied
below.

Put the four functions supplied below in the form's code section.

Good luck

Jack


Public Function GetEndDateDef() As Date

' sets the default End date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheEDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the end date
TheEDate = rst!EndDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetEndDateDef = TheEDate

End Function

Public Function GetStartDateDef() As Date

' Gets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheSDate As Date

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordGet

' open the the table
rst.Open "tblDefaults", dbs, adOpenStatic, adLockReadOnly

' get the start date
TheSDate = rst!StartDateDef

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

GetStartDateDef= TheSDate
End Function

Public Function SetDefStartDate(TheSDate As Date)

' sets the default start date in the last value table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the start date field
rst!StartDateDef = TheSDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Public Function SetDefEndDate(TheEDate As Date)

' sets the default end date in the defaults table
Dim dbs As ADODB.Connection, rst As ADODB.Recordset


' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the databass
Set rst = New Recordset ' open a recordset

' open the table
rst.Open "tblDefaults", dbs, adOpenDynamic, adLockOptimistic

' save the end date field
rst!EndDateDef = TheEDate
rst.Update

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Private Sub StartDate_AfterUpdate()
' stores the latest start date that the user enters
If IsDate(Me.StartDate) Then
SetDefStartDate Me.StartDate
End If
End Sub

Private Sub EndDate_AfterUpdate()
' stores the latest end date that the user enters
If IsDate(Me.EndDate) Then
SetDefEndDate Me.EndDate
End If
End Sub
Click to expand...
Click to expand...
 
I have several applications that use a date range inquiry to a database.
Unfortunate, you haven't provided enough information to determine how to
proceed.
My applications use two date picker controls on a form [dtbegin and dtend]
for my application to provide a report related to the selected dates. The
dates may be the same dates or some date prior to today and in the future.
Or they me be today and today. It all depends on how your tables are
constructed that you are querying against. I do not store any user
selection. The user simply selects the beginning date and ending date they
wish to see a report for. Click "Display Report" command button and they
get the report filtered for their selection.
If you can provide some additional information about your tables that store
you information and how you wish to see the queried information I can
possibly be of more help.

Cheers,
Henry
 
Rob,

I'm not sure if you resolved this yet or not, but here is antoher solution
that might be simpler to use. For your table that keeps track of the dates
add another column such as ID. Just something so you can put in a control
number such as 1. Then instead of the select statement I mentioned earlier
use the DLookup funtion. Set the source for the 2 text boses that will show
these dates to

Textbox1: DLookup("[StartDate]","tblDataRange","[ID]=1")
Textbox2: DLookup("[EndDate]","tblDataRange","[ID]=1")

The DLookup function needs a criteria thats why I suggested adding the
column to add the 1. You can just copy these fields to any form you need.
Hope this helps.

Kelvin

rob p said:
Hi. I only want one record in tblDataRange - just the ability to keep
changing the date ranges. AllowAdditions off seems to keep it at one.

Assuming the table and fields are named exactly as your sample below, where
do I put the Select statement? I tried in the criteria line also in the SQL
view and got syntax errors. Is it two SELECT statements or one with an AND?
I can't get it to work either way.
Thanks.
rob



Kelvin said:
Thats seems like a lot of work to store 2 pieces of info. why not just
create a table with 2 fields and just link the form to these fields with
AllowDeletion and AllowAdditions off. That way there can only ever be 1
record. You can then just refer to these dates using a simple select
statement.

Select [StartDate] from tblDataRange;
and
Select [EndDate] from tblDataRange;

Since there is only 1 entry, it will only return 1 value, the date needed.

Kelvin

Treebeard said:
I need to get two dates (for a range) and store in a table. I want
Click to expand...
Click to expand...
to
do same table
and date,
and
Click to expand...
Click to expand...
 
Back
Top