if statement and dates

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

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
 
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
Click to expand...

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.
 
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?
 
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/####
Click to expand...

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?
 
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
 
Not to confuse you too much but let me explain teh form layout a
little.

I have a combobox cboxyear that lists 1998-2009
Click to expand...

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?
Click to expand...

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
Click to expand...

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
 
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]
Click to expand...
Click to expand...
 
Back
Top