Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 (Solved) Date/time madness! (SQL, Access VBA)

Author  Topic 

opopanax666
Starting Member

8 Posts

Posted - 2014-06-04 : 05:35:50
Hello everyone,

I'm slowly losing it, so thought I asked for some input here.
I'm using a Belgian version of Office (date format 'DD/MM/YYYY').

This is the code in question (edited for clarity):

Dim dteDatum As Date

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblGEPRODUCEERD", dbOpenDynaset, dbSeeChanges)

dteDatum = Now()

rst.AddNew
rst!Weging_datum = dteDatum

rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing

DoCmd.OpenReport strRAPPORT, , , "Weging_datum = #" & dteDatum & "#"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblGEPRODUCEERD WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY HH:MM:SS") & "#", dbOpenDynaset, dbSeeChanges)

rst.MoveFirst
rst.Edit
rst!Print_x = 1

rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing


This works when working with a local table ('weging_datum' is a 'date' field), but when changing everything to a linked SQL-table, it still writes to the table ('weging_datum' becomes a 'datetime' field), but it can't seem to find any records in the second part?!

I've never understood the deeper internal workings of the date/time system (and searching the net I'm clearly not alone), made worse by this ridiculous 'locale' system of Windows.

It has to be a format problem, but I can't for the life of me figure it out.

Maybe someone here could help me?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-04 : 08:22:52
Please provide the SQL table DDL (as CREATE TABLE...), some sample data from that table (as INSERT INTO...) and the value of the string


"SELECT * FROM " & strTABEL & " WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY HH:MM:SS") & "#"


just before the call to OpenRecordset. You can do that in the debugger.
Go to Top of Page

opopanax666
Starting Member

8 Posts

Posted - 2014-06-04 : 10:12:26
Thank you to have a look.

DDL:
CREATE TABLE [dbo].[tblGEPRODUCEERD2](
[id] [int] IDENTITY(1,1) NOT NULL,
[Geprod_id] [int] NOT NULL,
[Barcode] [varchar](50) NULL,
[Electro_code] [varchar](50) NULL,
[Bestemming_id] [int] NULL,
[Weging_datum] [datetime] NULL,
[Modified_datum] [datetime] NULL,
[Product_id] [int] NULL,
[Gewicht_bruto] [real] NULL,
[Gewicht_tarra] [real] NULL,
[Gewicht_netto] [real] NULL,
[Geprod_status_id] [int] NULL,
[Scan_datum] [datetime] NULL,
[Verzending_id] [varchar](50) NULL,
[Print_x] [int] NULL,
CONSTRAINT [PK_tblGEPRODUCEERD2] PRIMARY KEY CLUSTERED
(
[Geprod_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT:
INSERT INTO [dbo].[tblGEPRODUCEERD2]
([Geprod_id]
,[Barcode]
,[Electro_code]
,[Bestemming_id]
,[Weging_datum]
,[Modified_datum]
,[Product_id]
,[Gewicht_bruto]
,[Gewicht_tarra]
,[Gewicht_netto]
,[Geprod_status_id]
,[Scan_datum]
,[Verzending_id]
,[Print_x])
VALUES
(44138, 044-138, 044-138, 1, 2013-10-14 10:42:05.000, 2014-01-31 10:42:41.000, 27, 157.4, 33.8, 123.599991, 3, 2014-03-04 12:38:10.000, BE002099, 1)


Debug.Print:
SELECT * FROM dbo_tblGEPRODUCEERD2 WHERE Weging_datum = #4/06/2014 16:10:32#
Go to Top of Page

opopanax666
Starting Member

8 Posts

Posted - 2014-06-05 : 04:23:06
Found it! From the INSERT above I noticed SQL saved the date in ISO format, so using the following:

"SELECT * FROM dbo_tblGEPRODUCEERD WHERE Weging_datum = #" & Format(dteDatum, "YYYY-MM-DD HH:MM:SS") & "#"

made it work.

Thanks everybody
Go to Top of Page
   

- Advertisement -