Is date and time evaluted in query (2 Viewers)

[Pat Hartman]

Dmax is easier to use. I am working with a miniscule amount of data, so performance is not really an issue.

Once you know what the best, most efficient option is, use it. Train yourself to be the best that you can be rather than mediocre. That way, when it matters, you won't have to look back and find a conversation like this to remind you of better options. Your apps won't be filled with mediocre solutions that usually work, they'll be filled with solutions worth copying.

Sometimes the domain function is the best option but almost never in a query or in a VBA loop. As a stand-alone, one-off get a piece of data, domain functions are the best, simplest solution. The solution in #8 seems to be the best option based on your original question. The domain function is simpler than opening a query. Since both the original code and the domain function each run a single query with no where clause, each will generate the same execution plan. If the datetime field is indexed, Access will use an index to obtain the value. Otherwise, it will read RBAR (row by agonizing row) through every single row in the table because it can't determine the max value until it has read all values.

 

[jpl458]

True but I'm not sure I'd worry about that for very long


They are part of the same field so yes. It's like asking if the cents part of a dollar amount is considered when checking an amount field. If you are having trouble, it is because you are storing the data as a string rather than as a date/time data type.

The difference between the SQL MAX aggregate function and the DMAX function is that DMAX builds a SELECT MAX(field) FROM table WHERE criteria and then executes it and returns the only thing it retrieved. So a DMAX uses MAX to get what it needs to answer your query. Which is why we do not suggest using domain aggregates in an SQL statement. Oh, they work - but they work way too hard.

I've found that DMax only returns a time 1200 AM, even though the field used contains both date and actual time.
I have a table and one of the fields in that table is calldate. It is defined as Date/Time in the table.
I am adding data to the table but only if the date of the incoming date is greater than the latest date in the call date field

In the code is the following:

 Dim lastDate As Date
 lastDate = DMax("calldate", "Mastertbl3")
 If fl.DateCreated > lastDate Then
   Some code

However, when I debug print the values involved I get:

LastDate 1/19/2023
DateCtreated 1/19/2023 12:35:26 PM
TimeValue 12:00:00 AM (of LastDate)

The problem is that if this is run more than once per day the same record keeps being appended to the Master Table, because LastDate contains
1/19/2023 12:00:00 AM and the last date interrogated record is 1/19/2023 12:35:26 PM.

Is there a more accurate way of setting last date that really contains the LastDate on the master

Hope this is not too confusing;

Thanks

 

[Pat Hartman]

Domain functions don't change the data type. I have no problem printing out dates with time using dMax()
Are you sure you are using Now() rather than Date() to save the date?

 

[jpl458]

Are you sure you are using Now() rather than Date() to save the data? Also, have you formatted the field so that it is not showing you the time even if it is there?

Not using either. The interrogated data is from the properties of a file, and it clearly shows date and time. The problem is in the DMax function, which only returns a date with a time of 1200 AM. So if I run the code more than once per day, if a record was posted with a time greater than 1200 AM for the same day, it gets posted twice. I have done a load of tests and DMax only returns a time of 1200 AM for any date/time field. This is true, of course, unless I've been using the really good drugs. Took a while to figure out why this was happening.

 

[Pat Hartman]

If you are using dMax(), you are getting the date from a table, NOT from the file system. What is the data type of the field in the table? As I said, I have no problem returning the correct value.

 

[The_Doc_Man]

I'm with Pat on this one. DMax will always return the correct data type for the field named as the first argument. HOWEVER - are you sure you are not returning the date to a LONG data type? I.e. what is on the LEFT side of the DMax? If it is a LONG, it will handle the day part of a date/time variable easily, since the current date runs in the 44K range but storing to an integer data type as an intermediate will truncate away the time portion because that requires fractions.

What is the data type of "CallDate" ?? And what is actually stored in that field? I would check the table itself in datasheet view, sorted on the CallDate field, to find the maximum value in that view. Because there is no way in HELL that DMax would truncate a date without there being an INTEGER or LONG somewhere in the picture. I know this because if DMax truncated numbers with fractions, there would have been a mob of angry junior programmers with torches and pitchforks heading to Redmond, Oregon sometime soon after Access v 1.0 was released. So the problem MUST be either in the data itself or in a hidden intermediate variable when you are storing the data.

 

[jpl458]

If you are using dMax(), you are getting the date from a table, NOT from the file system. What is the data type of the field in the table? As I said, I have no problem returning the correct value.

Let me try and explain again.

The DMax I am using is :

