Create date/time field from separate date and time entries

[dizzydangler]

Just a quick question...I'm running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm) format. I've been running queries by date alone, which has been working, but there are times when I have to run queries based on times as well. (e.g. return all apointments that occurred after 1:00 PM on 12/15/2009 and before 1:00 PM on 12/22/2009). I need the query to work whether time is input as a search parameter or not, and I've been playing around with IIF statements involving the possible combinations of start/end dates and times, but it's getting WAY more complicated than I think it needs to be. Plus, I haven't gotten it to work so far.

It occurs to me that the easiest thing to do would be to convert the values in both the date and time fields into a single date/time field, then base the IIF function on this new value. There's got to be a function that lets you do this, I just haven't figured out what it is.

Any help?

JS

[ChipR]

This seems to work:

CDate("January 2, 2009 13:00")

[dizzydangler]

Thanks, ChipR, but that expression returns a date from the text string--what I want to do is combine the date field and the time field into a date/time field that I can then use in an IIF statement to set the start date/time and end date/time in my query parameters. I tried to use both '[Date]' and '[Time]' as text in the CDate function, like this:

CDate("[Date]","[Time]")

Also tried:

CDate(#[Date]#,#[Time]#)

maybe there's a syntax problem, but it appears that it will accept only one argument.

JS

[Stewart Ross]

Hi. If the date and time values are stored in date fields then the simple answer is to add the two together to form a single date/time value. The date part of a date/time value is the integer component of the value stored, and the time part the decimal component. Addition of these should work without any problem, resulting in a value of type date/time. A dummy IIF that does this kind of comparison is shown below :

IIF([some date]+[some time] > [another date] + [another time], "First was greater", "Not greater")

-Stewart

[dizzydangler]

Stewart, that's brilliant--I had no idea you could just add date + time values to form date/time fields. I think I'm on the right track now. Still haven't gotten the code to work, but I haven't played with it too much yet. What happens if a null value gets added in the [some time] field? Can the resulting field (which I would think would still be just a date field) still be compared with a date/time field? Or does it create a date/time field with 00:00:00 for the time?

[ChipR]

CDate takes one string as its argument, so the syntax would be

CDate([DateField] & " " & [TimeField])

[Stewart Ross]

Nulls cannot be propagated in arithmetic operations - the result of adding the date and time values will itself be null if the time field is null. You would need to substitute an appropriate value, using a combination of the Nz and CDate functions to return a properly-typed date/time value:

IIF([some date] + CDate(Nz([some time], "00:00")) > ...)

I am assuming that your date field cannot be null. If it can, you would need to substitute for it in similar fashion, although what the date you substitute might be I cannot guess. Substituting the current date would give:

IIF(CDate(Nz([some date], Date())) + CDate(Nz([some time], "00:00")) > ...)

-Stewart

[NeoPa]

Absolutely Stewart.

In SQL though it could be as simple as :

SELECT ...,
       Nz([DateField],Date())+Nz([TimeField],0) AS [DateTimeField],
       ...
 
FROM   [YourTable]

PS. As Stewart mentioned JS, dates and times are stored as Date/Time values. How they are formatted (anywhere, form; query; even table) is entirely irrelevant and you should not allow yourself to be confused by that factor.

[dizzydangler]

OK, I think I've gotten over the date/time hurdle. I replaced any null values for time in the table of saved records with "0:00," and made the input form require a time input, thus eliminating the possiblity of a null time field in any record in the database. I've also been able to write expressions to query by date/time if both Date and Time controls are filled in on the query form.
I mentioned previously that my query form works fine running a search by date alone, given a start date and end date, just a start date, just an end date, or no dates (returns all records). What I haven't been able to do is write an expression that allows it to do either/or--that is, if dates controls are filled in, execute the query using Date-only parameters, but if date and time controls are filled in, execute the query using Date/Time parameters. I want to give the user the ability to query by date/time if values are entered in the time controls, but query by Date only, if the time controls are left blank (null value).

I'm basically trying to use strings of IIF statements to say, in plain english:

IF (STARTTIME AND ENDTIME ARE BOTH NULL, EXECUTE QUERY BY START DATE AND END DATE, IF(STARTTIME AND ENDTIME ARE BOTH NOT NULL, EXECUTE QUERY BY START DATE/TIME AND END DATE/TIME, IF(STARTTIME IS NOT NULL AND ENDTIME IS NULL, EXECUTE QUERY BY START DATE/TIME AND END DATE, OTHERWISE EXECUTE QUERY BY START DATE AND END DATE/TIME)))

Hope this makes sense to someone. I'll post the code I have so far when I have more time, but if anyone has any ideas now, I'm all ears.

Thanks!

JS

[NeoPa]

Assuming your form (with the StartDate, EndDate, StartTime & EndTime controls are on) is called frmSelect :

SELECT ...
 
FROM   ...
 
WHERE  [DateTimeField] Between (Forms!frmSelect.StartDate +
                                Nz(Forms!frmSelect.StartTime,0))
                       And     (Forms!frmSelect.EndDate +
                                Nz(Forms!frmSelect.EndTime,0))

[dizzydangler]

Thanks, NeoPa. I think I'm on the right track, now. I need to be able to do more than just search between two dates--I need to query for all results before a certain date, all results after a certain date, and allow variations for date-only searches vs. Date/Time searches. I've been experimenting with ways to do this using the Select Case function and Cases consisting of different "Null" or "Not Null" combinations of the four variables (form controls): SearchStartDate, SearchStartTime, SearchEndDate, and SearchEndTime.

Haven't been able to get it working using Select Case, but I have been able to get the query to do what I want using a series of nested IIF statements. So I'm good...or so I thought!

What I've discovered is that there are some inconsistencies in the data that are throwing off the query results, in certain cases. All the Date and Time data that's been entered into the database since November has been in the Short Date (MM/DD/YYYY) and Short Time (HH:MM) formats--my expression works fine with these records. However, before that, (about 9000 records going back to 2004) most of the data (in both the Date AND the Time Fields!) is in General Date format (MM/DD/YYYY HH:MM:SS), even though it only appears in the table view as Short Date or Short Time. This makes for some pretty wacky results when I use expressions like this

([Table1].[Date]+[Table1].[Time]>=[Forms]![frmSelect]![StartDate]+[Forms]![frmSelect]![StartTime])

in my code.

For instance, in a record that lists Date as 10/21/2004 and Time as 21:00, I get a result for [Date]+[Time] as 8/14/2109 6:00:00 AM! This is because the expression is adding 10/21/2004 9:00 PM + 10/21/2004 9:00 PM, the "true" values which are in the Date and Time fields in this particular record. I can write an even more cumbersome expression that compares date fields and time fields individually--for instance, this expression for querying results that occur after an given date and time--same basic concept as the previous expression, only this one also works with records that have "general date" data in the Date and Time fields:

([Table1].[Date]>[Forms]![frmSelect]![StartDate] OR ([Table1].[Date]=[Forms]![frmSelect]![StartDate] AND [Table1].[Time]>[Forms]![frmSelect]![StartTime])

But I would rather find a way to "truncate" the values in the Date/Time fields, leaving them as JUST DATE in the Date field and JUST TIME in the Time field.

Is there any way to do this? Thanks,

Joe

[NeoPa]

@NeoPa
I did warn you about getting confused by the format of dates and times. Dates and Times are stored numerically and what is visible when displayed is not necessarily a true reflection of the situation.

That said, there are functions called DateValue() & TimeValue() which can be used to convert a Date & Time value to either one or the other, depending on your requirement. Putting these functions as wrappings around any reference you have to the unreliable data from your table should resolve your current issue.

NB. (This is worth noting so I suggest you read carefully) This business of the data not conforming to the understanding you had, should impress upon you the critical importance of determining exactly what you're working with at the design stage. It is always far less hassle, and less painful, if these details are properly understood before approaching the job itself. To be fair, even many hardened professionals skip past this too often, but they generally pay for it when they do.

Anyway, I'm glad you seem to be making good process and good luck with your project.

[dizzydangler]

SORTED! Discovered the Format function, which I used in an update query to truncate all the date and time fields and give me exactly what I want in each field. For those that might be looking for a similar solution in this thread, I put the following expressions in the "Update To:" row of the Update Query

For the Date field:

Format([Date], "mm/dd/yyyy")

For the Time field:

Format([Time], "hh:nn")

Now the nested IIF functions work like a charm, give users the ability to get exactly the results they want every time they run the search query.

Thanks to all--problem solved. I'm going to mark Stewart's original post as the best answer, because that was the breakthrough that got me started on the right track.

[NeoPa]

Personally I'd avoid the use of Format in this context as it encourages the thinking that the format matters, but that's just me. If you find that easier to understand and work with that's all that matters really.

I also appreciate your choosing Stewart's post (Good call IMHO). It's particularly nice that you chose to explain why in case anyone may have felt overlooked. I think you're the first member I've noticed doing that.

Lastly, it seems while we've been happily working on this, your status changed to full Member. Congratulations & I hope to see more of you and your questions.