date value issue

[kaosyeti]

i have a report that tracks customer contacts by salespeople at a car
dealership. the contacts are assigned several values, one of which is the
date that they came in. the report is capeable of being run to any date
range specified by the user, which is a key feature for training of new
employees (ergo, changing this is not an option). the report is based on a
table of these contacts but in a few of its controls, does some dlookup,
dcount and dsum expressions to pull data from a table of sold cars. the
problem is this. the table of sold cars is created with monthly data. it is
not important for my facility to track the DATE the car was sold for the
purposes of this report. most of the time, in fact, the report will be run
on a whole month's data at one time. when this happens, the d-functions will
pull the # of sales for each salesperson for that month. if it's a 3 month
period of time, it works just as well as 1 month or 20 months. So here's the
problem: if someone wanted to run a report for a period of time that is not
either from the first day of a month or ending at the last day of a month,
how can i set up the visible property of some of these expressions to be
false? in more detail:

if txtboxstartdate = 10-1-05 and txtboxenddate = 10-31-05 then these controls
should be visible.
if txboxstartdate = 05-1-03 and txtboxenddate = 10-31-05 then these controls
should be visible.
if txtboxstartdate = 11-15-05 or txtboxenddate = 12-4-05 then these controls
should not be visible.

basically, how do you write an expression that evaluates two text boxs that
are formatted as mm/dd/yyyy to be whole month values or not? sorry if this
was wordy. thanks for looking.

--
Message posted via http://www.accessmonster.com

[Tom van Stiphout]

On Sat, 03 Dec 2005 16:21:16 GMT, "ka******@comca st.net via
AccessMonster.c om" <u15580@uwe> wrote:

Depending on your exact requirements, you could write some code in the
Report_Open event or the Report_Format event. The first if the
visibility is set based on the criteria specified by the user, the
second if visibility is based on the date for the particular row that
is being formatted.
Code similar to:
private sub SetVisibility(b yval blnVisible as boolean)
dim ctlVisibility as Variant 'List of controls to be toggled
dim ctl as variant
ctlVisibility = Array("txtOne", "btnTwo", "txtThree")
for each ctl in ctlVisibility
ctl.Visible = blnVisible
next ctl
end sub

Call this sub using:
if (SomeCondition) then
SetVisibility True
else
SetVisibility False
end if

Or somewhat more concise:
SetVisibility SomeCondition
I don't exactly get the thrust of your last question. If you want to
know what month a particular date is in, just call the Month function.
If you want to know if two text fields have dates that span exactly
one month, then you could write code similar to:
if Day(Forms!MyFor m!MyBeginDate)= 1 and
Day(DateAdd("d" ,1,Forms!MyForm !MyEndDate))=1 then
'it's exactly 1 month
(Note how 1 day after the last day of a month is day 1)

-Tom.

i have a report that tracks customer contacts by salespeople at a car
dealership. the contacts are assigned several values, one of which is the
date that they came in. the report is capeable of being run to any date
range specified by the user, which is a key feature for training of new
employees (ergo, changing this is not an option). the report is based on a
table of these contacts but in a few of its controls, does some dlookup,
dcount and dsum expressions to pull data from a table of sold cars. the
problem is this. the table of sold cars is created with monthly data. it is
not important for my facility to track the DATE the car was sold for the
purposes of this report. most of the time, in fact, the report will be run
on a whole month's data at one time. when this happens, the d-functions will
pull the # of sales for each salesperson for that month. if it's a 3 month
period of time, it works just as well as 1 month or 20 months. So here's the
problem: if someone wanted to run a report for a period of time that is not
either from the first day of a month or ending at the last day of a month,
how can i set up the visible property of some of these expressions to be
false? in more detail:

if txtboxstartdate = 10-1-05 and txtboxenddate = 10-31-05 then these controls
should be visible.
if txboxstartdate = 05-1-03 and txtboxenddate = 10-31-05 then these controls
should be visible.
if txtboxstartdate = 11-15-05 or txtboxenddate = 12-4-05 then these controls
should not be visible.

basically, how do you write an expression that evaluates two text boxs that
are formatted as mm/dd/yyyy to be whole month values or not? sorry if this
was wordy. thanks for looking.

[kaosyeti]

as it turns out, i couldn't use your or any other suggestion i found on this
site because my question was asked wrong. i didn't properly explain that the
date range in question must start on the first day of any month and end of
the LAST day of any month for my db to consider it to be a whole month.
because of the nature of my data, the range of the 1st to the 1st is no good.
so here's what i came up with.

If Day(Forms!formq uerybuilder.Con trols!txtboxsta rtdate) = 1 Then
If Day(DateAdd("d" , 1, Forms!formquery builder.Control s!
txtboxenddate)) <> 1 Then
Me.Text205.Visi ble = False
Me.Text206.Visi ble = False
Me.Text207.Visi ble = False
Me.Text208.Visi ble = False
Me.Text209.Visi ble = False
Me.Text210.Visi ble = False
Me.Text211.Visi ble = False
Me.Text212.Visi ble = False
End If
End If

now, i know it's ugly but it works and i didn't want to ignore your answer.
if you can see a flaw in what i did (besides being unnecessarily cumbersome --
but i hardly know vba at all so i'll live) please say so and i'll fix it.
i'm not proud. thanks for your time.
Tom van Stiphout wrote:

Depending on your exact requirements, you could write some code in the
Report_Open event or the Report_Format event. The first if the
visibility is set based on the criteria specified by the user, the
second if visibility is based on the date for the particular row that
is being formatted.
Code similar to:
private sub SetVisibility(b yval blnVisible as boolean)
dim ctlVisibility as Variant 'List of controls to be toggled
dim ctl as variant
ctlVisibilit y = Array("txtOne", "btnTwo", "txtThree")
for each ctl in ctlVisibility
ctl.Visible = blnVisible
next ctl
end sub

Call this sub using:
if (SomeCondition) then
SetVisibility True
else
SetVisibility False
end if

Or somewhat more concise:
SetVisibilit y SomeCondition

I don't exactly get the thrust of your last question. If you want to
know what month a particular date is in, just call the Month function.
If you want to know if two text fields have dates that span exactly
one month, then you could write code similar to:
if Day(Forms!MyFor m!MyBeginDate)= 1 and
Day(DateAdd("d ",1,Forms!MyFor m!MyEndDate))=1 then
'it's exactly 1 month
(Note how 1 day after the last day of a month is day 1)

-Tom.

i have a report that tracks customer contacts by salespeople at a car
dealership. the contacts are assigned several values, one of which is the

[quoted text clipped - 24 lines]

are formatted as mm/dd/yyyy to be whole month values or not? sorry if this
was wordy. thanks for looking.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200512/1