Dim lastDate As Date
lastDate = DMax("calldate", Mastertbl3") 'This is the max date in the master file

If I do a Debug.print on that field I get;

LastDate 1/19/2023

The if I do a TimeValue on Lastdate I get:

TimeValue 12:00:00 AM. This happens no matter what record/ it looks at from the incoming data.

On the master file the calldate field is as such:

However, the datecreated field the Dmax date is compared to is

Which is greater than 12:00:00 AM by 12 hours and 26 minutes
Note that everytime I run this code I calculate a new LastDate, so if the compare is correct I should only get recorde that have a dte/time field greater than LastDate.

 

[jpl458]

I'm with Pat on this one. DMax will always return the correct data type for the field named as the first argument. HOWEVER - are you sure you are not returning the date to a LONG data type? I.e. what is on the LEFT side of the DMax? If it is a LONG, it will handle the day part of a date/time variable easily, since the current date runs in the 44K range but storing to an integer data type as an intermediate will truncate away the time portion because that requires fractions.

What is the data type of "CallDate" ?? And what is actually stored in that field? I would check the table itself in datasheet view, sorted on the CallDate field, to find the maximum value in that view. Because there is no way in HELL that DMax would truncate a date without there being an INTEGER or LONG somewhere in the picture. I know this because if DMax truncated numbers with fractions, there would have been a mob of angry junior programmers with torches and pitchforks heading to Redmond, Oregon sometime soon after Access v 1.0 was released. So the problem MUST be either in the data itself or in a hidden intermediate variable when you are storing the data.

Just sent some more info to Pat. What you say makes sense, what is happening does not. DMax is not truncating the data, It's always pitting in 12:00 AM for the time part, even though the inp[ut field has a time greater then 12:00 AM. Put everything you mentioned in the reply to Pat.

Thanks

 

[Pat Hartman]

And I say it again. There is NO problem with using dMax() on a field containing both date and time. Access suppresses the time when it is midnight. If all you are seeing is the date, then the time is midnight and that is suppressed. Internally dates are stored as double precision numbers. The integer portion is the number of days since Dec 30, 1899. The decimal portion is the elapsed time since midnight.

So, midnight on 12/30/1899 = 0.0 - displays as 12/30/1899 --- blank for time
6 am on that date = 0.25 - displays as 12/30/1899 06:00:00 AM
noon on that date = 0.5 - displays as 12/30/1899 12:00:00 PM

 

[Pat Hartman]

You keep posting clips of display data. You have NOT answered my question about what is in the table. Upload a table with various dates in it that show the problem. You are talking about what you see in file manager but that is NOT what the dMax() is acting on.

 

[ebs17]

As already shown, Date (DateTime) is converted internally as a double, i.e. it is a number. With a simple multiplication you can look at this number.

? #1/19/2023 12:35:26 PM# * 1
 44945,5246064815

You can compare numbers. And you can be sure that a MAX or DMax gets the most value from the data provided. I'll cast my vote here if it's voted on here.

 

[jpl458]

You keep posting clips of display data. You have NOT answered my question about what is in the table. Upload a table with various dates in it that show the problem. You are talking about what you see in file manager but that is NOT what the dMax() is acting on.

Never used the file manager with his problem. I will create a couple of tables and forward to you. Not all the data in the table, only the parts involved, if that's OK. Have dinner to contend with first.

 

[jpl458]

You keep posting clips of display data. You have NOT answered my question about what is in the table. Upload a table with various dates in it that show the problem. You are talking about what you see in file manager but that is NOT what the dMax() is acting on.

I think I've found the problem. When I append data into the Maser Table use the DateValue and TimeValue of the incoming date to create 2 separate fields. Then, when I use DMax() I am using the date field from the Master that had the time stripped off when appended to the Master. Now, how do i concatenate thse date and time fields back together to create a proper lastDate variable. Midnight reading.

 

[jpl458]

I'm with Pat on this one. DMax will always return the correct data type for the field named as the first argument. HOWEVER - are you sure you are not returning the date to a LONG data type? I.e. what is on the LEFT side of the DMax? If it is a LONG, it will handle the day part of a date/time variable easily, since the current date runs in the 44K range but storing to an integer data type as an intermediate will truncate away the time portion because that requires fractions.

What is the data type of "CallDate" ?? And what is actually stored in that field? I would check the table itself in datasheet view, sorted on the CallDate field, to find the maximum value in that view. Because there is no way in HELL that DMax would truncate a date without there being an INTEGER or LONG somewhere in the picture. I know this because if DMax truncated numbers with fractions, there would have been a mob of angry junior programmers with torches and pitchforks heading to Redmond, Oregon sometime soon after Access v 1.0 was released. So the problem MUST be either in the data itself or in a hidden intermediate variable when you are storing the data.

I just posted a note to Pat, and I think I found the problem. You gave me the thought clue when you said that if there is no time then Dmax returns 12:00 AM. And that is exactly what it does. When I append the data to the master I split the incoming time field into a date field and a time field using DateValue and TimeValue, then I erroneously use the truncated date field in the Mast to create the lastDate field, which alway has 12:00 AM as the time part.

Time for Football.

Thanks

 

[The_Doc_Man]

Glad you could track it down. As Elmer Fudd once said, the hardest part of making Hassenpfeffer is to catch the rabbit - and you just did that. If what I (or any other participant) said helped you to reorient your thinking, then great! That's why we are here.

 

[Pat Hartman]

It is better to store the date as a single field. If you leave it split, use DateAdd() to put the two back together. But really, it is better to use ONE field

 

[jpl458]

It is better to store the date as a single field. If you leave it split, use DateAdd() to put the two back together. But really, it is better to use ONE field

Is that because it saves space, or that you can do anything you want with that one date and is simplifies things: or both.

 

[Pat Hartman]

It simplifies processing. If the date includes time, it is because time is significant. Therefore, you compare and sort on a single field rather than having to deal with two. It also saves space but that is secondary.

 

[jpl458]

It is better to store the date as a single field. If you leave it split, use DateAdd() to put the two back together. But really, it is better to use ONE field

I ran a query and DateAdd works well with the table that's in the query. But, I need to use the DateAdd in vba and the table is external. My question is very basic, and I have looked up and tried several variations, but can't get it to work. Here is the expression:

Dim lastDate as Date
lastDate = DateAdd("n", [Mastertbl3].[starttime], [Mastertbl3].[calldate])
For the time being, I just want to put the 2 times (date and time) back together again. My problem is with the syntax. I can't figure out how to address fields in another table in the database from VBA.

Thanks for your help

 

[Pat Hartman]

If you want to use a table in VBA, you need to open a recordset using DAO. But I sense confusion in your question. Are you asking how to permanently join the two? To do that, you would use an update query. First add the new datetime field to the table. Then create a querydef that updates the new field with the DateAdd() calculation. Once the two fields are joined, delete the original two fields and change your forms/reports to work with the single field.

Otherwise, tell us what you want to do with the recordset and we can offer some advice.