tricky date manipulation in report

[ElSteveO]

I have a report that uses 2 user entered dates to display the sales in the time period between the two dates. I also am calculating the year to date sales in the same report using dsum and abs functions. I want the year to date sales to use the year entered by the user in the start date field, and the full end date entered by the user. I know I want to use the datepart function but I think my syntax is wrong. Here is what I have for a formula to calculate the actual number of Pontiac Grand Ams sold year to date:

=Sum(Abs([Vehicle]="Pontiac  Grand Am" And [N_U]="new" And [sale_date] Between "01/01/" & DatePart("yyyy",[txt1stDate]) And [txt2ndDate]))

[vehicle] is the sold type of vehicle
[N_U] is new or used vehicles
[txt1stDate] is the 1st date entered by the user
[txt2ndDate] is the 2nd date entered by the user

This formula is not working. It does work if I substitute (at the "between" part of the formula) actual dates enclosed w/pound signs.

I searched other posts for hours but couldn't get an answer, so I hope I'm not duplicating anyone's efforts.

Thanks,
Steve

 

[PaulBricker]

You can try wrapping your expression in the CDate() function and see if that helps. Haven't tried it but it may coerse the date. This is what it would look like in your expression.

CDate("01/01/" & DatePart("yyyy",[txt1stDate]))

I'm not sure why you are using the Abs() function here. If you have negative values it converts them to positive values and I'm not sure that you would want to do that. Also, it's an extra calculation that you probably don't need.

Paul

 

[ElSteveO]

Thanks for your effort Paul. Unfortunately the expression "is typed incorrectly or is too complex to be evaluated". I think it's close, but I feel there is something wrong with the syntax.

Also, I used the abs formula because it seems to work. It acts as a counter. I used Dsum for the sums in the "year to date" section and that works too.

Steve

 

[ElSteveO]

I got the formula to somewhat work by specifically addressing the text boxes on date range form, so my formula looks like this:

=Sum(Abs([Vehicle]="Pontiac  Grand Prix" And [N_U]="new" And [Sale_Date] Between [Forms]![frmDR1]![txtBeginYear] And [Forms]![frmDR1]![txtLastDate]))

so the only change I made was to add, "[Forms]![frmDR1]![txtFirstDate]" and "[Forms]![frmDR1]![txtLastDate]". Also, I got rid of the datepart formula. This now works, but my problem is I want the date range to be from the beginning of said year to the ending date on the date range form. My solution was to put a third text box on my date range form that took the 1st date and convert it to January 1, of whatever year I'm working on by using the datepart formula. So, instead of using [txtFirstDate] I used [txtBeginYear]. This yields the same results as [txtFirstDate] and I can't figure out why. Any ideas?

Thanks,
Steve

 

[PaulBricker]

Try setting the Control Source for txtBeginYear to
=DateSerial(Year([txtFirstDate], Month(1),Day(1))

See if that works for you.

Paul

 

[ElSteveO]

Thanks, Paul, I'll give it a shot.
Steve