if statement and dates

[ryan.fitzpatrick3]

is there away to program if statements too coincide with dates. for
example I have a combobox (cboxQuarter) with Q1,Q2,Q3,Q4 as values. I
have 2 other cboxes, cboxstartdate and cboxenddate where a person can
input which ever date they want into each and run the query and the
report will only show the data between those dates. (this is what I
want it doesn't work yet)

I'd like these calendar quarters to program a query to pull the dates
that go to each quarter, i.e. if I run Q1 then it'll run 1-1-2009 to
3-31-09. how would I go about this?

Do I put if statements in vba if me.cboxQuarter = Q1 then
me.cboxstartdate = 1-1-2009 and me.cboxenddate = 3-31-2009 else if
me.cboxQuarter = Q2, etc etc.

Thanks,

ryan

 

[John W. Vinson]

is there away to program if statements too coincide with dates. for
example I have a combobox (cboxQuarter) with Q1,Q2,Q3,Q4 as values. I
have 2 other cboxes, cboxstartdate and cboxenddate where a person can
input which ever date they want into each and run the query and the
report will only show the data between those dates. (this is what I
want it doesn't work yet)

I'd like these calendar quarters to program a query to pull the dates
that go to each quarter, i.e. if I run Q1 then it'll run 1-1-2009 to
3-31-09. how would I go about this?

Do I put if statements in vba if me.cboxQuarter = Q1 then
me.cboxstartdate = 1-1-2009 and me.cboxenddate = 3-31-2009 else if
me.cboxQuarter = Q2, etc etc.

Thanks,

ryan

I'd suggest basing cboxQuarter on a little two field table with data

Q1;1
Q2;4
Q3;7
Q4;10

Make the second column the bound column, but set its ColumnWidth to 0 to
conceal it.

and put code in its AfterUpdate event like:

Private Sub cboxQuarter_AfterUpdate
Me!cboxStartDate = DateSerial(Year(Date()), Me!cboxQuarter, 1)
Me!cboxEndDate = DateSerial(Year(Date()), Me!cboxQuarter+3, 0)
End Sub

to explicitly push the quarter dates into the start and end dates. They can of
course be overridden by the user.

 

[ryan.fitzpatrick3]

thank you for this, but it didnt seem to help, do I program a the
dates in the date() part of the code? would I use a case select
statement whereas
Q1 = 1/1/#### - 3/31/####
Q2 = 4/1/#### - 6/30/####
etc
etc

how should I go about this?

 

[John W. Vinson]

thank you for this, but it didnt seem to help, do I program a the
dates in the date() part of the code? would I use a case select
statement whereas
Q1 = 1/1/#### - 3/31/####
Q2 = 4/1/#### - 6/30/####

Try

BETWEEN DateSerial(Year(Date()), Forms!YourFormName!cboQuarter, 1) AND
DateSerial(Year(Date()), Forms!YourFormName!cboQuarter) + 1, 0)

as a criterion in your query. This assumes that you always want the current
year - if you run the query in January for Q4 will you want last year? If you
run it for Q2 will you want the coming quarter or the past one?

 

[ryan.fitzpatrick3]

Not to confuse you too much but let me explain teh form layout a
little.

I have a combobox cboxyear that lists 1998-2009
I have a combobox cboxQuarter that lists Q1-Q4

A user can select the cboxyear and select 2009 and all 2009 volumes
and spend will filter on the form.

I would like it where if a user picks Q1 in cboxquarter that the
filtered information will default to current year and pull all volumes
and spends between 1/1/2009 and 3/31/2009. But if they pick Q1 and
select 2008 in cboxyear then it'll pull ranges between 1/1/2008 and
3/31/2008 or whatever year they decide. this possible?

I was thinking an if statement:
I have MondayYear as the field that has the dates in the table

I made this up, it's probably totally wrong, but hopefully it'll give
insight on what i'm trying to do.

Private Sub cboxQuarter_AfterUpdate()
Dim strWhere As String
If Nz(Me!cboxQuarter, 0) = 0 Then

strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(Year(Date()), Forms!YourFormName!cboQuarter, 1) & ") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(Year(Date()), Me!cboxQuarter +3, 0) & ") AND "

Else
strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(01/01/ & (me.cboxYear), Forms!YourFormName!cboQuarter, 1) &
") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(03/31/ &(me.cboxYear), Me!cboxQuarter +3, 0) & ") AND "

End If
End Sub

 

[John W. Vinson]

Not to confuse you too much but let me explain teh form layout a
little.

I have a combobox cboxyear that lists 1998-2009

Set its Default Value property to Year(Date()).

