Can't Print Records 6 Months Old to Current Date

[joanl]

Hi,

I have a database that I've "inherited" from another creator who's no longer here. The database has over 45,000 records. It contains fields called DateOfFile, and AcctNumber (per client). A client can have several records in the database with different DateOfFile and the same AcctNumber. I need to print records 6 months old to current for a certain AcctNumber. I get the type mismatch error. In the table, the original creator used the data type 'text' for the DateOfFile field. Here is the code that is behind the print command button.

Private Sub PrintRecord_Click()
On Error GoTo Err_PrintRecord_Click

Dim stDocName As String
Dim link As String
   
    stDocName = "PrimaryData"
    link = &quot;[AcctNumber] = '&quot; & [AcctNumber] & &quot;'&quot; & (Fix(Now() - [DateofFile])) <= 180
    
    DoCmd.OpenReport stDocName, acViewNormal, , link
            
Exit_PrintRecord_Click:
    Exit Sub

Err_PrintRecord_Click:
    MsgBox Err.Description
    Resume Exit_PrintRecord_Click
    
End Sub

Any suggestions are greatly appreciated.

 

[K9Boxer]

It appears that you are trying to do a date function on a text field. This won't work.

Determine the date where Now() - 180 days and put into a text format [TextDateCurr-6Mo].
Change your code to
[DateofFile] >= [TextDateCurr-6Mo]

Perhaps this will work for you.