Any MS Access Guru's out there who can help?

fitshase

I am creating a database for work and on a few of the fields I need to either have a date or "N/A". (e.g., if it's a ground based site we don't need to instruct a structural engineer so the data in "Structural Engineer Instructed" would be "N/A". If it's a rooftop site, the data would be ##/##/####).

I don't want to leave it as a text field as people will start to insert dates with different formatting (e.g., 01.02.10, 01/02/2010, 01/02, etc) so I would like to either use Validation or the Input Mask. I also don't want the field to be left blank if it is not applicable as the date field would then be queried to see which was blank and those would be chased.

Is there a way to do this?

oldtoolie

I just dabble with Access -- there are forums with experts.

However, it seems to me that you are trying to put two different elements of data into one field and that doesn't work.

I would try making a field with a dropdown list with 'engineer required' or 'engineer not required' making selection of one of these mandatory. That would insure that a decision was made re an engineer. Then have another field to record the date an engineer was instructed.

You could have a report that would show all of this data and show which jobs needed to have an engineer assigned.

There may well be a more elegant way to do this but this would work.

Darksun

Can you not set it as date format, but don't make it a required field? Not sure exactly how to do this in access but I'm sure it's possible.

That way it can just be left blank if not required

fitshase

oldtoolie wrote: »

I would try making a field with a dropdown list with 'engineer required' or 'engineer not required' making selection of one of these mandatory. That would insure that a decision was made re an engineer. Then have another field to record the date an engineer was instructed.

You could have a report that would show all of this data and show which jobs needed to have an engineer assigned.

There may well be a more elegant way to do this but this would work.

I had thought of that but was hoping there would be a way in order to cut down on the amount of fields. I have about 15 fields which are either a date or N/A.

Can you not set it as date format, but don't make it a required field? Not sure exactly how to do this in access but I'm sure it's possible.

That way it can just be left blank if not required

If it is blank then when the report is ran, the blanks will be highlighted to chase up an engineer instruction. If it is N/A, it will not show.



I may just have to bite the bullet and add the extra fields. Just means more work and more complicated queries.

Darksun

Well, one solution that is far from ideal is to use a 'dummy' date to represent N/A, for example 1/1/1900

RobTang

These sort of "issues" crop up when your data model isn't normalised properly or not enough to a degree you need.

Of course I can't really say too much without knowing what you need to capture but I would suggest going back and re-normalise your data.

Dont use hacks like adding a dummy dates UNLESS you consider the implications and do it as an active decision rather then just a hack to get it working,