Date Range Calculation problem

[supermaestro]

Hi

I'm developing a database for an Accountants form. They need a Year End field whichis essentially dd/mm i.e 01/02 for Feb 1st. Basically how can I search for date ranges using just day and month without the year? I know that the following works with the year added but need to use it without the year:

If Not IsNull(Me.Year_End_From) Then
strWhere = strWhere & " (contacts.[Year End] >= #" & Format(Me.Year_End_From, "mm/dd/yyyy&quot & "#) AND"
End If

If Not IsNull(Me.Year_End_To) Then
strWhere = strWhere & &quot; (contacts.[Year End] <= #&quot; & Format(Me.Year_End_To, &quot;mm/dd/yyyy&quot & &quot;#) AND&quot;
End If

Can anybody help. Would I also have to make the field 'Year End' in my contacts table equal to text rather than date/time has it is at present?

 

[ReluctantDataGuy]

You should be able to compare a string like &quot;01/02&quot; to parts of the date. Check out the DatePart() function. In help, go tto Contents / Visual Basic Language Reference ' Functions / D-G / DatePart.

An example using DatePart():

If Not IsNull(Me.Year_End_From) Then
strWhere = strWhere & &quot; (contacts.[Year End] >= '&quot; & _
DatePart(&quot;d&quot;,Me.Year_End_From)) & &quot;/&quot; & _
DatePart(&quot;m&quot;,Me.Year_End_From) & &quot;' AND&quot;
End If

From that, you should be able to figure out the comparison for Me.Year_End_To.

Hope this helps.

 

[supermaestro]

Hi

I'm still struggling with this. The Year End is stored as text i.e 01/02 for 1st Feb in the contacts database. A form is used to get the Year End From date i.e. 01/01 and the Year End To Date i.e 01/03. I want to return the records for those that fall in between. The code is a small part of a query that I build up. However, the following code results in an Object expected or other error. Any ideas on the syntax so that I can calculate the ranges? Thanks

If Not IsNull(Me.Year_End_From) Then
yearEndFrom = Me.Year_End_From & &quot;/&quot; & DatePart(&quot;yyyy&quot;, Date)

'strWhere = strWhere & &quot; (contacts.[Year End] >= #&quot; & Format(yearEndFrom, &quot;mm/dd/yyyy&quot & &quot;#) AND&quot;

strWhere = strWhere & DatePart(&quot;d&quot;, contacts.[Year End]) & &quot;/&quot; & DatePart(&quot;m&quot;, contacts.[Year End]) & &quot; >= #&quot; & DatePart(&quot;d&quot;, Me.Year_End_From) & &quot;/&quot; & DatePart(&quot;m&quot;, Me.Year_End_From) & &quot;# AND&quot;
End If

If Not IsNull(Me.Year_End_To) Then
yearEndTo = Me.Year_End_To & &quot;/&quot; & DatePart(&quot;yyyy&quot;, Date)

'strWhere = strWhere & &quot; (contacts.[Year End] <= #&quot; & Format(yearEndTo, &quot;mm/dd/yyyy&quot & &quot;#) AND&quot;

strWhere = strWhere & DatePart(&quot;d&quot;, contacts.[Year End]) & &quot;/&quot; & DatePart(&quot;m&quot;, contacts.[Year End]) & &quot; <= #&quot; & DatePart(&quot;d&quot;, Me.Year_End_To) & &quot;/&quot; & DatePart(&quot;m&quot;, Me.Year_End_To) & &quot;# AND&quot;

End If

 

[jvanleuvan]

I am assuming that you want to open a recordset containing records that have a date between (for example) Jan 3(03/01) and Mar 6(06/03) with no year:

Also I assume that in your Table [year end] is a string that is of the format &quot;01/02&quot; (1 feb)

*NOTE: this is not US date format of mm/dd/yyyy

try this SQL

SELECT CDate([date] & &quot;/&quot; & Year(Now())) AS date_adjust
FROM [mytable]
WHERE (((CDate([date] & &quot;/&quot; & Year(Now())))>=CDate([from_date] & &quot;/&quot; & Year(Now())) And (CDate([date] & &quot;/&quot; & Year(Now())))<=CDate([to_date] & &quot;/&quot; & Year(Now()))));

* this works with US styl dates, If your system default is set to dd/mm/yyyy then it should also work