Re: Query Help!

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

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

"John Spencer MVP" wrote:

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!

"Ken Snell [MVP]" wrote:

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/



"Melanie" <Melanie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5C80A43E-18BC-45F3-93D6-946DA4A14009@xxxxxxxxxxxxxxxx
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?

"Ken Snell [MVP]" wrote:

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/


"Melanie" <Melanie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B6F82FD2-C05D-46D6-BE1B-9ABCF42AB606@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: Query Help!
    ... So are the fields DateTime fields or are they text fields that are storing strings that look like dateTimes. ... Access MVP 2002-2005, 2007-2009 ... 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... ... from the "callback" field with the calculated field's value: ...
    (microsoft.public.access.queries)
  • Re: Query Help!
    ... Ken Snell ... <MS ACCESS MVP> ... criteria for <=Date, everything looks right except there are 2 records ... from the "callback" field with the calculated field's value: ...
    (microsoft.public.access.queries)
  • Re: Query Help!
    ... That formula seems to be working fine, but when I put in the criteria it is ... <MS ACCESS MVP> ... from the "callback" field with the calculated field's value: ... the other is a calculated field that is taking the date ...
    (microsoft.public.access.queries)
  • Re: Query Help!
    ... Access MVP 2002-2005, 2007-2009 ... That formula seems to be working fine, but when I put in the criteria it is ... from the "callback" field with the calculated field's value: ...
    (microsoft.public.access.queries)
  • Re: Text Numeric Deja Vu
    ... > Removing the criteria did not affect the error message. ... >> I didn't expect you to remove the criteria permanently. ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.tablesdbdesign)