DateSerial problem

[joel009]

I am trying to set up a query that will select values before the first day of the current month and the last day 2 months ago. Essentially getting all the records for the previous month. I am trying to use the DateSerial, and I can get all records before the first day of the current month but when I add in the part for the greater than the last day 2 months previouos it does not return any records.
Can anyone see what I'm doing wronng?

This works fine excluding the current months records:
SELECT AssetCenterImport.[Remarks (Problem)], AssetCenterImport.[Notified On]
FROM AssetCenterImport INNER JOIN T_InventoryBilling ON AssetCenterImport.SerialNo = T_InventoryBilling.IB_SerialNumber
WHERE (((AssetCenterImport.[Notified On])>Format(DateSerial(Year(Now()),Month(Now()),1),"m/d/yyyy")));

this returns no records:
SELECT AssetCenterImport.[Remarks (Problem)], AssetCenterImport.[Notified On]
FROM AssetCenterImport INNER JOIN T_InventoryBilling ON AssetCenterImport.SerialNo = T_InventoryBilling.IB_SerialNumber
WHERE (((AssetCenterImport.[Notified On])>Format(DateSerial(Year(Now()),Month(Now()),1),"m/d/yyyy") And (AssetCenterImport.[Notified On])<Format(DateSerial(Year(Now()),Month(Now())-1,1)-1,"m/d/yyyy")));

When I run the DateSerial part in the debugger it returns the desired date.

Can't figure it out.

 

[Sumitdev]

Think you might have your > and < the wrong way round. Should it be:

SELECT AssetCenterImport.[Remarks (Problem)], AssetCenterImport.[Notified On]
FROM AssetCenterImport INNER JOIN T_InventoryBilling ON AssetCenterImport.SerialNo = T_InventoryBilling.IB_SerialNumber
WHERE 
(((

AssetCenterImport.[Notified On])  [b]<[/b]  Format(DateSerial(Year(Now()),Month(Now()),1),"m/d/yyyy") 

And (

AssetCenterImport.[Notified On])  [b]>[/b]  Format(DateSerial(Year(Now()),Month(Now())-1,1)-1,"m/d/yyyy")

));

 

[THWatson]

Does it help if you use Between rather than the < and > signs?

You could also use Month(Date()) rather than Month(Now())

Tom

 

[SkipVought]

Hi,

You could also consider using...

Where Format(AssetCenterImport.[Notified On], "yyyymm") = Format(DateSerial(Year(Date(), Month(Date()-1,1), "yyyymm")

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[joel009]

Thanks for the suggestions, I finally got it working. Here is the query:
UPDATE AssetCenterImport INNER JOIN T_InventoryBilling ON AssetCenterImport.SerialNo = T_InventoryBilling.IB_SerialNumber SET T_InventoryBilling.IB_CurrentChange = [AssetCenterImport].[Remarks (Problem)]
WHERE (((AssetCenterImport.[Notified On])<Format(DateSerial(Year(Now()),Month(Now()),1),"m/d/yyyy") And (AssetCenterImport.[Notified On])>Format(DateSerial(Year(Now()),Month(Now())-1,1)-1,"m/d/yyyy")));
Needed a few more grouping parenthesis.