VBA/ Sql to run query on Month only

[GUIDO22]

I wish to use a recordset call with SQL to return all records for the current month/year... can you advise on the parameters to Format to achieve this please..?

 

[CJ_London]

in sql the criteria would be something like

WHERE recordDate > (date-day(date)) and recordDate<dateadd('m',1,date-day(date))+1

 

[GUIDO22]

I came up with this ... (Month(Now())=MONTH(SaleDate) AND Year(Now())=YEAR(SaleDate))

 

[Gasman]

I came up with this ... (Month(Now())=MONTH(SaleDate) AND Year(Now())=YEAR(SaleDate))

That would also work?

 

[GUIDO22]

Yes, works fine..

 

[ebs17]

SELECT
   *
FROM
   TableX
WHERE
   DateTimeField >= DateSerial(Year(Date()), Month(Date()), 1)
      AND
   DateTimeField < DateSerial(Year(Date()), Month(Date()) + 1, 1)

This also allows you to use an index that may exist. This works faster - sometimes you need speed.

 

[CJ_London]

my suggestion in post #2 also utilises indexing

 

[ebs17]

This is correct, because no calculation is applied to the table field and there is no NOT or <> in the comparison.

However, in SQL Date must be used as Date() (=> function). Date without brackets would be interpreted as unknown variable.

 

[CJ_London]

hmm verified it in the immediate window - sql will add the () automatically. But agree, poor presentation on my account

 

[ebs17]

What you should also pay attention to: A DateTime field can contain time components, which would therefore also have to be taken into account.

? Date - Day(Date)
31.01.2023

Here, practically the whole day before the current month would be included in the filter.

 

[Gasman]

in sql the criteria would be something like

WHERE recordDate > (date-day(date)) and recordDate<dateadd('m',1,date-day(date))+1

I kept getting 'expected expression' for the last criteria when using it in the immediate window?
Changed 'm' to "m" and it worked as I expected?