Results 1 to 6 of 6
  1. #1
    l3111 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    38

    Exclamation Parameter Query on a calculated field

    I have a date (Actual Discharge Date) and I need to calculate this date + 90days. I have acheived this using DateAdd("d",+90,[ActualDischargeDate])
    I need to however run a parameter on this newly calculated cell. I need to see how many records reach thier 90day date within a reporting period.
    I have many parameters within my database, but when i enter:
    Between [Reporting Period Start Date] And [Reporting Period End Date, the query just retuns all the records, no criteria seems to have been applied.
    I've searched the web, high and low, but cannot find an ansewer i understand, as i do not use the SQL side of queries, just the handy to use sections.
    Any simple help please?



    l3111, Manchester, UK

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Are you entering this:
    Between [Reporting Period Start Date] And [Reporting Period End Date
    . . . in the Criteria row of your Query design?
    There's a closing ']' missing . . .

    Can you look at the top left of your Access screen and click 'View' and then 'SQL View' - and then paste the SQL code you see in that window here so we can look at it?

  3. #3
    l3111 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    38
    Hi, here is the SQL from my query,

    SELECT tblReferrals.RAISENumber, tblReferrals.Title, tblReferrals.Name, tblReferrals.Surname, tblReferrals.Gender, tblReferrals.DOB, tblReferrals.ReferralDate, tblReferrals.ProgrammeStartDate, tblReferrals.ActualDischargeDate, CVDate((DateAdd("d",+90,[ActualDischargeDate]))) AS [90 Days], tblReferrals.HCHoursBeforePerWk, tblReferrals.HC, tblReferrals.HChrspw, tblReferrals.OutcomeOnDischarge, tblReferrals.Completion
    FROM tblReferrals
    GROUP BY tblReferrals.RAISENumber, tblReferrals.Title, tblReferrals.Name, tblReferrals.Surname, tblReferrals.Gender, tblReferrals.DOB, tblReferrals.ReferralDate, tblReferrals.ProgrammeStartDate, tblReferrals.ActualDischargeDate, tblReferrals.HCHoursBeforePerWk, tblReferrals.HC, tblReferrals.HChrspw, tblReferrals.OutcomeOnDischarge, tblReferrals.Completion, tblReferrals.ReferralDeclined
    HAVING (((CVDate((DateAdd("d",+90,[ActualDischargeDate])))) Between [Reporting Period Start Date] And [Reporting Period End Date]) AND ((tblReferrals.Completion)="Yes") AND ((tblReferrals.ReferralDeclined)=False));


    When the criteria isn't there it shows 107 records, but whn i add the criteria it shows 51 records, but they do not fit in to the criteria i entered.....

    Many Thanks

    l3111, Manchester, UK

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    1. May I ask what the purpose of your Grouping is?
    Sometimes, applying Grouping can have unintended results.
    I can't say that in your case there is anything 'wrong' with the SQL - since I don't know the reason why you're Grouping.

    2. Can you post a copy of your database here so I can look at it myself?
    If you ARE able to post a copy here for me to look at - tell me specifically what you want to see when you run your query.

  5. #5
    l3111 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    38
    Hi Robeen

    Unfortunatly I cannot upload a copy of my database due to confidentialty reasons

    However I can explain in more detail what kinds of data my database holds and what result i am aiming for from this query.

    I work for a local government within the UK and we run a service where (mainly) older people recieve a programme of care from our Reablement service. It aims to increase thier independence and confidence after a stay in hospital or a fall. At the end of thier programme we would hopefully discharge them from our service as independent, however some will require a small package of professional care from the independent sector, others will be admitted to hospital, respite or residential care.

    As a performance measure we would like to know the status of completed customers 90 days after they were discharged from the service.

    I have created this query using the only table in the database (tblReferrals) and inserted the required fields. I have entered a filter on the Completion field as we only want to contact customers who DID complete their programme. I have entered False under the ReferralDeclined field as I do not want the query to return customers who we declined from the service (these are recorded for another of how performance measures)

    The only other filter i require is parameter on the 90day calculated field. I have acheived this by using the DateAdd function and this works correctly, adding 90days to the day of discharge. However, this report will be run every friday, and the parameter query will need to run to only show which customers have reached thier 90 day date within that week. For example using the table below (copied direct from my query), if the query was run this friday (14/10/2011) using the dates 10/10/2011 -16/10/2011 only customers 2,3,4 should be shown. However, the parameter is not working. However it does filter, but not applicable to these dates.

    Customer Gender Actual Discharge Date 90 Days
    1 Male 11/07/2011 09/10/2011
    2 Female 12/07/2011 10/10/2011
    3 Female 13/07/2011 11/10/2011
    4 Male 15/07/2011 13/10/2011
    5 Female 21/07/2011 19/10/2011

    I hope this helps.

    l3111, Manchester, UK
    Last edited by l3111; 10-12-2011 at 08:32 AM. Reason: Table format changed when I posted reply

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'm afraid I can't explain the problem.

    I'm not sure if this will help - but it's worth a shot.
    instead of doing everything you need in the one query:
    1. Use the query you posted to give you everything BUT the parameters.
    2. Make sure you are getting all the data you need [minus your date-range parameters].
    3. Create a new query ON THE ABOVE QUERY & pull in all the fields.
    4. In the NEW query - add your date-range parameters.

    Without looking at the actual data, I can't really tell [I'm not that good ]

    Are you able to make your date-range parameters work on other queries that you run?

    The thing that 'troubles' me is that your DateAdd function AND your parameters might not be compatible - when run int the same query. Try doing the DateAdd and other criteria in on query - and then make another query in which you can add the date range parameter prompts.

    I've had similar issues when I've tried to do one too many things in a query.
    And I've found that when I just do an extra query & add the last step into it - then things work again.

    I hope I've been able to help.

    If this doesn't work, is there a way you can copy your database - keep all the functionality [the table & query] - but just create a few fake rows of data that you can then post?
    That way I can see the problem & troubleshoot it - without seeing any real data?

    All the best!!
    P.S. What's the weather like in Manchester? I have family who live in Essex. I lived in England myself for 4 years - I'm in the US now - it's not quite as 'damp' ! ! ! ! .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Query with a Calculated field
    By Lorlai in forum Queries
    Replies: 3
    Last Post: 09-21-2011, 10:57 AM
  2. Replies: 2
    Last Post: 12-03-2010, 09:33 AM
  3. Replies: 1
    Last Post: 05-05-2010, 01:54 AM
  4. union query with a calculated field
    By grad2009 in forum Queries
    Replies: 9
    Last Post: 03-31-2010, 04:50 PM
  5. Replies: 4
    Last Post: 03-05-2010, 09:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums