Is date and time evaluted in query

[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.

 

[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

 

[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.

 

[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.

 

[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

 

[ebs17]

put ... date and time ... back together again

The power of addition:

? #1/21/2022# + #17:34:15#, Date + Time
21.01.2022 17:34:15         22.01.2023 18:29:56
? Date * 1, Time * 1, Date + Time, Now, Now * 1
44948         0,772581018518519          22.01.2023 18:32:31         22.01.2023 18:32:31          44948,7725810185

I don't understand the reference to DateAdd in this context.

 

[jpl458]

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.

After reading your post the best solution is to convert the master tabel to date. time. I ran a test that looks like this:

  lastDate = DateAdd("n", "[Mastertbl3].[starttime]", "[Mastertbl3].[calldate]")

Long term, less work to convert now.