Solved

# "If Date" in MS Access

Posted on 2013-09-26
Medium Priority
458 Views
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
0
Question by:ShannonCallahan
• 5
• 4
• 3
• +2

LVL 61

Expert Comment

ID: 39525214
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.
``````
0

LVL 61

Expert Comment

ID: 39525234
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

LVL 29

Expert Comment

ID: 39525312
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
``````
0

LVL 74

Expert Comment

ID: 39525418
...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

LVL 61

Expert Comment

ID: 39525456
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

LVL 74

Expert Comment

ID: 39525462
...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

LVL 74

Expert Comment

ID: 39525480
<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

LVL 1

Author Comment

ID: 39525721
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

LVL 74

Expert Comment

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

Try it out, then let here know...

Jeff
0

LVL 53

Expert Comment

ID: 39526923
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

LVL 1

Author Comment

ID: 39527585
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

LVL 53

Expert Comment

ID: 39527603
You probably need one test only:

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

/gustav
0

LVL 1

Author Comment

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

Shannon
0

LVL 29

Assisted Solution

IrogSinta earned 2000 total points
ID: 39527632
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

LVL 53

Expert Comment

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

/gustav
0

LVL 1

Author Comment

ID: 39527689
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

LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 39527700
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

LVL 1

Author Closing Comment

ID: 39527729
Thank you all for your help.
V/R,
Shannon
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This article describes and provides a custom-made tool I wrote to give businesses a means of identifying commercial music content, without having to expend too much effort. Business recordings are easily identified from possibly illegal music files …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
###### Suggested Courses
Course of the Month15 days, 1 hour left to enroll

#### 597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.