Results 1 to 9 of 9

Thread: Query Request

  1. #1
    Join Date
    Mar 2004
    Location
    Eden ,NorthCarolina
    Posts
    7

    Red face Unanswered: Query Request

    Hi, could someone please help me. I am press for time to make this happen. I have a database with the date that each member joined the
    church. I want to be able to specify the date members joined on a 30, 60, 90, day period in my Query. I have member that joined two month ago 2/1/04. I want to be able to pull these member from the table only. I want to be able to do this on a regular basic. I have tried
    to recall these member creating a Query using "2/1/04" in the criteria section when I run the Query its blank. I want to be able
    to go back a couple of year if I have to. When I run the query without the criteria I get a list of dates. I don't know if maybe my date format in mytable is wrong or what. COULD YOU HELP PLEASE!!!!!!

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Re: Query Request

    On your query where you're using a date for your criteria, make sure to enclose it in ##. So if you want to find members who joined on 2/1/04, you'd put "=#2/1/04#" in the criteria section (minus the qoutation marks). also, make sure you're selecting the info you need in the Fields area of the query builder. If all you're getting is a list of dates, you're probably only selecting the fldDate field or whatever. try selecting the name in one field, then in the next one over select the date and put in the date as your criteria. you should get a list of members and the dates they joined.

    Originally posted by wrighj1
    Hi, could someone please help me. I am press for time to make this happen. I have a database with the date that each member joined the
    church. I want to be able to specify the date members joined on a 30, 60, 90, day period in my Query. I have member that joined two month ago 2/1/04. I want to be able to pull these member from the table only. I want to be able to do this on a regular basic. I have tried
    to recall these member creating a Query using "2/1/04" in the criteria section when I run the Query its blank. I want to be able
    to go back a couple of year if I have to. When I run the query without the criteria I get a list of dates. I don't know if maybe my date format in mytable is wrong or what. COULD YOU HELP PLEASE!!!!!!

  3. #3
    Join Date
    Mar 2004
    Location
    Eden ,NorthCarolina
    Posts
    7

    Re: Query Request

    Originally posted by wrighj1
    Hi, could someone please help me. I am press for time to make this happen. I have a database with the date that each member joined the
    church. I want to be able to specify the date members joined on a 30, 60, 90, day period in my Query. I have member that joined two month ago 2/1/04. I want to be able to pull these member from the table only. I want to be able to do this on a regular basic. I have tried
    to recall these member creating a Query using "2/1/04" in the criteria section when I run the Query its blank. I want to be able
    to go back a couple of year if I have to. When I run the query without the criteria I get a list of dates. I don't know if maybe my date format in mytable is wrong or what. COULD YOU HELP PLEASE!!!!!!
    OK I have tried putting the date in by surrounding the date with the # key in front of date and at the end. When I run the Query I get nothing.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It may be easiest if we can see the file itself - dates are tricky business.

    Anyway, here's my crack at it:

    In the table, make sure the field data type is "Date/Time"

    Now, you're probably better doing this in a form, but if you want to use a query, there are a couple of straight forward ways:

    In the query, add a new field with this as the "Field":
    MemberDays: DateDiff("d",[JoinDate],Now())

    (replace "JoinDate" with the name of your field)

    This will give you a number, in days, between today and when they joined. Then you can enter in as criteria what rnage you want

    For example:
    > 60 and < 91
    will give you people who joined 3 months ago only, whereas
    < 91
    Gives you everybody from today till 3 months ago

    Have fun

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Smile

    Just noticed your "stormy" icon!

    You can smile now!

  6. #6
    Join Date
    Mar 2004
    Location
    Eden ,NorthCarolina
    Posts
    7

    Red face Calculating age

    Hello it me again here a sample of what I trying to do. I want to
    be able to calculate member birth date and pull up date a member joined
    on a 30 60 90 day period and yearly when need. I have tried to do the date a member joined in a Query but I'm getting a syntax error. Here is what my data looks like.

    Table Name is Ministry and its link to anothe Table with member address and phone in it, this table is called Noah's Ark

    Column Names:

    Dated Joined Birthdate Age
    7/1/04 15-Aug-40 need calculation for this field
    7/27/03 29-Mar-81 to get age every year
    10/20/02 2-Jul-80
    8/17/97 24-Dec-70
    1/1/04 15-Oct-60
    2/4/04 12-Nov-85
    1/25/04 6-Jun-70
    3/11/04 2-11-44

    When I do a Query to get date joined, my query is blank. I don't Know how to even started the calculation and where for the age. Can someone please help me!!!!!!!
    Last edited by wrighj1; 04-14-04 at 18:12.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok. First, I suggest using "code friendly names"

    For example, remove spaces and punctuation from your field and table names - Access will let you get away with it when naming things, but it can cause lots of problems down the road. "Noah's Ark" can be renamed "NoahsArk" for example.

    Second, make sure the field holding the date is of datatype "Date/Time" in the table.

    Next, keep in mind that when working with dates, the value should be between pound signs, much like text has to be in quotes. Example:
    #3/14/2004#
    "This is some text"

    Now, for query building, start with a basic select query - no filters, no equations. Create a query, add the table(s) and then drag the fields you want down into the "QBE" grid (the columns in the bottom of the design query window).

    View the query and confirm your data is showing up.

    Next, verify the date field is working. In the date field column, in the first criteria row, put the following line, exactly as I have it typed:
    >#3/1/2004#

    Run the query again, and you should only see records with a date more recent than March 1st, 2004. This will confirm the date field is working correctly.

    Finally, add the function I posted earlier as a NEW field. Copy it from this thread and paste it in a new field location. Again, open the query with no filters to ensure it works.

    If this fails, zip the file and post it.

  8. #8
    Join Date
    Mar 2004
    Location
    Eden ,NorthCarolina
    Posts
    7
    Thanks a bunch tcace I figure out how to get my members joined date. What I didn't understand how to go into the query and add a field to get result for the 30, 60, and 90 days. Oh yea, if going into the forms is better to do what I have to do can you explain that to me.

    Thanks In Advance!

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Attached is an example showing how to add fields to a query for the calculations.

    The contex is:

    NewFieldName: Function(function parameters)

    When refering to another field in your query as parameters in the function, enclose the filed's name in brackets. For example, here's the DateDiff function that tells me how many days your person has been a member:

    MemberDays: DateDiff("d",[JoinDate],Now())

    Calculating the age using these functions is a 2 step procedure becasue the DateDiff function rounds and when we speak of a persons age in years, we don't round. For example, if you were born in January of 1984, you are 20 years old, but if you were born in June of 1984, you are 19 (with respect to today being April of 2004). Here's the two I used (there are many ways to do it):

    AgeDays: DateDiff("d",[BirthDate],Now())
    AgeYears: Fix([AgeDays]/365.25)

    The only difference in using a function on a form is this:
    1) Instead of adding a field, you add a new text box
    2) In the text box's properties, set the ControlSource to:
    =Function(function parameters)

    You can reference other controls (notice I didn't say fields) on the form in your expression providing their names are enclosed in brackets. The name of the control matches the name of the field only if you created the form using a wizard or dragged the control onto the form from the filed list. So, make sure you check the control's NAME before referencing it!

    Note: Queries and Forms do NOT accept variable names in their expressions. If you try to use a variable in the expression, Access will assume you're using a field name.

    Also, if you use a variable as the search criteria for your query, it won't work - Access will assume it's a literal value, not a variable.

    To use a variable in a query, you have to use a public function, which requires some coding.

    Good luck
    Attached Thumbnails Attached Thumbnails sprite 6.jpg  

Posting Permissions

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