Variable start of the month query, WHERE IF clause

[thraling]

Hello,

I have to calculate a sum of deliveries since the beginning of the month. The point is that the first day of the "month" is NOT the 1st, but a day calculated as follows:

S M T W T F S| S M T W T F S|S M T W T F S|S M T W T F S
1 2 3 4 5 6 7| x 1 2 3 4 5 6|x x 1 2 3 4 5|x x x 1 2 3 4

In this case the month is starting either from the 1, or from the days of the previous month marked with "x".

S M T W T F S|S M T W T F S|S M T W T F S
x x x x 1 2 3| x x x x x 1 2|x x x x x x 1

In this cases the week in exam will go to the previous month, and our month will start from the 4th, the 3rd and the 2nd respectively.

Now, I have to write a query WHERE the date is between today and the beginning of the month. This date comes from a Form ("DateTo"), and have to perform a check (I guess, whether the month starts since the 5th day of the week or not).

I wrote that:

WHERE SU116.DATE = CASE WHEN DATEPART(dw,DATEADD(month, DATEDIFF(month, 0, [Forms]![Form1]![DateTo]), 0)) >= 5 THEN DateAdd("d",9-DATEPART(dw,[Forms]![Form1]![DateTo]),[Forms]![Form1]![DateTo]) ELSE DateAdd("d",0-DATEPART(dw,[Forms]![Form1]![DateTo])+1,[Forms]![Form1]![DateTo]) END

But it's not working ("missing operator").

Could you be so kind to give me a really appreciated help?


Thank you

 

[Ranman256]

you cant put CASE statements in a query.
I would base the query dates from a form. The form will show the start of the month,
(using a case statement, or user entry)

then the query would look at the form for criteria:
select * from table where [date] between forms!MyForm!txtStartDate and forms!MyForm!txtEndDate

 

[thraling]

you cant put CASE statements in a query.
I would base the query dates from a form. The form will show the start of the month,
(using a case statement, or user entry)

then the query would look at the form for criteria:
select * from table where [date] between forms!MyForm!txtStartDate and forms!MyForm!txtEndDate

It makes sense. Writing that hieroglyph drove me crazy enough

 

[informer]

Hi thraling,

You can call a VBA function from a query. The function must be declared in a module (Warning not in class module !!!)
The function will be called for each record of the select query

In a module
public function functionForSQL (parameter1 as type, parameter2 as type,...) as return Type
... your code
functionForSQL = returnValue
end function

In SQL query
select field1, field2, functionForSQL as aliasName FROM TABLE ...

Hope it will help you

 

[Simon_MT]

You can also create a "Financial" Period Calendar and store the Period into the record.

Simon