Date serial YTD 2

[villica]

Hi everyone

I am trying to get the ytd sales depending on the date the user selects on the form

I created a form where the user picks the year and month
so if the user picks let's say nov 2006, I want to know waht is the total ytd sales as of nov 2006. On the table I have an invoice date fiel. I can not come up with the formula using the date pick on the form.


villica

 

[Golom]

Try

InvoiceDate BETWEEN DateSerial(Year(DF),1,1) 
                AND DateSerial(Year(DF),Month(DF)+1,0)

Where "DF" is the date field selected by the user.

I have assumed that you want the year-to-date sales for the end of November (for example) if the user picked Nov, 2006.

If "DF" is not a DateTime field then a few more gyrations may be necessary to extract the Year and Month from it.

 

[villica]

thanks Golom

On the form I have two combo boxes, one is the year and the other one is month.

villica

 

[Golom]

Probably easiest to convert the data from those two boxes into one date field to be used in the query

DF = cDate(YearField & "/" & MonthField & "/1")

 

[PHV]

Use this criteria for the invoice date field:
Between DateSerial([Forms]![Form name]![Year combo],1,1) And DateSerial([Forms]![Form name]![Year combo],[Forms]![Form name]![Month combo]+1,0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[villica]

thanks Golom and PHV and almost there

How do I make this statement provided by PHV work for previous year so in this case if they selected nov 2006
I want a column for previous year result which it would be
nov 2005


Between DateSerial([Forms]![Form name]![Year combo],1,1) And DateSerial([Forms]![Form name]![Year combo],[Forms]![Form name]![Month combo]+1,0)

villica

 

[Golom]

Between DateSerial([Forms]![Form name]![Year combo][red] - 1[/red],1,1) 
    And DateSerial([Forms]![Form name]![Year combo][red] - 1[/red],[Forms]![Form name]![Month combo]+1,0)

 

[villica]

sorry Golom and I was just going to repost again because the one I use is this one, so I need to subtract somehow a year

Between DateSerial(Year(CDate([forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value])),1,1) And DateSerial(Year(CDate([forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value])),Month(CDate([forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value]))+1,0)

villica

 

[Golom]

Between DateSerial(Year(CDate( [forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value]))[red] - 1[/red],1,1) 
    And DateSerial(Year(CDate([forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value])[red] - 1[/red]),
                   Month(CDate([forms]![form1]![list1].[value] & "/" & "1" & "/" & [forms]![form1]![list3].[value]))+1,0)

 

[villica]

sorry Golom, I must be doing something wrong. I am tryng to get the prior year YTD and the last query gave me a strange result. I thouught i have to subtract from the year

villica

 

[Golom]

Paren in the wrong place

Between DateSerial(Year(CDate( [forms]![form1]![list1].[value] & "/1/" & [forms]![form1]![list3].[value])) - 1,1,1) 
And DateSerial(Year(CDate([forms]![form1]![list1].[value] & "/1/" & [forms]![form1]![list3].[value])) - 1,
Month(CDate([forms]![form1]![list1].[value] & "/1/" & [forms]![form1]![list3].[value]))+1,0)

 

[villica]

thank you so much for your help Golom, it works so perfect.

villica

 

[PHV]

Why not simply this ?
Between DateSerial([forms]![form1]![list3]-1,1,1) And DateSerial([forms]![form1]![list3]-1,[forms]![form1]![list1]+1,0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886