Avatar of Fordraiders
FordraidersFlag for United States of America

asked on 

Need to determine if a new quarter has started and check another table for validation

Need to determine if a new quarter has started and check another table for validation

I have a form with a dropdown of names.
After i choose a name i need to perform a Date/quarter calculation.
and compare a value in another table.

quarter_table
fields:
id-autonumber
associate_name - text
q1 - text default  =  no
q2 - text default  =  no
q3 - text default  =  no
q4   - text default  =  no

So after a name has been chosen
What I need:
I need a function to check the Current date(todays date)  and tell me the current quarter, q1,q2,q3,q4

If it is say "april 6th 2020"...the value to return would be "Q2"
Next:
so then i need to check the table "quarter_table" for that person. Look at the Column q2 and see if the entry is  "NO"
if it is
need a message box to display  "q2 = no"


I have a textbox on the form to tell me what
  quarter it is:
Private Sub Text97_GotFocus()
Dim str As String
str = Format(Now, "q")

With Me.Text97
.Locked = False
If str = "1" Then
     Me.Text97.Text = "Q1"
  End If  

If str = "2" Then
     Me.Text97.Text = "Q2"
  End If

If str = "3" Then
     Me.Text97.Text = "Q3"
  End If

If str = "4" Then
     Me.Text97.Text = "Q4"
  End If



.Locked = True
End With
End Sub




Thanks
fordraiders
Microsoft AccessVBA

Avatar of undefined
Last Comment
Gustav Brock
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

if your quarters are standard (Jan to Mar=1), you can use Val(Format(myDate,"q")) or DatePart("q", myDate) to get the quarter as a number

Otherwise, add or subtract from the date and use this method

this will give you Q1, Q2, ...
Format(myDate,"\Qq")
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Well you can go as this :
DatePart("q",date)

Open in new window

Avatar of Fordraiders
Fordraiders
Flag of United States of America image

ASKER

thanks all !!  as always. stay safe.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You are welcome!
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo