Query Help!

M

Melanie

I have a database that I am using to track sales leads... In this database, I
have two different dates that I would like to run queries off of. One date is
a user entered field they use if someone is reqestuing a callback on a
certain date, the other is a calculated field that is taking the date that
the contact was last called and adding 8 days. I am trying to add a field
that would look at both of those date fields and give me the callback date if
there is one and if not would give me the date that is calcualted from the
formula. Is that possible? I have tried this several different ways to no
avail. To make it even more complicated, I need this field to only give me
anything with a date of today (or the current date) and before. Does this
make any sense? Is it possible?
 
J

John Spencer MVP

You can use an expression in a query to return one date or the other and then
you can apply criteria to it. In the query design view, this would look
something like the following:

Field: ContactOn: NZ([Callback],DateAdd("d",8,[LastCalled])
Criteria: = Date()

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
K

Ken Snell [MVP]

Generic answer, yes, you can use the Nz function to replace a NULL value
from the "callback" field with the calculated field's value:

UseForCallback: Nz(NameOfCallbackField, ExpressionForCalculating8DaysMore)
 
M

Melanie

Thank you so much that seems to be working. One question though, I set the
criteria for <=Date(), everything looks right except there are 2 records that
are coming up before they are supposed to. one is for 11/20/2009 & the other
for 10/16/2009. Do you know why that is?
 
M

Melanie

This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!
 
J

John Spencer MVP

So are the fields DateTime fields or are they text fields that are storing
strings that look like dateTimes.

If you are guaranteed to have data in either Call-Back On or Last Action Date
then you can change the calculated field to force a conversion.

Followup: CDate(NZ([Call-back on],DateAdd("d",8,[Last Action Date])))

If both fields could be null then you can use something like the following
(all on one line)

Followup:
CDate(NZ([Call-back on],DateAdd("d",8, Nz([Last Action Date], #2999-12-31#))))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!

Ken Snell said:
Post the calculated field's full expression so that we can see what you're
doing.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
M

Melanie

The fields are text fields, Do I need to change them?

John Spencer MVP said:
So are the fields DateTime fields or are they text fields that are storing
strings that look like dateTimes.

If you are guaranteed to have data in either Call-Back On or Last Action Date
then you can change the calculated field to force a conversion.

Followup: CDate(NZ([Call-back on],DateAdd("d",8,[Last Action Date])))

If both fields could be null then you can use something like the following
(all on one line)

Followup:
CDate(NZ([Call-back on],DateAdd("d",8, Nz([Last Action Date], #2999-12-31#))))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!

Ken Snell said:
Post the calculated field's full expression so that we can see what you're
doing.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thank you so much that seems to be working. One question though, I set the
criteria for <=Date(), everything looks right except there are 2 records
that
are coming up before they are supposed to. one is for 11/20/2009 & the
other
for 10/16/2009. Do you know why that is?

:

Generic answer, yes, you can use the Nz function to replace a NULL value
from the "callback" field with the calculated field's value:

UseForCallback: Nz(NameOfCallbackField,
ExpressionForCalculating8DaysMore)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that I am using to track sales leads... In this
database,
I
have two different dates that I would like to run queries off of. One
date
is
a user entered field they use if someone is reqestuing a callback on a
certain date, the other is a calculated field that is taking the date
that
the contact was last called and adding 8 days. I am trying to add a
field
that would look at both of those date fields and give me the callback
date
if
there is one and if not would give me the date that is calcualted from
the
formula. Is that possible? I have tried this several different ways to
no
avail. To make it even more complicated, I need this field to only give
me
anything with a date of today (or the current date) and before. Does
this
make any sense? Is it possible?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer MVP

Well, I would change them to datetime fields if that is ALL you store in them.
Also, I might do a quick check to make sure that all the data could be
converted without loss.

Create a query with your date field and then apply criteria to return any
records where the date string could not be interpreted as a date.

Something like:

Field: IsDate(Nz([Call-Back on],#2009-01-01#))
Criteria: False

If that returned NO records or records where you don't care if the value is
dropped, then I would convert. If you can't convert then you are going to
have to figure out some other solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The fields are text fields, Do I need to change them?

John Spencer MVP said:
So are the fields DateTime fields or are they text fields that are storing
strings that look like dateTimes.

If you are guaranteed to have data in either Call-Back On or Last Action Date
then you can change the calculated field to force a conversion.

Followup: CDate(NZ([Call-back on],DateAdd("d",8,[Last Action Date])))

If both fields could be null then you can use something like the following
(all on one line)

Followup:
CDate(NZ([Call-back on],DateAdd("d",8, Nz([Last Action Date], #2999-12-31#))))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!

:

Post the calculated field's full expression so that we can see what you're
doing.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thank you so much that seems to be working. One question though, I set the
criteria for <=Date(), everything looks right except there are 2 records
that
are coming up before they are supposed to. one is for 11/20/2009 & the
other
for 10/16/2009. Do you know why that is?

:

Generic answer, yes, you can use the Nz function to replace a NULL value
from the "callback" field with the calculated field's value:

UseForCallback: Nz(NameOfCallbackField,
ExpressionForCalculating8DaysMore)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that I am using to track sales leads... In this
database,
I
have two different dates that I would like to run queries off of. One
date
is
a user entered field they use if someone is reqestuing a callback on a
certain date, the other is a calculated field that is taking the date
that
the contact was last called and adding 8 days. I am trying to add a
field
that would look at both of those date fields and give me the callback
date
if
there is one and if not would give me the date that is calcualted from
the
formula. Is that possible? I have tried this several different ways to
no
avail. To make it even more complicated, I need this field to only give
me
anything with a date of today (or the current date) and before. Does
this
make any sense? Is it possible?
Click to expand...
Click to expand...
Click to expand...
 
M

Melanie

I went ahead and changed these to date time fields and fixed the issues. It
works beautifully! Thanks for all of your help.


John Spencer MVP said:
Well, I would change them to datetime fields if that is ALL you store in them.
Also, I might do a quick check to make sure that all the data could be
converted without loss.

Create a query with your date field and then apply criteria to return any
records where the date string could not be interpreted as a date.

Something like:

Field: IsDate(Nz([Call-Back on],#2009-01-01#))
Criteria: False

If that returned NO records or records where you don't care if the value is
dropped, then I would convert. If you can't convert then you are going to
have to figure out some other solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The fields are text fields, Do I need to change them?

John Spencer MVP said:
So are the fields DateTime fields or are they text fields that are storing
strings that look like dateTimes.

If you are guaranteed to have data in either Call-Back On or Last Action Date
then you can change the calculated field to force a conversion.

Followup: CDate(NZ([Call-back on],DateAdd("d",8,[Last Action Date])))

If both fields could be null then you can use something like the following
(all on one line)

Followup:
CDate(NZ([Call-back on],DateAdd("d",8, Nz([Last Action Date], #2999-12-31#))))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Melanie wrote:
This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!

:

Post the calculated field's full expression so that we can see what you're
doing.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thank you so much that seems to be working. One question though, I set the
criteria for <=Date(), everything looks right except there are 2 records
that
are coming up before they are supposed to. one is for 11/20/2009 & the
other
for 10/16/2009. Do you know why that is?

:

Generic answer, yes, you can use the Nz function to replace a NULL value
from the "callback" field with the calculated field's value:

UseForCallback: Nz(NameOfCallbackField,
ExpressionForCalculating8DaysMore)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that I am using to track sales leads... In this
database,
I
have two different dates that I would like to run queries off of. One
date
is
a user entered field they use if someone is reqestuing a callback on a
certain date, the other is a calculated field that is taking the date
that
the contact was last called and adding 8 days. I am trying to add a
field
that would look at both of those date fields and give me the callback
date
if
there is one and if not would give me the date that is calcualted from
the
formula. Is that possible? I have tried this several different ways to
no
avail. To make it even more complicated, I need this field to only give
me
anything with a date of today (or the current date) and before. Does
this
make any sense? Is it possible?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer MVP

Glad it worked. See you around

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I went ahead and changed these to date time fields and fixed the issues. It
works beautifully! Thanks for all of your help.


John Spencer MVP said:
Well, I would change them to datetime fields if that is ALL you store in them.
Also, I might do a quick check to make sure that all the data could be
converted without loss.

Create a query with your date field and then apply criteria to return any
records where the date string could not be interpreted as a date.

Something like:

Field: IsDate(Nz([Call-Back on],#2009-01-01#))
Criteria: False

If that returned NO records or records where you don't care if the value is
dropped, then I would convert. If you can't convert then you are going to
have to figure out some other solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The fields are text fields, Do I need to change them?

:

So are the fields DateTime fields or are they text fields that are storing
strings that look like dateTimes.

If you are guaranteed to have data in either Call-Back On or Last Action Date
then you can change the calculated field to force a conversion.

Followup: CDate(NZ([Call-back on],DateAdd("d",8,[Last Action Date])))

If both fields could be null then you can use something like the following
(all on one line)

Followup:
CDate(NZ([Call-back on],DateAdd("d",8, Nz([Last Action Date], #2999-12-31#))))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Melanie wrote:
This is what I have in my formula-

Followup: NZ([Call-back on],DateAdd("d",8,[Last Action Date]))

This is what I have for my criteria
<=Date ()

The last action date is the field I need 8 days added on to and the
call-back on is the field I need to show if there is something listed there.
That formula seems to be working fine, but when I put in the criteria it is
giving me dates I don't need showing like 1/5/2010, 10/16/2009... etc. Maybe
I am entering the incorrect criteria, but the only things I need to pop up in
this query is everything that is supposed to be followed up with today and
any date before today.

Thanks so much for all the help you have given me so far!

:

Post the calculated field's full expression so that we can see what you're
doing.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thank you so much that seems to be working. One question though, I set the
criteria for <=Date(), everything looks right except there are 2 records
that
are coming up before they are supposed to. one is for 11/20/2009 & the
other
for 10/16/2009. Do you know why that is?

:

Generic answer, yes, you can use the Nz function to replace a NULL value
from the "callback" field with the calculated field's value:

UseForCallback: Nz(NameOfCallbackField,
ExpressionForCalculating8DaysMore)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that I am using to track sales leads... In this
database,
I
have two different dates that I would like to run queries off of. One
date
is
a user entered field they use if someone is reqestuing a callback on a
certain date, the other is a calculated field that is taking the date
that
the contact was last called and adding 8 days. I am trying to add a
field
that would look at both of those date fields and give me the callback
date
if
there is one and if not would give me the date that is calcualted from
the
formula. Is that possible? I have tried this several different ways to
no
avail. To make it even more complicated, I need this field to only give
me
anything with a date of today (or the current date) and before. Does
this
make any sense? Is it possible?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Copy previous record into form field 0
#error - Blank 3
can't find the right query 0
Creating a Chart on form from selected data in query. 0
query to show where no data in a stated column 0
Publisher is filling blank lines with data 2
query on date 1
Query to show items before a nominated Date 1

Top