Greater than also showing equal to for DATE

[dcdimon]

I am trying to use a date to determine what records to append to a table via a query. I have everything functioning, but my date expression is returning not only the dates that are greater than the MAX date in the table but records that are also equal to that date. The field I'm evaluating has a date/time format (2/6/2014 4:27:12 am) because we can have several entries per day so the time is used to make each entry unique.

The problem I'm having is that when I'm trying to only query dates after the latest date entry in the table I'm getting that date as well as the later dates.

So > #2/6/2014 4:27:12 am# yields the date 2/6/2014 4:27:12 am and all dates after. I cannot get that first date to not be included. Any ideas?

I've done a test and Acces is seeing this as a date and not a string, so I'm at a loss as to why I can't get this seemingly simply expression to work.

Thanks

DD

[orange]

Please show us the sql of the query.

Also could you clarify this

The problem I'm having is that when I'm trying to only query dates after the latest date entry in the table I'm getting that date as well as the later dates.

How can you find dates AFTER the latest date entry in the table?

[ItsMe]

Try greater than and equal to but, do it for the following day and start at midnight. (I think it is midnight)

>= #2/7/2014 12:00:00 am#

[dcdimon]

Please show us the sql of the query.

Here's the query i'm having problems with. When it runs it pulls up the record with that date and all the others after. It should be ignoring that date and then grabbing the others.

SELECT *
FROM P9CntrLnQ
WHERE (((P9CntrLnQ.StampTime)>#2/6/2014 4:27:12#));

How can you find dates AFTER the latest date entry in the table

I'm appending the query results into another table after running some calculations. The date that the query is looking at is from that append table (the latest date).

The SQL script above is from a test query I created to see why I was pulling in records that had already been appended. Everything was typed in at the query, it's not pulling the info from another source. I was hoping to narrow down where the issue was coming from, but this test query isn't working as expected so I'm thinking it's something I'm doing wrong in setting up the query. Although, to be honest, I havent a clue what it could be.

DD

[orange]

?? The query you show is only pulling data from 1
Table P9CntrLnQ.

I suggest you show us the other table and sample data - I find your post confusing.
Hopefully someone else understands it and can respond.

[dcdimon]

?? The query you show is only pulling data from 1
Table P9CntrLnQ.

I'm not sure why it matters how many tables I'm pulling data from in my query. My question is why the 'greater than' is not showing just dates after the criteria in my WHERE clause. Whether I'm getting data from 1 or 13 tables is not relevant.

I have created a query that is looking at a table and using a date that I have keyed in to only pull records after that date. However it's pulling the record with that date and dates after that date.

In my query the data is 2/6/2014 4:27:12 AM, I want to see only records after that date, but not including that date, so I'm using the greater than operator '>'.

When I run the query I'm am getting the record with that date as well as dates greater than the entered date.

My question is why?

At this point I have a very simple query looking at a data table and just a single criteria with a date/time field in it. It's set up as a date/time field in the table. When I enter the criteria into access it puts the # symbols around it so the query wizard recognizes the field as being a date/time as well.

If I break up the date field into two columns, using DATEVALUE(Stamptime) and TIMEVALUE(Stamptime) and the criteia [>2/6/2014] and [>4:27:12 AM], respectively, I get what I'm expecting; only dates after and not including 2/6/2014 4:27:12 AM. If I use the = operator I get the record with that date and when I use the < operator I get all records before that date.

I'm not sure why I can't use the whole date/time entry to evaluate the records.

[ItsMe]

Perhaps your solution is contained within post #3.

[dcdimon]

Perhaps your solution is contained within post #3.

The problem with that is I need to actually pull what the date/time field is. We run 24/7, 6 days a week and some of the data is entered hourly. So I need to be exact with what the last date is.

I'm working on creating test tables and queries to see if there's something I'm missing when I'm looking at the current tables and queries. If I figure out what the issue is I'll post the fix here.

For now I'm just going to plug away at it on my own.

DD

[orange]

I misinterpreted your

it's not pulling the info from another source.

--my bad.

24/7 is 24 hr/day *7 days/week.
You don't do 24/7 for 6 days a week.......

To find the Last date in your table use a query like this

Select Max(YourDateFieldNameGoesHere) from YourTableNameGoesHere

Try this so we can see what Access returns.
If you have your DateTime field as General Date format, this should give the Max DateTime in the table.

Also, In your query you have to delimit a date value with #
eg #2/6/2014 4:27:12 AM#

[dcdimon]

I'm not sure why I'm having this issue. It doesn't seem to be consistent throughout my database - some tables exhibit this issue and others don't.

My database is split with a SQL backend and linked tables.

When I use the MAX with my date field I get the most recent date in the list. When I enter a date into a query criteria in the wizard the # signs are automatically inserted around the date - which means that Access sees the field as being date formated.

I've spend more time than I want to trying to work this out so I made a work around. I'm adding a second to the time portion of the MAX date to not include the record already in the appended table.

Thanks for everyone's help and, while I didn't really solve this issue, I'm going to mark the thread as solved because I did get my function to work (in a round about way).

DD