Problem with Delivered date query

[hardrock]

hey all, I have a query with a few columns one of which is a called "Delivered" . Delivered is a check box entry, which at the moment has its criteria is set to "False". The report this query generates will only show records that have not been delivered. i.e Outstanding records, because Delivered = False

I want to modify the query such that if the user makes "Delivered" = True the records will still show as Outstanding (false) for 7 days.

How do i set my query to obey this rule? Thanks

 

[rainman89]

you would probably need a delivered date that was filled in when u click the button. then u would use a dateadd function for the delivered date field.

if dateadd("d",7,[delivereddate]) <Date() then
delivered = false
else

thats the only way i could think that would do it

 

[hardrock]

re

Ok when i save the record now and if delivered = true, it will write the del date date in the DELREC column in my table. The code below is great to test the condition of one record, by my table consists of about 100 records. i loading my table into a query, so its there where i am trying to test the conditions. Maybe a loop test conditon is needed in my table?

 

[rainman89]

you would put it in a seperate column in your query, and it will do it for all of the records

 

[hardrock]

re

sorry i am a noob mate! where do i put the code ? in the criteria? coz it looks like vba to me? cheers

 

[rainman89]

how are u going to be showin the results of your query? through a form? or through a report?

 

[hardrock]

re:

In a report mate. At the momemt a button on my form opens the report.
The report gets its data from a query. The query gets its data from a table. The query presently has its "delivered" criteria set to "False" thus it only returns outstanding records. I now want to add the feature as the top of my post says. Thanks

 

[spasticus]

hey hardrock.

take out the criteria in you query for delivered = false (leave it blank)

for the example lets say you have text box on the form with the open report button (where the user enters true or false) called txtUserValue

on the click event of the open report button put

DoCmd.OpenReport "Query1", acPreview
Reports("Query1").FilterOn = True
Reports("Query1").Filter = "[Delivered]=" & txtUserValue

if you open the query in design view and set FilterOn to yes you can take out the second line.

 

[hardrock]

re:

Hey Spasticus, your bit of code will filter if true or false. The fact remains there is no condition to make "DELIVERED" False after 7 days after the delivered check box is ticked on my form. Remember, i have to test the condition of about 100 records once the open report button is pressed.

At the moment. If the user clicks the delivered check box on the form, the deldate is entered into the table in field "DATEDEL". If the item is not delivered, this field remains blank.

I think the solution here is that when the click open report button is pressed, i need a bit of code to test all records for

a) DATEDEL is not null
b) DATEDEL = deldate + 7 days

The outstanding records on the report should not be shown if both conditions are true. Is this clearer?

 

[rainman89]

can you post a sample db for us to look at? you can remove your records if u wish

 

[spasticus]

im a bit confused.

a sample db would be helpful.

do you want to change Delivered from true to false 7 days after it is set to true?

what do you want the report to show? Just Delivered = true or false records?

... or Delivered = true and [date it was set to true is 7 days ago]?

 

[GaryPanic]

we are all a bit confused on this one ...

You want on your report all outstanding (not delivered) yes and ?
all those delivered for the past 7 days

if this is so what you need is 2 qry's and then join them

you have figured out the outstanding - thats easy


if its the and delivered witihn the last 7 days again this is easy

you need to have a date field on your table

and on your print option have a option to include all delivered options for the past x days - now if your report is on a sepearate form you can then have this as an option so you can increase/decrease the days


right as it has been explained what you need to do is remove all filters and run report - does this give the info that you can then filter on
1 delivered - true/false or yes/no or 0 /-1 whatever way it shows
and then there must be a date field

this will be blank for the not delivered and should have a date in it for the delivered
we are still looking at raw unfilter data at this point ....
now you need to save copies of your queries one for the not delivered and you have sussed this one out already ...

the other one requires a little thinking over as you only want those that have been delivered within a time frame - then you need I think (Guys correct me if I am wrong) a union qry to join these together - as these are really the same qry but with different filters this should work

.....
g