(Solved) Date/time madness! (SQL, Access VBA)

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.

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#

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources