dateadd on month

[samuel]

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?

 

[Allen Browne]

Drop the Format() from around your calculated field. It has the effect of
turning the result into text, so you get a text match (not a date match.)

Then try this for your critiera:

= DateSerial(Year(Date()), Month(Date()), 1)

And < DateSerial(Year(Date()), Month(Date()), 1)

(Note that the criteria goes on one line.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?

 

[Ken Snell \(MVP\)]

Show us sample data for what is in the "date" field in the table -- I'm
guessing that it's something like the text string "20070904" (representing
September 4, 2007?).

 

[samuel]

Thanks for the tip.

I'm wanting to get all the results for last month. I'm wanting last month's
data and the first of this month. All results from last month

Drop the Format() from around your calculated field. It has the effect of
turning the result into text, so you get a text match (not a date match.)

Then try this for your critiera:

= DateSerial(Year(Date()), Month(Date()), 1)

And < DateSerial(Year(Date()), Month(Date()), 1)

(Note that the criteria goes on one line.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?

 

[Allen Browne]

To get last month, subtract 1 from the month part:

= DateSerial(Year(Date()), Month(Date()-1), 1)

And < DateSerial(Year(Date()), Month(Date()), 2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the tip.

I'm wanting to get all the results for last month. I'm wanting last
month's
data and the first of this month. All results from last month

Drop the Format() from around your calculated field. It has the effect of
turning the result into text, so you get a text match (not a date match.)

Then try this for your critiera:

= DateSerial(Year(Date()), Month(Date()), 1)

And < DateSerial(Year(Date()), Month(Date()), 1)

(Note that the criteria goes on one line.)

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?

 

[samuel]

My actual table data is:

2007081607034100

Show us sample data for what is in the "date" field in the table -- I'm
guessing that it's something like the text string "20070904" (representing
September 4, 2007?).

--

Ken Snell
<MS ACCESS MVP>

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?

 

[Ken Snell \(MVP\)]

SELECT * FROM TableName
WHERE [NameOfDateStringField] Like
Format(DateAdd("m",-1,Now()),"yyyymm") & "*";

--

Ken Snell
<MS ACCESS MVP>

My actual table data is:

2007081607034100

Show us sample data for what is in the "date" field in the table -- I'm
guessing that it's something like the text string "20070904"
(representing
September 4, 2007?).

--

Ken Snell
<MS ACCESS MVP>

I have data that contains results for last month. My date field is:

Date:
Format(DateSerial(Left([xact_dati],4),Mid([xact_dati],5,2),Mid([xact_dati],7,2)),"mm/dd/yyyy")

I only want last month's data, my criteria is

=DateAdd("m",-1,Now())

However, I get no results. Any thoughts?