"If Date" in MS Access

Good morning experts,
I want to be able to change the background color of a combobox based off its selected value compared to the current calendar quarter. something like:

If combobox.value = "1st Quarter" AND current quarter is not 1 then
combobox.backcolor = vbRed
elseif
combobox.value = "2nd Quarter" AND current quarter is not 2 then
combobox.backcolor............ and so on.

Please let me know if you need further information.
Thank you.
V/R,
Shannon
LVL 1
ShannonCallahanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try something like this:

Dim intQtr as integer

intQtr = int(Month(Date()))/4 + 1

If combobox.value = "1st Quarter" AND intQtr <>  1 then
combobox.backcolor = vbRed
elseif 
combobox.value = "2nd Quarter" AND intQtr <> 2 then
combobox.backcolor............ and so on.

Open in new window

0
mbizupCommented:
Btw, you can also use conditional formatting:

- Right click the combo box in design view and select conditional formatting.

- Use ExpressionIS formatting to build your conditions

If you are using Access 2010, you can have more than three conditions, whose expressions you can specify in this format, picking different background colors for each condition:

[combobox] = "First Quarter" AND (int(Month(Date()))/4 + 1) <> 1

[combobox] = "Second Quarter" AND (int(Month(Date()))/4 + 1) <> 2

etc
0
IrogSintaCommented:
Here's another way if you only have the 4 choices in your combo box.
    If Me.ComboBox.ListIndex + 1 <> Format(DATE, "q") Then
        Me.ComboBox.backColor = vbRed
    Else
        Me.ComboBox.backColor = vbWhite
    End If

Open in new window

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jeffrey CoachmanMIS LiasonCommented:
...I am just curious?

<combobox.value = "2nd Quarter" AND current quarter is not 2 then>
Why would selecting a value NOT produce the correct "quarter"?

If your combobox row source is a table with both values:
tblQuarters
qID
qName

ex:
qID    qName
1        First Quarter
2        Second Quarter
3        Third Quarter
4        Fourth Quarter

...Selecting "Second Quarter" would always produce a 2 as the value

Then you can use this (2) for your "Current Quarter" Value
me.CurrentQuarter=YourCombobox.Column(1)

...Thus bypassing the need for this system...

JeffCoachman
0
mbizupCommented:
Jeff,

I initially wondered the same thing, but I think existing records probably hold older data whose quarter as displayed in the combo needs to be compared to the quarter corresponding to today's date.
0
Jeffrey CoachmanMIS LiasonCommented:
...or if the (2) comes from an external source, you can "Lookup" the correct text without needing a combobox.

    YourQuatretText=Dlookup("qName","tblQuarters","qID=" & me.QuarterValue)
This says:
Find the qID in tblQuarters where the QID is whatever values you have in the "QuarterValue" field/textbox...
0
Jeffrey CoachmanMIS LiasonCommented:
<I think existing records probably hold older data whose quarter as displayed in the combo needs to be compared to the quarter corresponding to today's date.>

good point...

;-)

Jeff
0
ShannonCallahanAuthor Commented:
First, thank you all for your comments. I am trying to take the advice given to me by Jeff a couple days ago about simplifying this project. With this in mind I would like to use the conditional formating and stay away from VBA. Let me try to explain this better.

I have a combobox that I typed in the values I wanted:

1st
2nd
3rd
4th

These values represent the quarter a specific training was completed. I want the back color of the combobox to change red if the selected quarter is not the current quarter per date(month()). Example: This month is Sept (falls under 3rd calendar quarter) and if I have any value other than "3rd" is selected, then it will automatically turn red.
Thanks again,
V/R,
Shannon
0
Jeffrey CoachmanMIS LiasonCommented:
Then what mbizup posted here should get you on your way...

Try it out, then let here know...

Jeff
0
Gustav BrockCIOCommented:
As DatePart returns a number, I would do like this:
   
    Dim lngBackColor As Long
    If Me!ComboBox.ListIndex = DatePart("q", Date) - 1 Then
        lngBackColor = vbWhite
    Else
        lngBackColor = vbRed
    End If
    Me!ComboBox.BackColor = lngBackColor

/gustav
0
ShannonCallahanAuthor Commented:
So I tried the conditional formating:

[cboCAPEQuarter].Value="1st" And (Int(Month(Date()))/4+1)<>1
[cboCAPEQuarter].Value="2nd" And (Int(Month(Date()))/4+1)<>2
[cboCAPEQuarter].Value="3rd" And (Int(Month(Date()))/4+1)<>3
[cboCAPEQuarter].Value="4th" And (Int(Month(Date()))/4+1)<>4

the problem is, no matter whick value I select, the combobox is always green. Any suggestions?
V/R,
Shannon
0
Gustav BrockCIOCommented:
You probably need one test only:

Val(Nz([cboCAPEQuarter].Value,0))=DatePart("q",Date())

/gustav
0
ShannonCallahanAuthor Commented:
<You probably need one test only:>
what do you mean?

Shannon
0
IrogSintaCommented:
In Conditional Formatting, add just one condition.  Set it to Expression Is, and insert either of these for the expression:
[cboCAPEQuarter].ListIndex+1=Format(Date(),"q")
[cboCAPEQuarter].ListIndex+1=DatePart("q",Date())
0
Gustav BrockCIOCommented:
?? I wrote the complete expression/test? What else do you need?

/gustav
0
ShannonCallahanAuthor Commented:
Thank you all for your help,

[cboCAPEQuarter].ListIndex+1=DatePart("q",Date())

That was what I was looking for. The only issue that I am having is I want the combobox to change to red if it is not correct. I would just set the combobox's back color to red but it changes the color of the drop down options backcolor as well.

V/R,
Shannon
0
IrogSintaCommented:
Change the = sign to <>.  Again, you can use either Format function or DatePart function.

[cboCAPEQuarter].ListIndex+1<>Format(Date(),"q")
[cboCAPEQuarter].ListIndex+1<>DatePart("q",Date())
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShannonCallahanAuthor Commented:
Thank you all for your help.
V/R,
Shannon
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.