I have a combobox cboxQuarter that lists Q1-Q4

A user can select the cboxyear and select 2009 and all 2009 volumes
and spend will filter on the form.

I would like it where if a user picks Q1 in cboxquarter that the
filtered information will default to current year and pull all volumes
and spends between 1/1/2009 and 3/31/2009. But if they pick Q1 and
select 2008 in cboxyear then it'll pull ranges between 1/1/2008 and
3/31/2008 or whatever year they decide. this possible?

You have not mentioned anything about a Form Filter in this thread, which is
why I was assuming you were building a query. Sorry about the confusion!

I was thinking an if statement:
I have MondayYear as the field that has the dates in the table

I made this up, it's probably totally wrong, but hopefully it'll give
insight on what i'm trying to do.

Private Sub cboxQuarter_AfterUpdate()
Dim strWhere As String
If Nz(Me!cboxQuarter, 0) = 0 Then

strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(Year(Date()), Forms!YourFormName!cboQuarter, 1) & ") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(Year(Date()), Me!cboxQuarter +3, 0) & ") AND "

Else
strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(01/01/ & (me.cboxYear), Forms!YourFormName!cboQuarter, 1) &
") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(03/31/ &(me.cboxYear), Me!cboxQuarter +3, 0) & ") AND "

End If
End Sub

Well, that's totally bogus I fear... <g>

The DateSerial function takes three integer arguments, a number referring to
a year (either 9 or 2009 for this year), a month (a number 1 to 12) and a day.
You're giving it a fraction, 3 divided by 31 divided by 2009. And your
strWhere clause is going to end with " AND ", making it invalid... which
doesn't much matter because your code does nothing with it anyway!

Try:

Dim strFilter As String
If IsNull(Me!cboxYear) Then ' return all records
Me.Filter = ""
Me.FilterOn = False
Else
' see if the user specified a quarter
If IsNull(Me!cboxQuarter) Then
Me.Filter = "[MonDayYear] >= #" & DateSerial(Me!cboxYear, 1, 1) & _
"# AND [MonDayYear] < #" & DateSerial(Me!cboxYear + 1, 1, 1) & "#"
Else
Me.Filter = "[MonDayYear] >= #" & DateSerial(Me!cboxYear,_
Me!cboxQuarter, 1) & _
"# AND [MonDayYear] < #" & DateSerial(Me!cboxYear + 1, _
Me!cboxQuarter + 3, 1) & "#"
End If
Me.FilterOn = True
End If

 

[Mike Painter]

Format the date as just a year in one calculated field
yearDate:year(yourDate) and as a quarter in another
QrtrDate:format([yourdate],"q")
Select for the year and the quarter.

Not to confuse you too much but let me explain teh form layout a
little.

I have a combobox cboxyear that lists 1998-2009
I have a combobox cboxQuarter that lists Q1-Q4

A user can select the cboxyear and select 2009 and all 2009 volumes
and spend will filter on the form.

I would like it where if a user picks Q1 in cboxquarter that the
filtered information will default to current year and pull all volumes
and spends between 1/1/2009 and 3/31/2009. But if they pick Q1 and
select 2008 in cboxyear then it'll pull ranges between 1/1/2008 and
3/31/2008 or whatever year they decide. this possible?

I was thinking an if statement:
I have MondayYear as the field that has the dates in the table

I made this up, it's probably totally wrong, but hopefully it'll give
insight on what i'm trying to do.

Private Sub cboxQuarter_AfterUpdate()
Dim strWhere As String
If Nz(Me!cboxQuarter, 0) = 0 Then

strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(Year(Date()), Forms!YourFormName!cboQuarter, 1) & ") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(Year(Date()), Me!cboxQuarter +3, 0) & ") AND "

Else
strWhere = strWhere & "([MonDayYear] >= " & Format(Me.tboxStartDate,
DateSerial(01/01/ & (me.cboxYear), Forms!YourFormName!cboQuarter, 1) &
") AND "
strWhere = strWhere & "([MonDayYear] <= " & Format(Me.tboxEndDate,
DateSerial(03/31/ &(me.cboxYear), Me!cboxQuarter +3, 0) & ") AND "

End If
End Sub

Try

BETWEEN DateSerial(Year(Date()), Forms!YourFormName!cboQuarter, 1)
AND DateSerial(Year(Date()), Forms!YourFormName!cboQuarter) + 1, 0)

as a criterion in your query. This assumes that you always want the
current year - if you run the query in January for Q4 will you want
last year? If you run it for Q2 will you want the coming quarter or
the past one? --

John W. Vinson [MVP]