Criteria in a query (select the previous month)

[Akchayan]

Hello everybody,




I have a query that is needed for a report. In this query I have a field called "Date". In this field I have written the following criterion:
>=[Forms]![foPayrollMonth]![StartDate]-30 And <=[Forms]![foPayrollMonth]![EndDate]-30
With this criterion I try to call up the data from the previous month, but this is too imprecise. Is there another way to "filter" from the first day of the previous month to the last day of the previous month?

Thank you for the help!

[Welshgasman]

Use the DateSerial() function with the DateAdd() function

[orange]

This may give you a starting point.

' ----------------------------------------------------------------
' Procedure Name: lastMonth
' Purpose: Given a Date, identify the previous month Start day to End Day
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 01-May-23
' ----------------------------------------------------------------
Sub lastMonth()
    Dim MyDate(3) As Date
    MyDate(0) = #2/25/2023#
    MyDate(1) = #3/17/2020#
    MyDate(2) = #12/25/2021#
    MyDate(3) = #1/1/2000#
    Dim i As Integer
    For i = LBound(MyDate) To UBound(MyDate)
       Debug.Print DateSerial(Year(MyDate(i)), Month(MyDate(i)) - 1, 1) & "  thru " & DateSerial(Year(MyDate(i)), Month(MyDate(i)), 0)
    Next i
End Sub

Result:

01-Jan-23 thru 31-Jan-23
01-Feb-20 thru 29-Feb-20
01-Nov-21 thru 30-Nov-21
01-Dec-99 thru 31-Dec-99

[Welshgasman]

Hmm, I did not know DateSerial was clever enough to know when to amend the Year?
That was why I suggested the DateAdd()

I knew it it did it for the Day, never ocurred to me it would do it for the Month as well.

[Akchayan]

This may give you a starting point.

' ----------------------------------------------------------------
' Procedure Name: lastMonth
' Purpose: Given a Date, identify the previous month Start day to End Day
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 01-May-23
' ----------------------------------------------------------------
Sub lastMonth()
    Dim MyDate(3) As Date
    MyDate(0) = #2/25/2023#
    MyDate(1) = #3/17/2020#
    MyDate(2) = #12/25/2021#
    MyDate(3) = #1/1/2000#
    Dim i As Integer
    For i = LBound(MyDate) To UBound(MyDate)
       Debug.Print DateSerial(Year(MyDate(i)), Month(MyDate(i)) - 1, 1) & "  thru " & DateSerial(Year(MyDate(i)), Month(MyDate(i)), 0)
    Next i
End Sub

Result:

01-Jan-23 thru 31-Jan-23
01-Feb-20 thru 29-Feb-20
01-Nov-21 thru 30-Nov-21
01-Dec-99 thru 31-Dec-99

How would u use that in the query tho?
I mean in this field("kriterien"):


I tried this:
DateSerial(Year(MyDate(i)), Month(MyDate(i)) - 1, 1) & " thru " & DateSerial(Year(MyDate(i)), Month(MyDate(i)), 0)

But i got a Syntax Error.

[Welshgasman]

You do not copy what was posted.
That was to show you how to get the date range.

So just use the dates on the form in each use of DateSerial.

[Akchayan]

You do not copy what was posted.
That was to show you how to get the date range.

So just use the dates on the form in each use of DateSerial.

I did it like this in my form now:

    Dim Anfang As Date    
    Dim Ende As Date
    Dim i As Integer
    For i = LBound([Forms]![foLohnabrechnungMonat]![Startdatum]) To UBound([Forms]![foLohnabrechnungMonat]![Enddatum])
    Date = DateSerial(Year([Forms]![foLohnabrechnungMonat]![Startdatum](i)), Month([Forms]![foLohnabrechnungMonat]![Startdatum](i)) - 1, 1)
    Ende = DateSerial(Year([Forms]![foLohnabrechnungMonat]![Startdatum](i)), Month([Forms]![foLohnabrechnungMonat]![Startdatum](i)), 0)
    Next i

But i get this error:
Error during compilation:


Expected: data field

What did i do wrong?

[orange]

Give us an example of the data value(s) on your form and the names of the controls involved.

Given a dateX,
this is the first of last/previous month: DateSerial(Year(datex), Month(dateX) - 1, 1)
this is the last of last/previous month: DateSerial(Year(dateX), Month(dateX), 0)

[Akchayan]

Give us an example of the data value(s) on your form and the names of the controls involved.

Given a dateX,
this is the first of last/previous month: DateSerial(Year(datex), Month(dateX) - 1, 1)
this is the last of last/previous month: DateSerial(Year(dateX), Month(dateX), 0)

An example would be:

[Forms]![foLohnabrechnungMonat]![Startdatum] = 01.05.2023

[Welshgasman]

I did it like this in my form now:

    Dim Anfang As Date    
    Dim Ende As Date
    Dim i As Integer
    For i = LBound([Forms]![foLohnabrechnungMonat]![Startdatum]) To UBound([Forms]![foLohnabrechnungMonat]![Enddatum])
    Date = DateSerial(Year([Forms]![foLohnabrechnungMonat]![Startdatum](i)), Month([Forms]![foLohnabrechnungMonat]![Startdatum](i)) - 1, 1)
    Ende = DateSerial(Year([Forms]![foLohnabrechnungMonat]![Startdatum](i)), Month([Forms]![foLohnabrechnungMonat]![Startdatum](i)), 0)
    Next i

But i get this error:
Error during compilation:


Expected: data field

What did i do wrong?

Well for a start, you do not indent your code which makes it harder to read.
You are using Date which is a reserved word and not Anfang for what I presume is your starting date?

How many dates are we talking about here as that will only populate the last set of dates?

Do you understand any of this logic? or are you just writing whatever comes to mind? I ask as we might have to take a step or two backwards if that is the case.

[Akchayan]

Well for a start, you do not indent your code which makes it harder to read.
You are using Date which is a reserved word and not Anfang for what I presume is your starting date?

How many dates are we talking about here as that will only populate the last set of dates?

Do you understand any of this logic? or are you just writing whatever comes to mind? I ask as we might have to take a step or two backwards if that is the case.

I don't rly understand the logic

[Welshgasman]

I don't rly understand the logic

Ok,
Let's step back and clarify a second.
You have two dates on your form, yes?, a start date and end date? That is how this sort of process works.

[Akchayan]

Yes i do.
The startdate is: "startdatum"
The enddate is: "enddatum"

[Welshgasman]

Yes i do.
The startdate is: "startdatum"
The enddate is: "enddatum"

OK, but if we are working on last month, we only need today's date for example, and from Orange's code we can work out the last month dates 1st and end of month.

So what purpose do these two dates serve except for that was what you were doing.?

No need to enter any date really as we can use the system date, yes?

[Akchayan]

OHHH yeah thats correct.