Query Formula Help

[Jimmy]

I have a field "Last Contact Date" in a query. I need to create a formula to
show me the records that have a last contact date more than 30 days old. Any
thoughts???

Thanks.

 

[Amy Blankenship]

WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30

HTH;

Amy

 

[Jimmy]

This is what i entered and I get an error

Expr1: Where DateDiff("d",[CRS Current Clients_qry].[Last Contact
Date],Now())>30

Is that correct?

Thanks

WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30

HTH;

Amy

I have a field "Last Contact Date" in a query. I need to create a formula
to
show me the records that have a last contact date more than 30 days old.
Any
thoughts???

Thanks.

 

[Douglas J. Steele]

WHERE [Last Contact Date] <= DateAdd("d", -30, Date)

 

[Amy Blankenship]

This is what i entered and I get an error

Expr1: Where DateDiff("d",[CRS Current Clients_qry].[Last Contact
Date],Now())>30

Is that correct?

No.

In the top row of the query grid, enter

TimeSinceContact: DateDiff("d",[CRS Current Clients_qry].[Last Contact

Date],Now())

In the row just beneath the check box, enter

30

HTH;

Amy

 

[Jimmy]

I entered: Expr1: [Last Contact Date]<=DateAdd("d",-30,[Date]) and it prompts
me for a parameter value.

WHERE [Last Contact Date] <= DateAdd("d", -30, Date)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a field "Last Contact Date" in a query. I need to create a formula
to
show me the records that have a last contact date more than 30 days old.
Any
thoughts???

Thanks.

 

[Jimmy]

That worked!!! Thank you very much.

This is what i entered and I get an error

Expr1: Where DateDiff("d",[CRS Current Clients_qry].[Last Contact
Date],Now())>30

Is that correct?

No.

In the top row of the query grid, enter

TimeSinceContact: DateDiff("d",[CRS Current Clients_qry].[Last Contact

Date],Now())

In the row just beneath the check box, enter

30

HTH;

Amy

 

[Douglas J. Steele]

Remove the square brackets around Date: it's supposed to be a reference to
the Date function. If Access inserts them for you, put Date() instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I entered: Expr1: [Last Contact Date]<=DateAdd("d",-30,[Date]) and it
prompts
me for a parameter value.

WHERE [Last Contact Date] <= DateAdd("d", -30, Date)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a field "Last Contact Date" in a query. I need to create a
formula
to
show me the records that have a last contact date more than 30 days
old.
Any
thoughts???

Thanks.

 

[Douglas J. Steele]

A problem with that approach is that the function has to get called for
every row in the table. (Another problem is that it'll have problems if Last
Contact Date is Null for any row)

Putting DateAdd("d", -30, Date()) as the criteria means the function only
gets called once.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30

HTH;

Amy

I have a field "Last Contact Date" in a query. I need to create a formula
to
show me the records that have a last contact date more than 30 days old.
Any
thoughts???

Thanks.

 

[Jimmy]

I made that a required field. I'm assuming that should alleviate the issue???

A problem with that approach is that the function has to get called for
every row in the table. (Another problem is that it'll have problems if Last
Contact Date is Null for any row)

Putting DateAdd("d", -30, Date()) as the criteria means the function only
gets called once.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30

HTH;

Amy

I have a field "Last Contact Date" in a query. I need to create a formula
to
show me the records that have a last contact date more than 30 days old.
Any
thoughts???

Thanks.

 

[Douglas J. Steele]

Well, it should eliminate the possibility of Null dates causing a problem,
but it's still far less efficient to make the multiple function calls rather
than a single one.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I made that a required field. I'm assuming that should alleviate the
issue???

A problem with that approach is that the function has to get called for
every row in the table. (Another problem is that it'll have problems if
Last
Contact Date is Null for any row)

Putting DateAdd("d", -30, Date()) as the criteria means the function only
gets called once.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30

HTH;

Amy

I have a field "Last Contact Date" in a query. I need to create a
formula
to
show me the records that have a last contact date more than 30 days
old.
Any
thoughts???

Thanks.

 

[Amy Blankenship]

A problem with that approach is that the function has to get called for
every row in the table. (Another problem is that it'll have problems if
Last Contact Date is Null for any row)

Putting DateAdd("d", -30, Date()) as the criteria means the function only
gets called once.

Good point. I never claimed to be good with dates, which is why I planted
figs ;-).

 

[Amy Blankenship]

I entered: Expr1: [Last Contact Date]<=DateAdd("d",-30,[Date]) and it
prompts

Enter the [Last Contact Date] on the Expr1 line, and <= DateAdd(...) on the
line just below the check box. That's what that line is for. It is called
the criteria line, and what it means is "compare what you got in the first
line to whatever is in this line."

HTH;

Amy