Combine Date/Time fields

[TIonLI]

I am working with a table that lists dates and corresponding times in separate fields. For purposes of building a query to calculate elapsed time I would like to combine a [Short Date format] field with a [Long Time format] field ending up with a new field [General Date format]... Is it Possible?

[Rabbit]

You'll want to take a look at the CDate function.

[missinglinq]

I'm not sure you need to use CDate() Rabbit. How Access handles these date things is so quirky, but simply doing

YourNewField = YourShortDate & " " & YourLongTime

seems towork just fine. You can then use the results in Date oriented functions, such as DateAdd and DateDiff, without problems.

Welcome to TheScripts, New Poster!

Linq ;0)>

[TIonLI]

Thanx Rabbit and Linq. It worked great. dateDiff was the icing on the cake!

[TIonLI]

I'm not sure you need to use CDate() Rabbit. How Access handles these date things is so quirky, but simply doing

YourNewField = YourShortDate & " " & YourLongTime

seems towork just fine. You can then use the results in Date oriented functions, such as DateAdd and DateDiff, without problems.

Welcome to TheScripts, New Poster!

Linq ;0)>

Linq,

I'm having a problem trying to limit the results of the DateDiff expression. I would like to return only positive values with a cap at 12. I tried:
Between 0.1 And 12.1
and
>0.1 And <12.1
In each case I get a pop-up looking for a Parameter Value for Expr2 (which is the code that adds a date with the corresponding time per your earlier response).

[missinglinq]

Show us your code so far.

Linq ;0)>

[TIonLI]

Show us your code so far.

Linq ;0)>

To combine date/time:
Expr2: [ARREST-DATE] & " " & [ARREST-TIME]
and
Expr3: [ENTRY-DATE] & " " & [ENTRY-TIME]
To calculate elapsed time:
Expr4: (DateDiff("n",[Expr2],[Expr3]))/60

The DB currently has about 185000 records. I want to limit returns for Expr4 to only those that are 12 hours or less, AND I don't want to return negative numbers (There are a few bad data entries so I tried to prevent them from being used by using <"Expr3" as a criteria for Exp2, but it only works if the dates don't fit the criteria. On records where the date is the same and the hours are in question, <"Expr3" doesn't seem to make a difference.)

Thanx for all the help.

[missinglinq]

OK, where's the code you're using to try to limit the returned records to >.1 and < 12.1? And where are you putting this? In a query, or where? I'll probably be gone the rest of the evening, but if I'm not here someone else will come along.

[TIonLI]

OK, where's the code you're using to try to limit the returned records to >.1 and < 12.1? And where are you putting this? In a query, or where? I'll probably be gone the rest of the evening, but if I'm not here someone else will come along.

The Code I tried was:
Between 0.1 And 12.1
and
>0.1 And <12.1
Both as a criteria for Expr4 in a query.

[missinglinq]

OK, where's the code you're using to try to limit the returned records to >.1 and < 12.1? And where are you putting this? In a query, or where?

You said that before; we need to see the actual code, not just a snippet! And where are you placing this code?

Linq ;0)>

[MGrowneyARSI]

You may want to try building a new query with just the criteria for .1< And >12.1 keeping the date and time separate, and then building a new query off of that one making your concatenated field in the second query this way access does not get confused with the criteria referring to date or time. Sounds strange but I had to do similar.

[TIonLI]

You may want to try building a new query with just the criteria for .1< And >12.1 keeping the date and time separate, and then building a new query off of that one making your concatenated field in the second query this way access does not get confused with the criteria referring to date or time. Sounds strange but I had to do similar.

I'm scratching my head, but it worked... Thanx

[TIonLI]

You said that before; we need to see the actual code, not just a snippet! And where are you placing this code?

Linq ;0)>

Linq,

Just to help me provide the proper info when asking a question in the future, I'm confused as to what code you were asking me to provide other than what I gave you. "Between 0.1 And 12.1" was the only code I entered in the criteria field of the query. In the end it ended up being correct but only after I built a new query that pulled in the results of Expr4 from the original query. Is there something else I should have provided?

Also, Thanx again to you and all others for your assistance... and patience.

[MGrowneyARSI]

I have a mail merge where I had to do the same thing because I could not have any criteria in the query that it was using. The sad part is that was just the iceing on the cake glad it worked for you good luck.

[missinglinq]

Glad it worked for you, TIonLI!

Between 0.1 And 12.1" was the only code I entered in the criteria field of the query

That's what was missing! You didn't say "I entered in the criteria field of the query" in your posts! Didn't know whether you were doing this in code, in a SQL staatement or, as was the case here, in the Criteria Field in the Query Grid! The single biggest thing to remember, as NeoPa, the Forum Leader here, reminds everyone, is that the memebers here can't see what you see when you look at your database! We only know what you tell us.

It's great that you care enough to ask! I look forward to working with you again!

Linq ;0)>