Query Filter (1 Viewer)

D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
I have a query from which I want to pull only those records that have been updated in the last hour.

In my file, I have a DateTime field (12/21/2016 9:18 AM). I would like the query to include this record and others if the time and date have changed within the last hour.

I have read that using DateAdd would do the trick, but I am having problem getting the format correct. Is that even the correct thing to use?

I also have two fields that I use to split DateTime onto the separate fields (Date and Time).

Suggestions?
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
What have you got so far? DateAdd() is correct, with Now() for the current date/time.
 
S

sneuberg

AWF VIP
Local time
Today, 01:57
Joined
Oct 17, 2014
Messages
3,506
Try

Code: 
DateAdd("h",1,Now)

and see if it does what you want

Edit: With Paul's correction

Code: 
DateAdd("h",-1,Now)
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
There's your fish, though it would have to be -1.
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
In the DateTime column, I have "DateAdd("h",-2,Now())" and the query results in no records found (and there are at least three records that should show.

I used -2 to make the selection timeframe a little larger. No records are displayed.
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
There should be an operator:

> DateAdd("h",-2,Now())
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
pbaldy said:
There should be an operator:

> DateAdd("h",-2,Now())
Click to expand...
u
OK...
I put a ">" in front of DateAdd and got a bunch of records and then I put "<" and got less. So you were meaning "<>".

Can I make the "DateAdd" statement a little more complex, like get current day and then filter the last hour?
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
A math operator:

=, >, <

as shown in my revision of what you had. When you didn't include one, it assumed =.
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
pbaldy said:
A math operator:

=, >, <

as shown in my revision of what you had. When you didn't include one, it assumed =.
Click to expand...

When I use <DateAdd ("h",-1,Now()), I get 39 records dates ranging from today back to 1/10. The hours start at 4 AM to 4 PM
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
I used >, not <.

pbaldy said:
There should be an operator:

> DateAdd("h",-2,Now())
Click to expand...

You want "greater than 2 hours ago". You have "less than 2 hours ago".
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
pbaldy said:
I used >, not <.



You want "greater than 2 hours ago". You have "less than 2 hours ago".
Click to expand...

I think the "<" ">", "+", "-" combinations are throwing me.

What I need to extract is records from today that we updated within the last hour.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
Did you try what I posted? Here it is again:

> DateAdd("h",-2,Now())
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
pbaldy said:
Did you try what I posted? Here it is again:

> DateAdd("h",-2,Now())
Click to expand...

yes.. but let me try again

I am getting records from a plethora of hour ranges (917PM, 2:41PM, 8:36AM, 9:30AM, etc.

Not just the last two hours. (Nevermind the dates range is all over the place)
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
Is the field date/time or text? Can you attach the db here?
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
pbaldy said:
Is the field date/time or text? Can you attach the db here?
Click to expand...

the DB is too large...

the field is Text (which is probably the issue)

As I said earlier, I have taken the "datetime" field and split it to two fields for separate Date and Time. I could filter these columns.

The format of these columns is: Time: format([dateTime],"ShortTime)
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
Text is definitely a problem; you can't do date math on a text field. Does wrapping the field in CDate() fix the problem?
 
D

dmyoungsal

Registered User.
Local time
Today, 01:57
Joined
May 1, 2016
Messages
112
Good News!

I came in early this morning and reformatted the DateTime field to NOT be a text field.

I then went back to my query and entered ">DateAdd("h",-1,Now())". I ran the query and got exactly what I needed.

Thank You!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:57
Joined
Aug 30, 2003
Messages
36,124
Glad you got it working.
 
You must log in or register to reply here.

Similar threads

P
Update query
Replies
12
Views
364
Gasman
Gasman
C
Format datetime like mm/dd/yyyy hh:mm if time value, else mm/dd/yyyy
Replies
18
Views
1,102
Minty
Minty
G
EXACT NUMBER OF HOURS BETWEEN TWO DATES AND TIME
Replies
13
Views
513
The_Doc_Man
The_Doc_Man
S
Solved Pulling records based on day of the week
Replies
5
Views
287
theDBguy
theDBguy
E
max function on text field
2
Replies
22
Views
947
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom