Date must be 7th or 21st of month (date formatting)

TallMan

Registered User.
Local time
Today, 05:47
Joined
Dec 5, 2008
Messages
239
Good evening,

I have a field where the associate has to enter a date. there are two possible dates that they can pick from each month. the 7th and the 21st.

Does anyone know a way to say

If me.start_Date <> "**/07/****" or me.start_Date <> "**/21/****" then
msgbox "blah blah blah"

I am not sure of the formatting.
 
Code:
If Me.start_Date <> DateSerial(Year(Date(),Month(Date()),7) Or Me.startDate <> DateSerial(Year(Date(),Month(Date()),21) Then
   MsgBox "Blah, Blah"
End If
Etc.
 
Hey Bob,

I appreciate you help but I did not have much luck with that. I also have another question for you. I have this code set up so that the associate must enter the majority of the text boxes or else a message box comes up asking them to make sure all fields are set up. I am having an issue where the associate forgets to add two text boxes. The first time they try to save the information the msgbox comes up asking the to make sure all fields are set up. Then the second time they still miss a text box and it allows the information to be saved. it is almost the the code does not know to start over from the begging. Does this make sense?


Thanks again Bob!:o



PHP:
Private Sub Command42_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
        
        If Len(Me.PIP_Account) <> 8 Then
            MsgBox "Please enter an account number to update", vbInformation, "No Account"
                Else
                    If Me.Starts_On_PIP < Date Then
                        MsgBox "The start date you entered is prior to todays date. Please enter a date that is after todays date.", vbInformation, "End Date"
                        Me.Starts_On_PIP = ""
                            [Forms]![PIP_INFO_FORM]![Starts_On_PIP].SetFocus
                                Else
                                If me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),7) Or Me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),21) Then
                                MsgBox "Please make sure the date you PIP you enter starts on either the 7th or 21st of the month"
                                    If Me.Amount_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Date_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Month_PIP = "" Or Me.Starts_On_PIP = "" Then
                                      MsgBox "Please enter all information relating to this PIP Set-up"
                                        Else
                                    If Me.Ends_On_PIP < Date Then
                                         MsgBox "The end date you entered is prior to todays date. Please enter a date that is after todays date.", vbInformation, "End Date"
                                         Me.Ends_On_PIP = ""
                                        [Forms]![PIP_INFO_FORM]![Ends_On_PIP].SetFocus
                                             Else
                                                 If Me.Ends_On_PIP < Me.Starts_On_PIP Then
                                                 MsgBox "You end date is less than your start date", vbInformation, "Dates"
                                                 Me.Ends_On_PIP = ""
                                                 [Forms]![PIP_INFO_FORM]![Ends_On_PIP].SetFocus
                            Else
                                Me.User_Name_PIP = fOSUserName
                                Me.Date_Add_PIP = Now()
                                sqlinsert = "insert into Main_PIP_TBL (Account, Amount, PPM_Code, Pay_Date, Pay_Month, Starts_on, Ends_on, Funding, Add_Date, Associate_Name) values('" & Me.PIP_Account & "', '" & Me.Amount_PIP & "','" & Me.PPM_Code_PIP & "','" & Me.Pay_Date_PIP & "','" & Me.Pay_Month_PIP & "','" & Me.Starts_On_PIP & "','" & Me.Ends_On_PIP & "','" & Me.Funding_PIP & "','" & Me.Date_Add_PIP & "','" & Me.User_Name_PIP & "');"
                                db.Execute (sqlinsert)
                                    MsgBox "updated", vbInformation, "Update"
                                    Refresh
                                    Me.Amount_PIP = ""
                                    Me.PPM_Code_PIP = ""
                                    Me.Pay_Date_PIP = ""
                                    Me.Pay_Month_PIP = ""
                                    Me.Starts_On_PIP = ""
                                    Me.Ends_On_PIP = ""
                                    Me.Funding_PIP = ""
                                    Me.Date_Add_PIP = ""
                                    Me.User_Name_PIP = ""
                                End If
                            End If
                            End If
                        End If
                  End If
            End If
            SQL = " select * from Main_PIP_TBL where account = '" & Me.PIP_Account & "';"
            Me.Main_PIP_TBL_subform.Form.RecordSource = SQL
   End Sub
 
I am a little confused. The code that I have posted is to execute when the associate clicks the button.

basically they enter information into 6 text boxes and then click the "Add PIP" button.
 
You need to include an escape if any message box happens, before the update:

Exit Sub

you can have it after each of the message boxes, or you can have it at the end just before the update. For one, I would not have so many message boxes. I would look through the fields to see if all is filled out and if not I would add that one to a list that then can be listed at the end so they can go fix it all in one shot.

