Hi:

I have a table setup to enter dates for each person that requires safety quizes for the month those that don't have an "N/A" for the month by thier names.

Each year I have to start over and it is a real pain to manualy delete all the dates for each person for Jan through December.
I only want the dates gone not the name or the N/A values.

Is there a sub or query that will search each record and only delete the dates?

Thanks
Charles

Is there a sub or query that will search each record and only delete the dates?This is what a delete query is for. You can use criteria in it too. Check it out...

This is what a delete query is for. You can use criteria in it too. Check it out...

How do you create a delete query?

Charles

This is what a delete query is for. You can use criteria in it too. Check it out...

How do you create a delete query?

I have gotten as far as criteria but I don't know what to put since all the dates will be differant.
I tried <> "N/A" but that didn't work.


Charles

Give me a snapshot of the table with data in it...

Here is an example of the table.

Shift...Name....Lang .... Jan .......Feb..... March.......April.......May.....June
A.......John......E.......1/02/08....N/A....03/02/08....04/02/08.....N/A.... 05/02/08



Hope this helps.
There is about 600 records.

CHarles

Here is a function:dim rs as recordset
dim fld as field
dim i as integer
set rs = currentdb.openrecordset("table", dbopendynaset)

rs.movelast
rs.movefirst

with rs

while not .eof

for i = 4 to rs.fields.count - 1

if not fld(i) = "N/A" then
fld(i) = NULL
end if

next i
.movenext

wend
.close

end withHope it works! :)

It looks like the code would make the N/A values null.
I want to make all the dates null and leave the N/A's

Also where should the function go?
Should I call it from a module via a command button on a form?

Charles

I changed the code MrData. Sorry about that!

I used the format function, because your fields can't be formatted as a date data type if you have string values in some of the records. Is the type TEXT?

I don't care where you put the function call, but the actual code goes in a module. Call it Public Function "whatever name you want". You can run it in the module, or call it with a button, or whatever else you want to do.

Here is a function:dim rs as recordset
dim fld as field
dim i as integer
set rs = currentdb.openrecordset("table", dbopendynaset)

.movelast ' Errors Out Here
.movefirst

with rs

while not .eof

for i = 0 to rs.fields.count - 1

if format(fld(i)) = "##/##/##" then
fld(i) = NULL
end if

next i
.movenext

wend
.close

end withHope it works! :)

The code errors out on the line .movelast
The error states "Invalid or unqualified reference"

I put the code in a module called it Function Clear()
I put this line in the sub of a commandbutton "Call Clear"
I may be doing something wrong.

Charles

The code errors out on the line .movelast
The error states "Invalid or unqualified reference"

I put the code in a module called it Function Clear()
I put this line in the sub of a commandbutton "Call Clear"
I may be doing something wrong.

CharlesI changed it. It should work now. The change is in post #7. I forgot to put the actions in the WITH clause. Copy it again and then try it. The code is written correctly now.

The code as written generates this error "Syntax error in FROM Clause".

If I change the line that has the word "table" to the name of the table it gets pass the above error but stops on this line.
If Format(fld(i)) = "##/##/##" Then , with this error "Object variable or with block variable not set.

I was not sure if you wanted me to substitute "table" for the table name I just tried it to see what would happen it may have errored on the second line anyway had it not errored at the line containing "table".
I hope this makes sense I appreciate your help on this.

Charles

Yes it makes sense...and yes, I wanted the substitute. I thought that was a given, but I guess not... ;)

Make a backup of your table, and give the new version a try. I updated it. If that doesn't work, then there is something you are not telling me about the table. The code is based off of the sample matrix you have in one of the previous posts. Also, what is the data type of those DATE fields? Text?

And BTW, the stuff I wrote before shouldn't have worked. I pretty much slopped that stuff together, and it shows. Sorry...

I'm confused as to where the code is necessary. This SEEMS from the original question to be a simple update query if the individual's record is re-used (not keeping history) or a simple update query to mark all quiz records as "out of date" past a certain point. (Add a field to the quiz records... "QuizRecent" and make it yes/no.)

Then the person who has no "recent" quizzes needs another quiz.

The distinction is whether you want to keep history records. That governs whether you would just keep a quiz entry in the person's record (where you find that "N/A" marker) or whether you make a join to a quiz history table.

As to deciding whether someone needs a new quiz, add that "QuizCurrent" flag. Then at the beginning of the year, reset all such flags to "FALSE" in an update query. (Fancy version, do it once a month and reset flags only for those with dates more than 1 year old.) Now when you are about to test for whether a person is up to date, you can do a DCount of quiz history records for that person where the QuizCurrent flags are TRUE. If the answer is 0 (no records are current), then that person needs a quiz!

The function is not necessary Doc-Man. It gets the job done, and that's all...

If Format(fld(i)) = "N/A" Then , with this error "Object variable or with block variable not set.
The table feilds with the dates are set to Text
I did forget one feild in the example sorry.

Shift...Name....Job Title....Lang .... Jan .......Feb.....March.......April.......May.....Jun e
..A.......John.......Fixer........E.......1/02/08....N/A....03/02/08....04/02/08.....N/A.... 05/02/08


I appreciate all your help

I am keeping no history I just want to clear out all the dates and leave the N/A's each year when I start over.

Thanks
Charles

Charles,

read post #7's code again. There is no format() function in it anymore. It should work! Try it!

If it doesn't upload the database and I'll do it for you... :o