Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Question Unanswered: New to Access Looking for Ideas

    Hello All,

    I have three fields that have dates in them, I would like to query these fields looking for Null values and dates that are older than a year. So far everything I have tried has failed. Any Suggestions!

    Sincerely,
    Joseph Limata
    US Army

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    NULL has sepcial meaning in databases. yoiu can't look for soemthing = null, instead you have to use the function isnull instead

    so thats going to be something like isnull(mycolumn) = true, but you can shorten that to isnull(mycolumn) if you want to find NULLS or NOT isnull(mycolumn) if you don't

    checking dates... use the dateadd function

    so thats going to be something like mydatecolumn <= dateadd("yyyy",-1,date())
    date() returns the current system date, you cold also use now() which returns the current system date AND time

    Personally I don't use the query designer that often. I normally switch straight away to the SQL view, so how you'd specify that in the query designer I don't know
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    9

    Question Thanks for the Great Input

    Excellent, I do understand the IsNull function but it has not worked for me. Does mycolumn= the name of column I place the parameters in? If so everytime I type it it forces the following IsNull([mycolumn]), is this correct?

    Quote Originally Posted by healdem View Post
    NULL has sepcial meaning in databases. yoiu can't look for soemthing = null, instead you have to use the function isnull instead

    so thats going to be something like isnull(mycolumn) = true, but you can shorten that to isnull(mycolumn) if you want to find NULLS or NOT isnull(mycolumn) if you don't

    checking dates... use the dateadd function

    so thats going to be something like mydatecolumn <= dateadd("yyyy",-1,date())
    date() returns the current system date, you cold also use now() which returns the current system date AND time

    Personally I don't use the query designer that often. I normally switch straight away to the SQL view, so how you'd specify that in the query designer I don't know

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yup mycolumn is my shorthand for a column name, just as I often use mytable

    As I said beofre I don't use the query designer very often, i tend to use the SQL view in query design

    lets say your query ise
    select my, column, list from mytable
    where isnull(acolumn) and mydatecolumn<= dateadd("yyyy",-1,date())

    that means return all rows where acolumn is NULL AND where mydatecolumn is at least 1 year ago
    ..you'd need to substitute
    my, column, list for the list of columns you want returned
    acolumn to be the column you ar lookign for to be NULL
    mydatecoloumn with the name of the column of type date whose value you are looking for

    if you want to look for say a couple of columns that are null
    select my, column, list from mytable
    where (isnull(acolumn) or isnull(bcolumn)) and mydatecolumn<= dateadd("yyyy",-1,date())

    you can combine tings say you want to find where acolumn AND bcolumn are null
    where (isnull(acolumn) AND isnull(bcolumn)) and mydatecolumn<= dateadd("yyyy",-1,date())

    y'get the picture

    how you express that in the query designer I don't know, as I don't use it for that sort of query. offhand I think that in the lower boxes marked criteria each row is treated as an AND condition ie any values in there are buitl together if they are on separate rows then they are OR conditions. but I don;t know. The reality is in computing terms Im form the dark ages, I don't especially think about pretty interefaces Id rather actually control things directly. I still prefer using a dos window to look for files
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •