Query Date Issue

[healey21]

I have a query which I use to show items that haven't been completed, and use a date field as the checking field. What I do is set the Criteria to show Is Null which should (I believe) only show items with no date in the list, but for some reason if a date is added it doesn't refresh the list.

Is there an alternative for a Date Field to show only empty records with no date in this field.

Name of Field is ReportDate Format set to Date/Time and short format. In the query it is set to Is Null.

Any guidance will be useful.

 

[Norie]

How are you trying to 'refresh' the query?

If you close and re-open it then it should reflect any changes.

 

[healey21]

I am using 2 forms, one that shows the actual record, and on the record is the date field, I then have a form with a continued list and once the form is complete and and then closed the form which shows the list should have the record removed from view.

I have tried several requery options and refresh in the main form, but occassionaly the list still shows records that should have meet the criteria.?

 

[Norie]

What requery/refresh options did you try?

Do you open the form with the actual records from the 'list' form via a button or something?

If you do you could set things up so that when that happens the list form closes as well.

Then the next time the list form is opened the data should be updated, provided the record has been saved.

 

[healey21]

Sorry for delay in replying I refresh behind the form Close Button, but I will need to check tomorrow to see if it is saving before refresh, that may help me.

I will post back tomorrow.

Thank you Norie for the reminder to save first.

 

[healey21]

This is strange, I checked the form to see if I had added the DoCmd.Save and it was there, I then checked any record that hadn't been selected previously and once I added the correct criteria in the form it clears down of the other form correctly.

But when I check for another set that has already to been updated to the same criteria, ie checkbox (clicked) and dateField has a date in, then despite what I am doing it still remains in the list (there are about 20 records).

I have checked back to the table and the records and they show the correct data but won't disappear of my form or from the query !

I'm stumped.

 

[healey21]

After further investigation it would appear that 2 records with the same ID (in this case Policy Numbers) are being shown, so when they appear on the continued list form and then use the view button to open it will show both records. So I have gone back to the guys and asked what do they want to do about it. So my criteria was correct and all options were correct.

 

[Norie]

So is it sorted?

By the way why are you using code to save the record?

Usually records are saved automatically when using bound forms.

 

[healey21]

Hi Norie,

I have told the guys what the problem seems to be and am awaiting a reply so will see what they say.

I added the DoCmd.Save only after I didn't see the record coming off the list, I understand it is bound so shouldn't need to add this in, come to think of it I don't need the Requery either.

But I have to wait until they reply with what they want to happen next.

For now this thread can be closed.

Again thank you for reading and making very sensible suggestions.