Entering Text into a Numeric field 2

[kjv1611]

I have a table which has a form built off of it. I am aware that you cannot enter a String value into a numeric value location of a table (via a form or manually). My question is this:
Is there a way in VB coding (or other way) to allow the user to enter a value which does not match the default value listed in the table?
Here is my situation as an example:
Two of my fields on the table are numeric, but there are situations where I would like to be able to enter "N/A" as a string value instead. One is a date format, and one is a currency format. These fields are for a due date and due amount. On some occasions, there would not be a due date or due amount when the database would request such information. Most situations where the information will be requested, the data will be in the numeric format mentioned for each field, it's probably only a 20% issue, but 20% is big enough of an issue to me.

Any ideas out there on how to solve this situation?

 

[tracy1234]

Why are you opposed to storing a null for fields that are N/A? I don't think it is possible to store mixed datatypes as you suggest.

-Tracy

 

[Edski]

Yes, I agree with Tracy. Leave the fields blank (Null, Missing, Empty - whatever you want to call it). When it comes to printing reports and stuff you can change them to whatever you like ("N/A", "***", "---" etc) using vba code.

The problem with this is that there could be many different reasons for the Missing fields, such as "Not Appropriate", "Not available", "Available but unknown", "Known but not entered yet", etc. etc.

The solution is to have another field (Numeric) which lets you store the type of missing value. Then use this field where necessary in your reports.

Does that help?

 

[BNPMike]

Access is designed with built-in facilities for "n/a" data values ( which is exactly what nulls are). It would be quite pointless to ignore these and build your own.

 

[Edski]

Mike,
Really? As far as I know Access only differentiates between Null and zero-length strings. Can you elaborate on these "built-in facilties"?

 

[BNPMike]

I'm talking about nulls and how they are treated by eg Jet in joins, where clauses and so on. Using your own 'N/A' or whatever means you've got to program your own if conditions all over the place.

An analogy is in 1999 people would try and reject a system that was going to display 2000 dates as eg '1/1/0'. They said it's got to be '1/1/00' because they'd always had to put 2 year digit during their lives. Possibly here the issue is someone wants to see 'N/A' or '-', and finds a blank (ie null) as not satisfactory. Things like this cost trillions of pounds/dollars/euros every year. I try on this channel to dissuade people from customising packages like Access. It's better to spend the time on something of real business benefit rather than pure decoration.

When you buy car you wouldn't dream of spending thousands changing all the dials, knobs and levers for ones identical to your previous car. But in IT, it happens all the time.

 

[kjv1611]

Thanks for all the advice. You all seem to be saying the same thing, just in different ways. The idea of coding it to provide different info for different reports is a good idea, though. The information is something that could be required (but not made available anyway) in some cases, and that is mainly where I would like to focus. So, maybe some code behind the whole thing will be my best go there. The database is for auditing purposes, and in some cases, the numerica data should be there but does not matter - whereas in others it should be there and does matter, yet there will be some records in the same table to where the user/auditor cannot input the data at all, b/c they will not see the option, as it has no bearing on the audit results for that record. Again, thanks for all the input - seeing it put in different ways from different point of views is always helpful.

 

[BNPMike]

Good luck KJ