Dates (1 Viewer)

Kundan

Registered User.
Local time
Today, 04:19
Joined
Mar 23, 2019
Messages
118
In the attached DB I have to enter dates in the field REMARKS. I want to ensure that the dates are entered in the format dd-mm-yy and that the date is not less than today's date. How to achieve this?
 

Attachments

  • Database1.zip
    81.9 KB · Views: 126

namliam

The Mailman - AWF VIP
Local time
Today, 12:19
Joined
Aug 11, 2003
Messages
11,696
You dont, how you solve this is make a related table that has a date field and a remark field for that day.
 

Kundan

Registered User.
Local time
Today, 04:19
Joined
Mar 23, 2019
Messages
118
Isn't there any way to check in the present structure?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:19
Joined
Aug 11, 2003
Messages
11,696
write your own vba procedure to parce and check the content the remarks...

However forcing formatting of free text fields is a constant and everlasting nightmare
 

bob fitz

AWF VIP
Local time
Today, 11:19
Joined
May 23, 2011
Messages
4,713
Isn't there any way to check in the present structure?
Most things are possible but some are better than others. IMHO it would be better to follow the advice given by namlian, in a previous post, to create a related table for the remarks and dates.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:19
Joined
May 21, 2018
Messages
8,439
Code:
I want to ensure that the dates are entered in the format dd-mm-yy and that the date is not less than today's date.

There is no purpose behind trying to force a format on a date for entry. Today's date is internally stored as 43846 regardless of what format is applied. The formatting is only on the display side.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:19
Joined
Feb 19, 2002
Messages
42,872
Really, how hard is it to add an additional field for the date to separate it from the text string???????????????????? What is the argument all about? Do it right the first time, and never worry about it again.
 

Kundan

Registered User.
Local time
Today, 04:19
Joined
Mar 23, 2019
Messages
118
Really, how hard is it to add an additional field for the date to separate it from the text string???????????????????? What is the argument all about? Do it right the first time, and never worry about it again.

How do I incorporate multiple dates as shown in the attached DB?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:19
Joined
Feb 28, 2001
Messages
26,946
As I suspected from your question in post #12, that design is not completely normalized. By placing an arbitrary number of dates in the Remarks text box (any amount from no dates at all to more than two) you have in effect created a repeating group.

If that remarks field remains as a text string then you have the issue that you CAN'T do what you want when the second date gets added without splitting the field, parsing the new entry, and re-merging the field. You CANNOT validate a multi-date string-oriented field without a thorough knowledge of text parsing.

The simplest way with what you built would be to enter the date as a STRING separately and parse it for validation, after which you could concatenate multiple strings. Otherwise you would have the problem that with an arbitrary text string in that field, your user could in theory place the cursor ANYWHERE in the string and add stuff that violates the date formatting of prior entries.

Using a text field as a list of dates (to be validated) in Access is a perpetual nightmare of text parsing. You need to enter those dates in a separate child table that is one-to-many with the records you show in the form.

That is not all... those dates in that format, particularly once it becomes multiple dates in the same box, CANNOT be directly linked to anything else in the DB and, given date sorting issues, will not be easily testable for ordering.

Your form and this design show common Excel-style thinking. This is not a desirable design for Access databases because Access is not Excel. Further, as difficult as this would be in Access, the "validation" part in Excel would be at least as bad if not worse.

How do I incorporate multiple dates as shown in the attached DB?

The dates have to go in a child table, one date per row, because in that format, you can validate dates in fairly specific ways yet still have a list.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:19
Joined
Feb 19, 2002
Messages
42,872
Each comment should be a separate ROW in the table:

CommentID
ForeignKey (if necessary)
CommentDT
UserID
Comment

That gives you all the control you need. You can find comment made by a specific person or during a specific date range. You only have to look at the comment itself if you need to find text strings.

Mushing things into a single field saves NOTHING!!!! and causes extra work to boot.
 

Kundan

Registered User.
Local time
Today, 04:19
Joined
Mar 23, 2019
Messages
118
Each comment should be a separate ROW in the table:

CommentID
ForeignKey (if necessary)
CommentDT
UserID
Comment

That gives you all the control you need. You can find comment made by a specific person or during a specific date range. You only have to look at the comment itself if you need to find text strings.

Mushing things into a single field saves NOTHING!!!! and causes extra work to boot.

In the attached DB I have made a modification in which I can enter the details in a pop up form. I want the form to open in Datasheet view, but it is opening in form view I don't know why? Please guide me.
 

Attachments

  • Database1.zip
    72.1 KB · Views: 109

isladogs

MVP / VIP
Local time
Today, 11:19
Joined
Jan 14, 2017
Messages
18,164
Just tested. Both forms open in datasheet view
 

bob fitz

AWF VIP
Local time
Today, 11:19
Joined
May 23, 2011
Messages
4,713
In the attached DB I have made a modification in which I can enter the details in a pop up form. I want the form to open in Datasheet view, but it is opening in form view I don't know why? Please guide me.
Use the fo;;owing command to open the form:
Code:
DoCmd.OpenForm "Frm_REMARK", [COLOR="Red"]acFormDS[/COLOR]
 

Kundan

Registered User.
Local time
Today, 04:19
Joined
Mar 23, 2019
Messages
118
I have written some more code but it is not working. Please guide me.
 

Attachments

  • Database1.zip
    74.9 KB · Views: 116

Users who are viewing this thread

Top Bottom