I'm not really clear about this but perhaps you could have an unbound text
box in the form header with these properties:
Name TheYear
Format General Number
Validation Rule Between 1900 And 2999
Validation Text Enter a 4-digit year
and another text box:
Name TheMonth
Format General Number
Validation Rule Between 1 and 12
Validation Text Month must be a number between 1 and 12.
Your code could then do this kind of thing:
Dim dtMonthStart As Date
Dim dtMonthEnd As Date
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If (IsNull(Me.TheYear) Or IsNull(Me.TheMonth) Then
MsgBox "Enter both month and year"
Else
dtMonthStart = DateSerial(Me.TheYear, Me.TheMonth, 1)
dtMonthEnd = DateSerial(Me.TheYear, Me.TheMonth + 1, 0)
If Me.DateX Between dtMonthStart And dtMonthEnd Then
'whatever
End If
End If
I am not clear about the "whatever" bit. If you needed to read the total
from the table, you could:
strWhere = "DateX Between " & Format(dtMonthStart, strcJetDate) & _
" And " & Format(dtMonthEnd, strcJetDate)
MyTotal = DSum("SomeField", "SomeTable", strWhere)
Of course, if you wanted the total of the Amount field from the form how it
is currently filtered, you could just place a text box in the Form Footer
section, and set its ControlSource to:
=Sum([Amount])
While I am not clear on the question, hopefully that gives you a useful
direction.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
C_Ascheman said:
Ok I can understand that. Lets just suppose I have a form in Tabular
format.
It was a 2 text boxes in the Form Header section called Month_End_Datex
and
Month_End_Total.
In the Detail section of the form are 3 more boxes. Datex, Margin, and
Net_Change. These 3 boxes are filled out depending on the criteria of the
user. So lets say he / she enters 02/01/2006 for a start date, and
02/28/2006
for an end date. Since the form is in Tabular format it will list all
records
that meet this criteria.
What I am needing, and having problems figuring out is if how to check if
the dates in the Datex text boxes of the form are between 02/01/(equal to
year on form) and 02/28/(equal to year entered on form) to make the
Monthly_End_Datex text box display 01/(equal to year entered). Then if the
Month_End_Datex is say 01/2006 for it to place the correct value in
Monthly_End_Total where the Month_End_Datex equals the Month_End_Total
from
the table Main.
I was trying to use:
If [Datex] >= "02/01/yyyy" then
If [Datex] <= "02/28/yyyy" then
[Month_End_Datex] = "01/yyyy"
[Month_End_Total] = [Main]![Month_End_Total] where
[Main]![Month_End_Datex] = [Month_End_Datex]
end if
end if
I know this is incorrect as the yyyy in the strings, but for the life of
me
I cannot figure it out. Even tried using a Select case. That was even
worse
as I couldn't figure that out even to start. I have been working on this
over
a week now, and could really use some help on this.
C_Ascheman
:
There seems to be some confusion here about what Access is actually
doing.
Firstly, any date/time field is stored within Access as a number, where
the
integer part represents the date and the fraction part the time (part of
a
day). The Format merely affects how the date is displayed, not how it is
stored. All dates are stored with day and month and year, regardless of
how
you display them.
Secondly, if the text box in the Form Header is bound to a date/time
field
in your table, then it *does* relate to the current record in the form.
There is no such thing as a bound control that stores a value that does
not
relate to a specific record.
A third problem is that if you have a field named Date in the detail
section
of your form, you are using a reserved word (for the System date.) In
some
contexts, Access will misunderstand this name, and give you the results
for
today instead of for the date value in this field.
If the the date boxes in the Form Header exist only to limit the records
in
the form, you might use unbound boxes (Control Source property is blank,
values are not saved in any field), and filter the form to values for the
month.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
At the top of my form I have 2 boxes ([Month_End_Date] and
[Month_End_Total]). The [Month_End_Date] is saved into the DB as
mm/yyyy
format. These 2 boxes are in the Form Header part of the form as I
don't
want
it populating the form multiple times (Form is in Tabular format). In
the
Detail section of the form is several other text boxes. When the user
pulls
up the form by say date (beggining / ending dates) it populates the
form
with
all records that meet the criteria.
The problem I am having is that the [Date] text box in the Detail part
of
the form is in mm/dd/yyyy format. I am trying to program the form so
that
if
the date in in the [Date] text box is say 02/##/2006 (## being any day)
that
it places 01/2006 in the [Month_End_Date] text box of the Form Header
section, and the places the correct total in the [Month_End_Total] text
box
where the [Month_End_Date] corresponds with one from the Database.
Though
if
the [Date] is 01/##/2006 then the [Month_End_Date] is empty and the
[Month_End_Total] is 0.
Any help / advice will be greatly appreciated.
C_Ascheman
