Good Morning All,
I have a tricky one here, at least it is tricking me. It is a rather simple concept but cannot get this to work 100%. I will try to make this as short as possible.
In my VBA code below. I first check what day it is. If it is any day other than Friday then the query pulls date() + 7 from the "paydate field". If the Day is Friday then I pull dat() +7, Date()+8 and Date()+9 from the paydate field.
After I establish which days to pull I find out what month we are in so that I can pull the appropriate records from the "Pay_Month" field.
The code is running fine. It finds the day, then pulls the information and exports it to excel.
The problem is the code is pullling ALL of the records for the day I am looking for. For example, today is the 18th and it is not Friday so the query should pull all the records that have a day of 25. The query is skipping the "Pay_month" field and pulling every record with a 25. Depending on what month we are in I should only be pulling certain month records.
****Here is the really tricky part. When I populate a msgbox with the sql code it pulls the exact criteria I need, however after the query executes and populates the spreadsheet all of the monthly records are there!
Does this make sense? PLease post back with questions.
<FONT size=3><FONT face=Calibri>
I have a tricky one here, at least it is tricking me. It is a rather simple concept but cannot get this to work 100%. I will try to make this as short as possible.
In my VBA code below. I first check what day it is. If it is any day other than Friday then the query pulls date() + 7 from the "paydate field". If the Day is Friday then I pull dat() +7, Date()+8 and Date()+9 from the paydate field.
After I establish which days to pull I find out what month we are in so that I can pull the appropriate records from the "Pay_Month" field.
The code is running fine. It finds the day, then pulls the information and exports it to excel.
The problem is the code is pullling ALL of the records for the day I am looking for. For example, today is the 18th and it is not Friday so the query should pull all the records that have a day of 25. The query is skipping the "Pay_month" field and pulling every record with a 25. Depending on what month we are in I should only be pulling certain month records.
****Here is the really tricky part. When I populate a msgbox with the sql code it pulls the exact criteria I need, however after the query executes and populates the spreadsheet all of the monthly records are there!
Does this make sense? PLease post back with questions.
<FONT size=3><FONT face=Calibri>
PHP:
Private Sub Command140_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim sqldaily As String
Dim PPY_Excel As Object
Set PPY_Excel = CreateObject("Excel.Application")
'----------------------
Dim criteria1 As String
Dim criteria2 As String
If Weekday(Date) = 6 Then
criteria1 = Day(Date + 7) & " OR " & Day(Date + 8) & " OR " & Day(Date + 9)
Else
criteria1 = Day(Date + 7)
End If
If Month(Date) = "1" Then
criteria2 = "'Monthly' Or 'Jan-April-July-Oct' OR 'Jan-Annual'"
ElseIf Month(Date) = "2" Then
criteria2 = "'Monthly' Or 'Feb-May-Aug-Nov' Or 'Feb-Annual'"
ElseIf Month(Date) = "3" Then
criteria2 = "'Monthly' Or 'March-June-Sept-Dec' Or 'March-Annual'"
ElseIf Month(Date) = "4" Then
criteria2 = "'Monthly' Or 'Jan-April-July-Oct' Or 'April-Annual'"
ElseIf Month(Date) = "5" Then
criteria2 = "'Monthly' Or 'Feb-May-Aug-Nov' Or 'May-Annual'"
ElseIf Month(Date) = "6" Then
criteria2 = "'Monthly' Or 'March-June-Sept-Dec' Or 'June-Annual'"
ElseIf Month(Date) = "7" Then
criteria2 = "'Monthly' Or 'Jan-April-July-Oct' Or 'July-Annual'"
ElseIf Month(Date) = "8" Then
criteria2 = "'Monthly' Or 'Feb-May-Aug-Nov' Or 'Aug-Annual'"
ElseIf Month(Date) = "9" Then
criteria2 = "'Monthly' Or 'March-June-Sept-Dec' Or 'Sept-Annual'"
ElseIf Month(Date) = "10" Then
criteria2 = "'Monthly' Or 'Jan-April-July-Oct' Or 'Oct-Annual'"
ElseIf Month(Date) = "11" Then
criteria2 = "'Monthly' Or 'Feb-May-Aug-Nov' Or 'Nov-Annual'"
Else: criteria2 = "'Monthly' Or 'March-June-Sept-Dec' Or 'Dec-Annual'"
End If
'----------------------
sql1 = "INSERT INTO Main_PPY_TBL_TMP ( Account, Amount, PPM_Code, Pay_Date, Pay_Month, Starts_On ) "
sql1 = sql1 & "SELECT Main_PPY_TBL.Account, Main_PPY_TBL.Amount, Main_PPY_TBL.PPM_Code, Main_PPY_TBL.Pay_Date, Main_PPY_TBL.Pay_Month, Main_PPY_TBL.Starts_On "
sql1 = sql1 & "FROM Main_PPY_TBL "
sql1 = sql1 & "WHERE (Main_PPY_TBL.Pay_Date = " & criteria1 & ") AND (Main_PPY_TBL.Pay_Month = " & criteria2 & ") "
sql1 = sql1 & "ORDER BY Main_PPY_TBL.Account, Main_PPY_TBL.Pay_Date;"
MsgBox sql1
db.Execute (sql1)
'Send the information to excel on the associates C:drive
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Main_PPY_TBL_TMP", "C:\Process_PPY.xls", True
'Open Workbook
PPY_Excel.workbooks.Open "C:\Process_PPY.xls"
PPY_Excel.Visible = True
'Delete all records from temp tbl
SQL = "DELETE * FROM Main_PPY_TBL_TMP"
db.Execute (SQL)
End Sub
