Data Validation Concatenated Fields (1 Viewer)

[keith701a]

Is there a way to validate data between concatenated fields?
I'd like a concatenated date/time field to validate as ">=" another concatenated date/time field in an entry form.

 

[Mark_]

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.

 

[Pat Hartman]

NOT as text.

UNLESS you formatted it with the Format() function. Format() converts a date to a string and that makes it act like a string rather than like a date. A date string will NOT sort or compare the way you would expect it to. Strings sort/compare character by character, left to right.

 

[Mark_]

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.

 

[keith701a]

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.

 

[The_Doc_Man]

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.

 

[keith701a]

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.

All of the fields are saved/stored as Date/Time.

 

[The_Doc_Man]

OK, the simplest answer with what you have is

If ( [Date1] + [Time1] ) > ( [Date2] + [Time2] ) Then ...
    {was greater}
Else
    {was less or equal}
End if

 

[keith701a]

OK, the simplest answer with what you have is

If ( [Date1] + [Time1] ) > ( [Date2] + [Time2] ) Then ...
    {was greater}
Else
    {was less or equal}
End if

If 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?

 

[Pat Hartman]

mm/dd/yyyy;hh:mm

I believe the format is:
mm/dd/yyyy hh:nn

"nn" for seconds. "mm" for month. However, I would remove the format property entirely. That should also work and is less likely to get you into trouble.

 

[keith701a]

I believe the format is:
mm/dd/yyyy hh:nn

"nn" for seconds. "mm" for month. However, I would remove the format property entirely. That should also work and is less likely to get you into trouble.

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?

 

[Mark_]

If 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?

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.

 

[keith701a]

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.

 

[Mark_]

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.

 

[The_Doc_Man]

Access saves dates & times as integers

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.

 

[Pat Hartman]

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.

It is worse than that. Let's just take two dates:
10/01/18 and 12/31/17 if you compare the actual stored values, the October date will be greater. However if you do a string compare - character by character, left-to right, 12 is > 10 so the December date would be greater even though it is from the previous year. This is why it is sooooooooooo important to not format dates when you want to sort them or compare them.

For folks in the UK, there is one situation where you MUST format a date. That is when you are creating an SQL string that includes a date value. The reason for this is because SQL ASSUMES a US date format of month, day, year. In the UK and other countries, the typical order is day, month, year so in this instance and in this instance ONLY, you must format your date to mm/dd/yyyy or yyyy/mm/dd specifically.

Select * from mytable where Mydate = Forms!myform!mydate
works fine as long as the mydate control is a datetime data type.

However if you build this query in code, you must format it because strSQL is a string and so will not contain a date object.

strSQL = "Select * from mytable where mydate = #" & format(Me.mydate, "yyyy/mm/dd") & "#;"