Using DateSerial to get a range

[GMFTM]

Not that familiar with DateSerial, I need to build this so that I am gettting MTD data, current and Previous month.

Can someone start me off with the proper syntax? using beginning of month to current day and previous month.

DateSerial(??

 

[GMFTM]

Ok,

I am this far now, however I am getting an error on the Between and And....

Count(IIf([Sale_Date] Between (DateSerial(Year(Date), Month(Date) - 1, 1) And (DateSerial(Year(Date), Month(Date), 1) - 1),[VIN]))

 

[xenou]

The IIF() doesn't look complete. Should be IIF(Condition, TruePart, FalsePart) You only have IIF(Condition)

Possibly the condition stuff can go in your where clause so no need for IIF.

 

[GMFTM]

Previous VIN: Count(IIf([Sale_Date] Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),1)-1,[VIN],0))
Current VIN: Count(IIf([Sale_Date] Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1,[VIN],0))


Right, I finished of the condition, however I am getting the same results for both of these?

Any thoughts

 

[Joe4]

See if this works any better:
Previous VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),1)-1,1,0))
Current VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1,1,0))

 

[GMFTM]

Yes that was it, I saw it too after I posted it, lol.

Now If I am looking for MTD and today is the 21st, will the below always pull current date and the 1st of the month?

I think that is how I read it and wrote it, but again not 100% on it

See if this works any better:
Previous VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),1)-1,1,0))
Current VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1,1,0))

 

[Joe4]

To get between the 1st of the current month and today, you can just use:

Between DateSerial(Year(Now()),Month(Now()),1) and Now()

 

[GMFTM]

So like this:

Previous VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),1)-1,1,0))
Current VIN: Sum(IIf([Sale_Date] Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1,1,0))

I have two here, the first is the previous month which need to pull 1st - 21st
and the second current month need to pull 1st - 21st

so how would that look in the above syntax?

To get between the 1st of the current month and today, you can just use:

Between DateSerial(Year(Now()),Month(Now()),1) and Now()

 

[Joe4]

I have two here, the first is the previous month which need to pull 1st - 21st

Your "Previous" is returning through February 28, not February 21.

Note: You can easily check to see what your date calculations easily return by using a calculated field in a Query, i.e.
Just put this as a calculated field in any query and see what it returns:

Test: DateSerial(Year(Now()),Month(Now()),1)-1

So you can use this technique to perfect each date calculation, and then drop them into your equation.
I will leave that for you to work out. If you run into trouble with a particular one, post back on that one.