Date query not extending past the new year

J

johnkrytus

Registered User.
Local time
Today, 18:35
Joined
Mar 7, 2013
Messages
91
Now that the year has moved into 2014, the query below no longer works. It appears that it is not extending back into 2013. Can anyone please offer suggestions?


Code: 
Private Function multiDateClause(dateStartQry As String, dateEndQry As String, firstOp As String, secondOp As String)
 multiDateClause = dateClause(dateStartQry, firstOp) & " AND " & dateClause(dateEndQry, secondOp)
End Function

Private Sub b_last7_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-6", "Date()", ">=", "<="))
End Sub

I'm not actually talented enough to fully compose this code (a friend wrote it). So if you can offer a solution, please talk slowly and in short sentences so that I can understand it. :)
 
Without seeing the codein the custom functions todoListQry() and multiDateClause() there is no way to advise anything.
 
Sorry. Forgot the todoListQry. The multiDateClause was in there however. I put it all together here:

Code: 
Private Function todoListQry(whereClause As String) As String
    Dim selectClause As String
    Dim selectClauseHard As String
    Dim followupPersonClause As String
    Dim orderClause As String
    
     selectClause = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, noes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
     selectClauseHard = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, notes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
     followupPersonClause = "notes.followup_person_id = " & GetCurrentUserId()
     orderClause = "notes.priority DESC , notes.FollowUpDate ASC;"
             
     todoListQry = selectClause & " WHERE " & whereClause & " AND " & followupPersonClause & " ORDER BY " & orderClause
End Function

Private Function multiDateClause(dateStartQry As String, dateEndQry As String, firstOp As String, secondOp As String)
 multiDateClause = dateClause(dateStartQry, firstOp) & " AND " & dateClause(dateEndQry, secondOp)
End Function

Private Sub b_last7_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-6", "Date()", ">=", "<="))
End Sub
 
John,

It would really help if you showed the dateClause code and your query.

Wayne
 
I'm sorry to be such a knucklehead. I really thought it would be as simple as you advising me to use "now" instead of "date" And, I don't call dateClause in this particular sub..but here it is.

I can't say that it's necessarily the best way to do it..but it worked before 1/1/14

Code: 
Private Function todoListQry(whereClause As String) As String
    Dim selectClause As String
    Dim selectClauseHard As String
    Dim followupPersonClause As String
    Dim orderClause As String
    
     selectClause = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, noes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
     selectClauseHard = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, notes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
     followupPersonClause = "notes.followup_person_id = " & GetCurrentUserId()
     orderClause = "notes.priority DESC , notes.FollowUpDate ASC;"
             
     todoListQry = selectClause & " WHERE " & whereClause & " AND " & followupPersonClause & " ORDER BY " & orderClause
End Function

Private Function multiDateClause(dateStartQry As String, dateEndQry As String, firstOp As String, secondOp As String)
 multiDateClause = dateClause(dateStartQry, firstOp) & " AND " & dateClause(dateEndQry, secondOp)
End Function

Private Function dateClause(dateQry As String, op As String) As String
 dateClause = "Format(FollowUpDate, 'mm/dd/yyyy') " & op & " Format(" & dateQry & ",'mm/dd/yyyy')"
End Function

Private Sub b_last7_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-6", "Date()", ">=", "<="))
End Sub
 
johnkrytus said:
And, I don't call dateClause in this particular sub
Click to expand...

Yes it does. It is called inside yet another function.

Personally I would abandon this convoluted abomination and not bother to ask your friend for help again.

It looks to me that ultimately it is comparing dates as strings in mm/dd/yyyy format. That is not going to give sensible answers either before or after the new year.
 
John,

I don't see your original query.

There is nothing in the posted code that would restrict results to the current year. "whereClause" is referenced, but we don't get to see what it is.

It would be the Where clause that restricts data.

We also don't get to see firstop and secondop.

Can we see it?

If this builds a query, can we see what it builds?

Wayne
 
The todoListQry builds 5 (I think) different queries based on values read into it.

This is the entire module except for the qry_todolist_urgent which was built in the Access Query Builder

Here is the query result

