Access "Clear Contents"

[tdpman]

Is there a way to have access clear the contents of certain fields based upon a set criteria? I don't want to delete the entire record. I just need to clear the field if it equals "00/00/00" or "EMPTY".

Any ideas would be most appreciated.

Thanks!

 

[OhioSteve]

I assume that you are talking about changing the contents of a form field- not the contents of a table. Changing the contents of a form field really raises two questions. First, what code will you write? Second, what will trigger the code?

Let me give an example. Let's say that the secretary in your office always spells Xerox with a "Z". She puts this in a field called "companyname". You want to correct this error automatically. You could write this...

If Me.companyname = "Zerox" Then
Me.companyname = "Xerox"
End If

That code would work well. But then you have the second problem, what should trigger the code? I put the code in this example in the field's on exit event. That worked pretty well. However, all of the events occur at slightly different times. It seems that no matter which event you choose, it does not cause exactly the response that you are seeking. So the second question is really the tougher one.

 

[tdpman]

Would this be something that I could add into an existing query? I have an existing append query that copies data from one table to another. Could I put something in that query to clear the data if it equals "#EMPTY" or "00/00/00"?

 

[OhioSteve]

Yes you can do the same task in a query. However, queries usually deal more with data in tables. Initially, I thought that you wanted to change data in a form. Perhaps I misunderstood.

In a query, you can use the iif function to do this task. This is iif...

iif(A,B,C)

"A" is a test that can be resolved to T or F.
"B" is the value if the test is true.
"C" is the value if the test is false.

Lets go back to the previous example. This time, we will just let the secretary misspell the word. However, each evening we will run an append query that appends today's records to an archive table. We want to stop the mispelling before it gets into the archive table. The field is called "input name" in the first table and "companyname" in the archive table. In the append query, you would use iif as follows:

x1:iif([input name]="Zerox","Xerox",[input name])

X1 is just a random variable name. Remember that you will designate the destination table in the "append to" row.

The logical test compares the input name field with the string "Zerox".

If they are equal, you get the string "Xerox".

If they are not equal, you get input name.

 

[tdpman]

Sorry for not being clear earlier. Yes, I have a table that has a field in it. This field will contain dates. However, the text file that I import into this table contains items such as "#EMPTY" and "00/00/00". I would like to either run a query to clear this content or a piece of code that I can include in a macro to clear this content out.

 

[tdpman]

I'm not sure how I could make an Iif statement work. The field will also contain valid data that I would like to keep. I just need to clear out the data that reads "#EMPTY" and "00/00/00".

 

[tdpman]

Well, actually now that I have played with it a little bit I think I may have figured it out. I can do an Iif statement, saying if it does not equal "#EMPTY" or "00/00/00", return the original data and if not, leave it blank.

Thanks for your help on this!

 

[OhioSteve]

no problem