"If Date" in MS Access

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.

Select allOpen in new window

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

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

Select allOpen in new window

...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

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.

...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...

<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

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

Then what mbizup posted here should get you on your way...

Try it out, then let here know...

Jeff

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

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

You probably need one test only:

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

/gustav

<You probably need one test only:>
what do you mean?

Shannon

?? I wrote the complete expression/test? What else do you need?

/gustav

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

Thank you all for your help.
V/R,
Shannon