Counting Multiple Dates in a single Field.

[Woblypegs]

Hi, I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is enter 2 dates and have access return how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done. I hope this isn't as confusing to you as it looks to me. Many thanks for any help offered.



Wobly

[ssanfu]

This is not a normalized structure; it violates 1st normal form. See http://www.studytonight.com/dbms/dat...malization.php

The field is a large text format because there could be anything from 1 date to hundreds

If you have multiple dates for one field, consider using another table. Much, much easier to be able to count using a query or VBA.

[orange]

As Steve has said, your structure is not normalized. You will be in continuous "workaround mode" if you don't correct this basic flaw.
You need to research database principles and design. Getting a clear description of your business requirement, and ensuring that your tables and design meets the requirement is key to a successful database application.

Access has a date/time data type which you should use for Dates.

You really need to work through some tutorials regarding design.
Here are a few links that will help you:
don database principles
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck with your database project.