| Author |
Date greater than another date
|
|
| GwenP 2006-02-08, 10:02 am |
| I have 2 Access tables - one network version and one local.
2 date fields both set up in Access as date/time fields and populated with
general dates showing date and time.
Using VB6 code, I want to upload everything new the from local table up to
network table. When I upload, I keep a LastUpload date in the local db.
So the query I have for uploading only those entries since the last update
is as follows:
SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND TimeEnt
> #" & LastDL & "#)"
Set tmpSQLT = LocaDB.OpenRecordset(SQL1)
TimeEnt is the date field on the database and shows say 07/02/2006 14:31:10
LastDL is previouly got and variable stored got from another table showing
the last update in the same format.
Even though quite clearly my table fields are of a later date/time than my
LastDL field it is not working.
I have tried taking out the # signs and replacing with quotes and that does
not work. I have tried declaring LastDL as a date and a string? It doesn't
work.
Any ideas most welcome please.
| |
| Jeff Johnson [MVP: VB] 2006-02-08, 10:02 am |
|
"GwenP" <GwenP@discussions.microsoft.com> wrote in message
news:E3A55071-F30C-47AA-BF02-F0C4A15E3A00@microsoft.com...
[color=darkred]
> SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND
> TimeEnt
Add
Debug.Print SQL1
Stop
and see what your SQL string looks like. If it looks fine to your eyes, post
it here and we'll look at it.
| |
| Bob Butler 2006-02-08, 10:02 am |
| "Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
news:O1W$RbBLGHA.3064@TK2MSFTNGP10.phx.gbl
> "GwenP" <GwenP@discussions.microsoft.com> wrote in message
> news:E3A55071-F30C-47AA-BF02-F0C4A15E3A00@microsoft.com...
>
>
> Add
>
> Debug.Print SQL1
> Stop
>
> and see what your SQL string looks like. If it looks fine to your
> eyes, post it here and we'll look at it.
but first change it to
SQL1 = "Select * from TimeRecord where UserID = " & UserID & _
" AND TimeEnt > #" & Format$(LastDL,"yyyy-mm-dd") & "#"
Never let VB or SQL guess at how to interpret the date format
--
Reply to the group so all can participate
VB.Net: "Fool me once..."
| |
| GwenP 2006-02-08, 10:02 am |
| Many thanks for your reply.
This is the debug.print - looks OK to me?
Select * from TimeRecord where (UserID = 762 AND TimeEnt > #08/02/06
10:51:18#)
Gwen
"Jeff Johnson [MVP: VB]" wrote:
>
> "GwenP" <GwenP@discussions.microsoft.com> wrote in message
> news:E3A55071-F30C-47AA-BF02-F0C4A15E3A00@microsoft.com...
>
>
> Add
>
> Debug.Print SQL1
> Stop
>
> and see what your SQL string looks like. If it looks fine to your eyes, post
> it here and we'll look at it.
>
>
>
| |
| GwenP 2006-02-08, 10:02 am |
| Many thanks for your reply.
I have now put the format on so my query now looks like:
SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND TimeEnt
> #" & Format$(LastUL, "dd/mm/yy hh:mm:ss") & "#)"
It still does not work? my LastUL was 07/02/2006 16:51:18 and I have just
updated all my TimeEnt records to Now() using the query designer in Access,
so definitely my time ents are greater than my LastUL date.
I'm wondering if it is still seeing one of the dates in a different format
and is thinking the TimeEnts are a date in July, even though they loook right
in the database - 08/02/2006 10:49:11 or am I missing something?
Gwen
"Bob Butler" wrote:
> "Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
> news:O1W$RbBLGHA.3064@TK2MSFTNGP10.phx.gbl
>
> but first change it to
> SQL1 = "Select * from TimeRecord where UserID = " & UserID & _
> " AND TimeEnt > #" & Format$(LastDL,"yyyy-mm-dd") & "#"
>
> Never let VB or SQL guess at how to interpret the date format
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>
>
| |
| Bob Butler 2006-02-08, 10:02 am |
| "GwenP" <GwenP@discussions.microsoft.com> wrote in message
news:4C16693B-1D1F-4871-9E1C-06315523049D@microsoft.com[color=darkred]
> Many thanks for your reply.
> I have now put the format on so my query now looks like:
>
> SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND
> TimeEnt
No... the format must be "mm/dd/yyyy hh:nn:ss" or "yyyy-mm-dd hh:nn:ss" for
this to work. Any date in x/x/x format is assumed by the jet engine to be
mm/dd/yyyy regardless of any display formats you may have specified in
Access. For clarity it's generally best to always use yyyy-mm-dd format in
queries since it's more universal.
--
Reply to the group so all can participate
VB.Net: "Fool me once..."
| |
| Jeff Johnson [MVP: VB] 2006-02-08, 10:02 am |
|
"GwenP" <GwenP@discussions.microsoft.com> wrote in message
news:4C16693B-1D1F-4871-9E1C-06315523049D@microsoft.com...
> Many thanks for your reply.
> I have now put the format on so my query now looks like:
>
> SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND
> TimeEnt
But that's not what Bob told you to do. His format string was yyyy-mm-dd and
you changed it to dd/mm/yy.
[color=darkred]
> It still does not work? my LastUL was 07/02/2006 16:51:18 and I have just
> updated all my TimeEnt records to Now() using the query designer in
> Access,
> so definitely my time ents are greater than my LastUL date.
>
> I'm wondering if it is still seeing one of the dates in a different format
> and is thinking the TimeEnts are a date in July, even though they loook
> right
> in the database - 08/02/2006 10:49:11 or am I missing something?
That's just it: it is only how the dates LOOK. Specifically, it is how you
have asked Access to MAKE them look. It is NOT how Access WANTS them to
look, and more specifically, it is not how Access (Jet) will INTERPRET a
date. It was developed in America and when presented with a date that fits
in the American syntax (mm/dd/yy) it will interpret that date as such before
trying any other format, even if that format is your regional default.
| |
| Tony Proctor 2006-02-08, 7:07 pm |
| This isn't exactly what Bob suggested Gwen. He suggested formatting your
date in yyyy-mm-dd format. This is a variation of ISO 8601 and will be
accepted by SQL whatever your local date format happens to be set to.
Tony Proctor
"GwenP" <GwenP@discussions.microsoft.com> wrote in message
news:4C16693B-1D1F-4871-9E1C-06315523049D@microsoft.com...
> Many thanks for your reply.
> I have now put the format on so my query now looks like:
>
> SQL1 = "Select * from TimeRecord where (UserID = " & UserID & " AND
TimeEnt
>
> It still does not work? my LastUL was 07/02/2006 16:51:18 and I have just
> updated all my TimeEnt records to Now() using the query designer in
Access,
> so definitely my time ents are greater than my LastUL date.
>
> I'm wondering if it is still seeing one of the dates in a different format
> and is thinking the TimeEnts are a date in July, even though they loook
right[color=darkred]
> in the database - 08/02/2006 10:49:11 or am I missing something?
>
> Gwen
>
> "Bob Butler" wrote:
>
| |
|
| Many thanks - I read it as I thought it should look ! It is working now -
great!
Gwen
"Bob Butler" wrote:
> "GwenP" <GwenP@discussions.microsoft.com> wrote in message
> news:4C16693B-1D1F-4871-9E1C-06315523049D@microsoft.com
>
> No... the format must be "mm/dd/yyyy hh:nn:ss" or "yyyy-mm-dd hh:nn:ss" for
> this to work. Any date in x/x/x format is assumed by the jet engine to be
> mm/dd/yyyy regardless of any display formats you may have specified in
> Access. For clarity it's generally best to always use yyyy-mm-dd format in
> queries since it's more universal.
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>
>
|
|
|
|