dateadd on month


G

Guest

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?
 
Ad

Advertisements

A

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.

samuel said:
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?
 
K

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?).
 
G

Guest

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

Allen Browne said:
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.

samuel said:
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?
 
A

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.

samuel said:
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

Allen Browne said:
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.)

samuel said:
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?
 
G

Guest

My actual table data is:

2007081607034100



Ken Snell (MVP) said:
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>




samuel said:
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?
 
Ad

Advertisements

K

Ken Snell \(MVP\)

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

--

Ken Snell
<MS ACCESS MVP>


samuel said:
My actual table data is:

2007081607034100



Ken Snell (MVP) said:
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>




samuel said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top