Data Validation Concatenated Fields
- Thread starter keith701a
- Start date
If the field is already a date/time, then the value stored is numeric.
If you have another date/time that you are comparing to, you should not need to concatenate.
While you, the user, see something like "1 January 2018 6:00am" the data itself is stored as something like 42653.25, NOT as text.
If you have another date/time that you are comparing to, you should not need to concatenate.
While you, the user, see something like "1 January 2018 6:00am" the data itself is stored as something like 42653.25, NOT as text.
Pat,
Very true, but in that case the OP should be referencing strings, not Date/Time.
Also, depending on format you can get some very very very strange results as they would be sorted by alpha order, i.e. February is be January if you go off of Alpha. Also he would need to make sure both are formatted the same.
Very true, but in that case the OP should be referencing strings, not Date/Time.
Also, depending on format you can get some very very very strange results as they would be sorted by alpha order, i.e. February is be January if you go off of Alpha. Also he would need to make sure both are formatted the same.
To ease data entry, I have times and dates entered in separate fields. For example, I have received_date & received_time(24 hour) concatenated into received.
I'd like to validate the concatenated field received against another concatenated date/time field.
I'd like to validate the concatenated field received against another concatenated date/time field.
- Local time
- Today, 13:47
- Joined
- Feb 28, 2001
- Messages
- 30,908
The question is how they are stored; specifically, the data types of each field. Depending on how they were stored, your answer will differ.
It is, of course, your choice, but to store date and time as two separate fields for the same event very slightly wastes space. Of course, for convenience sake, you do what you feel you must - but if they were all combined in a date field, you could compare the two event date/time combinations in a simple IF [Date1]>[Date2] kind of statement.
It is, of course, your choice, but to store date and time as two separate fields for the same event very slightly wastes space. Of course, for convenience sake, you do what you feel you must - but if they were all combined in a date field, you could compare the two event date/time combinations in a simple IF [Date1]>[Date2] kind of statement.
All of the fields are saved/stored as Date/Time.
- Local time
- Today, 13:47
- Joined
- Feb 28, 2001
- Messages
- 30,908
OK, the simplest answer with what you have is
Code:
If ( [Date1] + [Time1] ) > ( [Date2] + [Time2] ) Then ...
{was greater}
Else
{was less or equal}
End ifIf I wanted to make a custom date & time field that had a short date + short time, would I format it as mm/dd/yyyy;hh:mm?
What would removing the format achieve? I understand the concept that Access saves dates & times as integers. I just want to restrict users into entering the date & time in that format. Should I just do that with an input mask?
The answer depends on how you want to store the data.
Are you storing in a string or are you storing in a Date/Time?
If you do that in a string it will save in exactly that format. If you store in Date/Time then the format is not relevant.
If you DO decide to save it in a string I would recommend YYYYMMDDhhnn format (remember, minutes are n not m) so that you can sort by that format.
If you are saving in a Date/Time then the format is for display / entry only and has no bearing on how the data is actually stored.
I'm storing in Date/Time. I'd just like users to type the data as MM/DD/YYYY hh:nn.
Hopefully this little example will help you with that.
You can use one entry that is formatted for data and the other formatted for time, just look at the conversion function in how you can break it out again for udpates.
- Local time
- Today, 13:47
- Joined
- Feb 28, 2001
- Messages
- 30,908
Technically, no. A DATE field or variable is a typecast of DOUBLE (64-bit floating point).
The format is a DOUBLE expression of days and fractions of a day since midnight of the reference date of 31 Dec 1899 at midnight. That is date/time zero. I would have to look it up to be exact, but we are about 42600+ days past that reference time. So lets take Mark_'s example in post #2 of the thread as being accurate.
The format of a DATE variable is a DATE which is a typecast (alternate interpretation) of a DOUBLE. When you compare two dates, you are comparing two relative distances from the same date to the two dates in question. So the difference between the dates cancels out the common reference date, leaving only the actual elapsed time between the two dates. It is a purely mathematical comparison when done that way.
That other stuff with MM/DD/YYYY hh:nn is just a display date or an input mask used for convert dates to or from strings. Internally, you do better with dates. They are faster and easier to compare and take up less space, since a date is 8 bytes as a DOUBLE but MM/DD/YYYY HH:NN is 16 bytes as text.
Oh, ... just for clarification: If you did a Short-Date formatted printout of your date and time variables, the date variable would be xx/yy/zzzz 00:00 or xx/yy/zzzz 12:00 AM. The time variable would be 31-Dec-1899 hh:nn.
Similar threads
