Calendar Control 12.0 Date Changing

[LB79]

Hi all,

Very strange going on with calendar control 12.0.
I'm using it to append deadlines to a table.
When the date is selected and cmd button pressed, it write a row of text including Me.Cal1 (my controls name).

The code below is my SQL which I run through VBA.

I have added a line of VBA to confirm the correct date – MsgBox Me.Cal1

When I run the code, the message box shows the correct date that has been selected – Say 8th March 2013 BUT when the query write to the table the date it inserts is 30th December 1899.

Has anyone come across this before?

INSERT INTO tbl_REF_Schedule ( REF, Type, PIC, UN, [Month], Date1 )
SELECT Format(Now(),'yyyymm') & '-' & Format(Now(),'dd') & [Type] & ' ' & [PIC] AS REF, tbl_REF_ReportPIC.Type, tbl_REF_ReportPIC.PIC, tbl_REF_ReportPIC.UN, Format(Now(),'yyyymm') AS Mth, " & Me.Cal1 & " AS DueDate
FROM tbl_REF_ReportPIC
WHERE (((tbl_REF_ReportPIC.Type)='1234'))


Many thanks

 

[mdlueck]

BUT when the query write to the table the date it inserts is 30th December 1899.

Has anyone come across this before?

NULL dates show up as some date back in the 1800's.

Perhaps read the value of the control into a VBA variable, then pass that variable to the control. That way you can inspect the VBA variable to make sure you have captured it correctly.

As-is, reading a control embedded into a SQL statement is a bit too tangled / intertwined to be sure where the date is getting lost.

Suggested way to read form controls safely:
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

 

[mdlueck]

I should add... in my code I read date controls into a string type variable, thus vbNullString is the default value. The date controls do not seem to mind receiving a date via a string variable.

String variables work well with building a SQL string.

 

[LB79]

Hi and thanks for the reply.
Ive tried reading the calendar control into a variable and showing that variable in a msgbox (to confirm that variable isnt null), but still the date shows in the table as December 1899.

 

[LB79]

To add to this, I seems between the MsgBox showing the correct date and the date writing to the table, the content of the string becomes 00:03:35 (a time based in 1899?). This was realised from a make table query (below). But I still dont understand how its losing the date between row 2 of the below code and the SQL.

Gen1 = Me.ATMT_cal1 'Calendar Control 12.0
MsgBox Gen1
ATMTSQL = "SELECT Format(Now(),'yyyymm') & '-' & Format(Now(),'dd') & [Type] & ' ' & [PIC] AS REF, tbl_REF_ReportPIC.Type, tbl_REF_ReportPIC.PIC, tbl_REF_ReportPIC.UN, Format(Now(),'yyyymm') AS Mth, " & Gen1 & " AS Due"
ATMTSQL = ATMTSQL & " INTO test"
ATMTSQL = ATMTSQL & " FROM tbl_REF_ReportPIC"
DoCmd.RunSQL ATMTSQL

 

[mdlueck]

All right, must be some problem with the control itself.

Where exactly did this control come from? I do not recognize it by the name in your thread title line.

The DatePicker OCX control I know of is at version "Microsoft Windows Common Controls 6.0 (SP6)"

 

[mdlueck]

Oh... http://excelusergroup.org/forums/t/1909.aspx#5468

And I would advise to use the built-in Date Picker (At least in A2007) over the DatePicker OCX.

 

[LB79]

Ah so poss that the control is buggy - So im not going mad...

Thanks for your assistance

 

[spikepl]

The control may or may not be buggy, but your method of referring to it definitely is.

You can do either

ATMTSQL = "SELECT Format(Now(),'yyyymm') & '-' &  Format(Now(),'dd') & [Type] & ' ' & [PIC] AS REF,  tbl_REF_ReportPIC.Type, tbl_REF_ReportPIC.PIC, tbl_REF_ReportPIC.UN,  Format(Now(),'yyyymm') AS Mth,  Forms!NAmeOFThisForm!ATMT_cal1 AS Due"
ATMTSQL = ATMTSQL & " INTO test"
ATMTSQL = ATMTSQL & " FROM tbl_REF_ReportPIC"
DoCmd.RunSQL ATMTSQL

or

ATMTSQL = "SELECT Format(Now(),'yyyymm') & '-' &  Format(Now(),'dd') & [Type] & ' ' & [PIC] AS REF,  tbl_REF_ReportPIC.Type, tbl_REF_ReportPIC.PIC, tbl_REF_ReportPIC.UN,  Format(Now(),'yyyymm') AS Mth, #" & Gen1 & "# AS Due"
ATMTSQL = ATMTSQL & " INTO test"
ATMTSQL = ATMTSQL & " FROM tbl_REF_ReportPIC"
DoCmd.RunSQL ATMTSQL

 

[spikepl]

...and 03:35 would fit...If you pass, say 8/3/2013 then 8 divided by 3 divided by 2013 is a fraction of a day

 

[LB79]

SpikePL that was perfect! completely didnt think to add ##!
Thanks