VBA date problem (American <-> local)

[opopanax666]

(solved) VBA date problem (American <-> local)

Hi everyone,

I work in Belgium (date format dd/mm/yyyy).
I have a JET table with a date field (format date/time)

I use this code to get the date and write it to the table:

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & "", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***
    
    dteDatum = Now()
    
        rst.AddNew
        rst!Geprod_ID = lngHuidigNr
        rst!Weging_datum = dteDatum
        
        rst.Update
        rst.Close
        
    Set rst = Nothing
    Set dbs = Nothing

Following this the date shows as "2/06/2014 12:00:05" in the table.

A bit further in the code, I use following code to retrieve this same record:

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & " WHERE Weging_datum = #" & dteDatum & "#", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***

        rst.MoveFirst
        rst.Edit
        rst!Print_x = 1

        rst.Update
        rst.Close

    Set rst = Nothing
    Set dbs = Nothing

and yes, it returns an error 3021 "no current record" (= empty recordset = no date found)(variable "dteDatum" is not changed between the 2 bits of code).

When I manually create a query:

SELECT * FROM tblGEPRODUCEERD2 WHERE Weging_datum = #2/06/2014#;

I get an empty result (and when I look at the design it shows #6/02/2014# as criteria)

When I make it:

SELECT * FROM tblGEPRODUCEERD2 WHERE Weging_datum = #6/02/2014#;

I get my record...

Now, I get that Access has problems with the (in my view totally ridiculous) "locale"-thingy, and dates in particular, but these 2 operations are based on the same(!) variable, containing the same(!) value, and still there's a problem.

So, does anybody have some kind of solution for this? I tried using Allen Browne's SQLDate function, but that opened an other can of worms (type mismatch, error-3075, error-3421)...

TIA for any feedback!

 

[CJ_London]

Try removing the # -

 ... WHERE Weging_datum = " & dteDatum , ....

you only really need to use these if you are building the date value e.g.

... WHERE Weging_datum = #" & Format(dteDatum,"mm/dd/yyyy") & "#", ...

 

[opopanax666]

Thanks for the quick reply... but now I get an error-3075 when setting this recordset

 

[CJ_London]

have you tried the second method?

Also, just notice that later on in the code you are accessing a different table and having success with #6/02/2014#

However dteDatum is a different value - 2/06/2014 12:00:05 - so you won't get a match anyway.

suggest you assign Date() to dteDatum rather than Now() or modify your query so you are only comparing the date parts e.g.


WHERE DateValue (Weging_datum) = DateValue(" & dteDatum & ")"

 

[namliam]

This is because your original date 2/06/2014 is getting "munched" into a US date in the first place.

My guess is your dteDatum is actually a text field which gets implicitely converted into a date by this code.
At this point in your code you need to make sure you use US date formatting...

Then in the SQL again you need US formatting. Any date used between ## has to be in US formatting

U zeit gegroet, vanuit het zonnige Amsterdam

 

[opopanax666]

have you tried the second method?

Also, just notice that later on in the code you are accessing a different table and having success with #6/02/2014#

However dteDatum is a different value - 2/06/2014 12:00:05 - so you won't get a match anyway.

suggest you assign Date() to dteDatum rather than Now() or modify your query so you are only comparing the date parts e.g.
WHERE DateValue (Weging_datum) = DateValue(" & dteDatum & ")"

Yes, tried the second method, and get an error-3075;

It's not a different table, variable "strTabel" contains "tblGeproduceerd2";

The manual queries were only created to check whether it was a "locale"-problem (so US date against international date);

I absolutely need the time part, date-part is not enough...

This is because your original date 2/06/2014 is getting "munched" into a US date in the first place.

My guess is your dteDatum is actually a text field which gets implicitely converted into a date by this code.
At this point in your code you need to make sure you use US date formatting...

Then in the SQL again you need US formatting. Any date used between ## has to be in US formatting

U zeit gegroet, vanuit het zonnige Amsterdam

"dteDatum" is dimmed as a "date" (hence the "dte" part), so not a text field;

The next point in your answer brings us back to my original question (sort of): how do I make sure (in this example) I'm using the same date/time in both recordsets?


Groeten terug uit Brugge (Zweden-België: 0-2)

 

[namliam]

how is your dteDatum being filled?

Following this the date shows as "2/06/2014 12:00:05" in the table.

Is kindoff meaningless to be honest, you can have a format on your column which will distort the value.

Try doing something like:

        rst!Weging_datum = dteDatum
debug.print format(rst!Weging_datum, "DD-MMM-YYYY"), format( dteDatum, "DD-MMM-YYYY")

....
dim strSQL as string
strSQL = " SELECT * " & _ 
         " FROM " & strTABEL & _ 
         " WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY) & "#"
Debug.print strsql
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***

Rode leeuwen worden Wereld kampioen

 

[opopanax666]

"dteDatum" is filled in the code by "Now()", that's it, nothing else.

Result of debug:

02-jun-2014   02-jun-2014
 SELECT * FROM tblGEPRODUCEERD2 WHERE Weging_datum = #06/02/2014#

 

[CJ_London]

What I am struggling to understand is if your date field has a date value of 2nd June 2014 - evidenced by

When I make it:

Code:
SELECT * FROM tblGEPRODUCEERD2 WHERE Weging_datum = #6/02/2014#;
I get my record...

Why you expect to be able to find it when dteDatum has both a date and time element. so is not equal to a value which is date only

You need to show the whole value in your debug.print

suggest you try

debug.print format(rst!Weging_datum, "DD-MMM-YYYY hh:nn:ss"), format( dteDatum, "DD-MMM-YYYY hh:nn:ss")

 

[Brianwarnock]

I don't have Access so cannot go far with this, but shouldn't you be showing the full value of the data not just the date part, I would use CDbl(dteDatum) etc

Brian

Oh I see Cj has replied

 

[opopanax666]

I tried some stuff in a test db, and it seems the problem comes from the fact that I need the time part of the "Now()".
If I use the variable dteDATUM without delimiters in a sql-string, it's going to throw an error (because there's a space between date and time).
If I use string delimiters ( ' ), it throws a 3464 type mismatch, which again is normal.
And if I use date delimiters ( # ), it throws a 3021 missing record, because the date is passed as a US date.

So to be able to get this working, I should insert an US-formatted date to begin with... But that looks and feels not quite right.

What to do, what to do?

 

[opopanax666]

What I am struggling to understand is if your date field has a date value of 2nd June 2014 - evidenced by

Why you expect to be able to find it when dteDatum has both a date and time element. so is not equal to a value which is date only

This would only be a problem if the date was stored as a string, but it isn't, it's stored as a "date". If I only put a date in the criteria (so no time part), Access (and SQL) only checks the date part. Works this way in several of my other databases, nothing impossible here...

 

[CJ_London]

This would only be a problem if the date was stored as a string, but it isn't, it's stored as a "date".

And a date is....

a decimal number where the value to the left of the decimal point is the date part of the value and to the right the time part of the value expressed as a fraction of 24 hours e.g.

so 41792.6066898148 is the equivalent of 2/6/2014 14:33:38

So you are trying to compare

41792.00000=41792.6066898148

Suggets try Brians suggestion of cdbl

 

[namliam]

If you need the time part, try adding that to the format:

...
dim strSQL as string
strSQL = " SELECT * " & _ 
         " FROM " & strTABEL & _ 
         " WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY HH:MM:SS") & "#"
Debug.print strsql
...

 

[Brianwarnock]

I really do not understand what is going on, Date time is stored as a double precision number and when you compare controls that is what is compared

Brian

Edit Damn, CJ keeps beating me to the punch

 

[opopanax666]

If you need the time part, try adding that to the format:

...
dim strSQL as string
strSQL = " SELECT * " & _ 
         " FROM " & strTABEL & _ 
         " WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY HH:MM:SS") & "#"
Debug.print strsql
...

Yep, this did it, works as intended!

 

[CJ_London]

@namliam - a suggestion made some 7 posts ago!

 

[namliam]

@namliam - a suggestion made some 7 posts ago!

I know, I stole it without shame !

 

[CJ_London]

@manliam - I think to OP was only reading the emailed post and not checking his thread for other posts - and you were always getting in first!