Sort of like this (BE AWARE: I did not include all of the IF's in this code, I just did enough to hopefully get the point across):

PHP:
Option Compare Database
Option Explicit

Private Sub Command42_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim strMessage As String
    Dim blnError As Boolean

    If Len(Me.PIP_Account) <> 8 Then
        strMessage = "Account Number" & vbCrLf
        blnError = True
    End If

    If Me.Starts_On_PIP < Date Then
        strMessage = strMessage & "Date Entered Prior to Today's Date" & vbCrLf
        blnError = True
    End If

    If me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),7) Or Me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),21) Then
        strMessage = strMessage & "PIP start date must be 7th or 21st" & vbCrLf
        blnError = True
    End If

    If Me.Amount_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Date_PIP = "" Or Me.PPM_Code_PIP = "" Or Me.Pay_Month_PIP = "" Or Me.Starts_On_PIP = "" Then
        strMessage = strMessage & "Information relating to this PIP Set-up" & vbCrLf
        blnError = True
    End If

    If Me.Ends_On_PIP < Date Then
        strMessage = strMessage & "End date is prior to today's date"
        blnError = True
    End If

    If blnError Then
        strMessage = "These errors need to be fixed before proceeding " & vbCrLf & strMessage
        MsgBox strMessage, vbExclamation, "Errors on form"
        Exit Sub
    End If

    Me.User_Name_PIP = fOSUserName
    Me.Date_Add_PIP = Now()
    sqlinsert = "insert into Main_PIP_TBL (Account, Amount, PPM_Code, Pay_Date, Pay_Month, Starts_on, Ends_on, Funding, Add_Date, Associate_Name) values('" & Me.PIP_Account & "', '" & Me.Amount_PIP & "','" & Me.PPM_Code_PIP & "','" & Me.Pay_Date_PIP & "','" & Me.Pay_Month_PIP & "','" & Me.Starts_On_PIP & "','" & Me.Ends_On_PIP & "','" & Me.Funding_PIP & "','" & Me.Date_Add_PIP & "','" & Me.User_Name_PIP & "');"
    db.Execute (sqlinsert)
    MsgBox "updated", vbInformation, "Update"
    Me.Amount_PIP = ""
    Me.PPM_Code_PIP = ""
    Me.Pay_Date_PIP = ""
    Me.Pay_Month_PIP = ""
    Me.Starts_On_PIP = ""
    Me.Ends_On_PIP = ""
    Me.Funding_PIP = ""
    Me.Date_Add_PIP = ""
    Me.User_Name_PIP = ""
    SQL = " select * from Main_PIP_TBL where account = '" & Me.PIP_Account & "';"
    Me.Main_PIP_TBL_subform.Form.RecordSource = SQL
End Sub
 
Last edited:
I see where you are going with this. I think this makes it a lot cleaner and potentially less annoying for the user. This way they are not constantly getting msgbox pop-ups.

I am still unable to get the date format to work that you suggested.
it is just showing in red.

PHP:
  If me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),7) Or Me.Starts_On_PIP <> DateSerial(Year(Date(),Month(Date()),21) Then
       strMessage = strMessage & "PIP start date must be 7th or 21st" & vbCrLf
       blnError = True
  End If


I have the input mask of the text box as "short date" does this matter?
 
Actually, there is a right paren missing )

just after

Year(Date()

in each of the formula fields so it needs to be:

If me.Starts_On_PIP <> DateSerial(Year(Date()),Month(Date()),7) Or Me.Starts_On_PIP <> DateSerial(Year(Date()),Month(Date()),21) Then
strMessage = strMessage & "PIP start date must be 7th or 21st" & vbCrLf
blnError = True
End If
 
Got it to work=)

This hopefully will be my last question for you.:(

I am still havin the issue where when the associate/me enters in information, then I get the message box with a list of things wrong, if I enter only one of them it still updates.

For example if I forget to enter in the amount and enter in a bad date. the msgbox will tell me you

-did not fill all the information
-You date needs to be either on the 7th ot 21st

when I go back and only correfct the date and hit submit it accepts it and updates it does not realize that all the information is still not correct.

Is there a way I can have the code start back at the top so that it re-runs all of the IF statements?

Does this make sense>?
 
Set a breakpoint and look at all of the values. When you click the button, no matter how many times, every time you have a DIM statement, it resets everything. So, I'm not sure where it is failing you, but if you set a breakpoint and then F8 through the code you might spot where it is failing.
 
Hi -

See if any of the logic in this query (based on Northwind's Orders table) would be of assistance:

Code:
SELECT DISTINCT Orders.*
FROM Orders
WHERE (((InStr(["7,21"],Val(Day([OrderDate]))))>0) AND ((Day([OrderDate])) In (7,21)));

Bob
 
surely its just

if day(indate)<>7 and day(indate)<>21 then
msgbox "you must pick 7th or 21st of a month
exit sub (or whatever)
end if
 

Users who are viewing this thread

Back
Top Bottom