Code: 
Option Compare Database
Private Function todoListQry(whereClause As String) As String
    Dim selectClause As String
    Dim selectClauseHard As String
    Dim followupPersonClause As String
    Dim orderClause As String
    
            selectClause = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, notes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
        selectClauseHard = "SELECT notes.priority, notes.FollowUpDate, Format([FollowUpDate],'mm/dd/yyyy') AS FollowUpDateTrunc, notes.created_at, notes.company_id, notes.followup_person_id, notes.author_id, notes.Notes, notes.person_id, notes.EntryId FROM notes"
    followupPersonClause = "notes.followup_person_id = " & GetCurrentUserId()
             orderClause = "notes.priority DESC , notes.FollowUpDate ASC;"
             
             todoListQry = selectClause & " WHERE " & whereClause & " AND " & followupPersonClause & " ORDER BY " & orderClause
End Function

Private Function dateClause(dateQry As String, op As String) As String
 dateClause = "Format(FollowUpDate, 'mm/dd/yyyy') " & op & " Format(" & dateQry & ",'mm/dd/yyyy')"
End Function

Private Function multiDateClause(dateStartQry As String, dateEndQry As String, firstOp As String, secondOp As String)
 multiDateClause = dateClause(dateStartQry, firstOp) & " AND " & dateClause(dateEndQry, secondOp)
End Function

'--**************
Private Sub b_urgent_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = "qry_todolist_urgent"  '--todoListQry(Notes.urgent = 1) '--THIS SHOULD ONLY GIVE US URGENT FOLLOWUPS REGARDLESS OF THE DATE
End Sub
Private Sub b_today_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-1", "Date()", ">=", "<="))
End Sub
Private Sub b_last7_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-6", "Date()", ">=", "<="))
End Sub
Private Sub b_month_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-30", "Date()", ">=", "<="))
End Sub
Private Sub b_future_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(dateClause("Date()+1", ">="))
End Sub
Private Sub b_all_past_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = "qry_todolist_all" '--using the dateClause does not filter out nulls
End Sub

Private Sub b_level_1_Click()
    Me.resultsFrame.SourceObject = "FollowUp_Level1"
    Me.resultsFrame.Form.RecordSource = "select (FirstName + ' ' + LastName) as fullname, * from people where people.Filed = 20 ORDER BY people.LastCnct DESC"
End Sub
Private Sub b_safetysheet_Click()
    Me.resultsFrame.SourceObject = "ActiveNoSafety"
    Me.resultsFrame.Form.RecordSource = "SELECT timeline.EmployeeId, timeline.SafetySheet, people.Filed, timeline.company_id, timeline.start_date, timeline.assignment FROM timeline INNER JOIN people ON timeline.EmployeeId = people.EmployeeID WHERE (((timeline.SafetySheet)<>'true' Or (timeline.SafetySheet) Is Null) AND ((people.Filed)=5 Or (people.Filed)=8) AND ((timeline.assignment)='true')) ORDER BY timeline.start_date DESC;"
    End Sub

Private Sub b_jillhead_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE notes.followup_person_id = 77 And notes.FollowUpdate Is Not Null"
End Sub
Private Sub b_joe_references_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE notes.followup_person_id = 84 And notes.FollowUpdate Is Not Null"
End Sub
Private Sub b_joehead_Click()
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE notes.followup_person_id = 68 And notes.FollowUpdate Is Not Null"
End Sub

Private Sub b_close_Click()
    DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.resultsFrame.SourceObject = "FollowUp_bystaff"
    Me.resultsFrame.Form.RecordSource = todoListQry(dateClause("Date()", "="))
End Sub
 
Last edited:
Just by way of closing this thread..

We took the Format out of the dateClause and it solved it.

Also, I think the reason that we handled it this way originally was because of the timestamp attached to the date that we needed off of there. I'm sure there is a better way... but regardless we got it to work. Thanks for the help.

Code: 
Private Function dateClause(dateQry As String, op As String) As String
    dateClause = "FollowUpDate" & op & dateQry
End Function
 
You must log in or register to reply here.

Similar threads

Steve R.
Date Verficiation to Minimize the Entry of "Bad" Dates
Replies
0
Views
2,382
Steve R.
Steve R.
D
From date to End date query not working
Replies
2
Views
588
djossh
D
A
FindFirst works well only for the 1st half of a year
Replies
8
Views
1,354
Anatoly
A
K
Processing Once a Year Data- Not wanting incremental records
Replies
1
Views
919
Uncle Gizmo
Uncle Gizmo
W
Insert query not inserting all fields
Replies
1
Views
669
wazz
wazz

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom