Date Format In Table Is Not Working

Hi,

I have a field in a table in an access 2007 database. The field is
set to data type date/time and format medium date. I have a form that
has a textbox set to date/time. The user can select a date from here
with a date picker. When they hit the submit button on the form, the
following query runs and inserts the data into my table.

QueryToSubmitData = "INSERT INTO Transactions (GiverId, DT,
ReceiverId, Amount) SELECT " & Me.NameCombo.Column(0) & _
", " & Me.Date.Value & ", " &
Me.ReceiverName.Value & ", " & Me.Pips.Value & " FROM Employee_List
WHERE " & _
"Employee_List.ID = 1;"

The problem is that the Me.Date.Value that is being inserted is only
inserting the time value into the table, despite the fact that the
user may have the following date selected '04/01/2009'. Any ideas why
this is the case? I have been playing around with it, but haven't
been able to figure out what the problem is.

R Tanner wrote:
Hi,

I have a field in a table in an access 2007 database. The field is
set to data type date/time and format medium date. I have a form that
has a textbox set to date/time. The user can select a date from here
with a date picker. When they hit the submit button on the form, the
following query runs and inserts the data into my table.

QueryToSubmitData = "INSERT INTO Transactions (GiverId, DT,
ReceiverId, Amount) SELECT " & Me.NameCombo.Column(0) & _
", " & Me.Date.Value & ", " &
Me.ReceiverName.Value & ", " & Me.Pips.Value & " FROM Employee_List
WHERE " & _
"Employee_List.ID = 1;"

The problem is that the Me.Date.Value that is being inserted is only
inserting the time value into the table, despite the fact that the
user may have the following date selected '04/01/2009'. Any ideas why
this is the case? I have been playing around with it, but haven't
been able to figure out what the problem is.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you put a Breakpoint on the VBA code to see if the variable
QueryToSubmitData has the correct date data? You may also want to
delimit the date value with # delimiters. Ex:

... ", #" & Me.Date.Value & "#, " ...

BTW, "Date" is a VBA built-in function [Date()]. It would be better to
re-name the control to a different name, like "txtDate." Also, you
don't need to include the .Value property when referencing a Control,
because .Value is the default property - the property that is
automatically read when the Control is referenced. Also, MS recommends
that the bang ! be used when referencing Controls, rather than the dot.

Me!txtDate


HTH,
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfo+jIechKqOuFEgEQLRZwCdFsIv+bcnH20PMgNcL32Qsd ElJVcAoOGs
tWWjih5SzeGAIS6PFTRy2+Bf
=lo7V
-----END PGP SIGNATURE-----

On Apr 30, 6:13*pm, MGFoster wrote:
R Tanner wrote:
Hi,

I have a field in a table in an access 2007 database. *The field is
set to data type date/time and format medium date. *I have a form that
has a textbox set to date/time. *The user can select a date from here
with a date picker. *When they hit the submit button on the form, the
following query runs and inserts the data into my table.

QueryToSubmitData = "INSERT INTO Transactions (GiverId, DT,
ReceiverId, Amount) SELECT " & Me.NameCombo.Column(0) & _
* * * * * * * * * * ", " & Me.Date.Value & ", " &
Me.ReceiverName.Value & ", " & Me.Pips.Value & " FROM Employee_List
WHERE " & _
* * * * * * * * * * "Employee_List.ID = 1;"

The problem is that the Me.Date.Value that is being inserted is only
inserting the time value into the table, despite the fact that the
user may have the following date selected '04/01/2009'. *Any ideas why
this is the case? *I have been playing around with it, but haven't
been able to figure out what the problem is.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you put a Breakpoint on the VBA code to see if the variable
QueryToSubmitData has the correct date data? *You may also want to
delimit the date value with # delimiters. *Ex:

* *... ", #" & Me.Date.Value & "#, " ...

BTW, "Date" is a VBA built-in function [Date()]. *It would be better to
re-name the control to a different name, like "txtDate." *Also, you
don't need to include the .Value property when referencing a Control,
because .Value is the default property - the property that is
automatically read when the Control is referenced. *Also, MS recommends
that the bang ! be used when referencing Controls, rather than the dot.

* *Me!txtDate

HTH,
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. *I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfo+jIechKqOuFEgEQLRZwCdFsIv+bcnH20PMgNcL32Qsd ElJVcAoOGs
tWWjih5SzeGAIS6PFTRy2+Bf
=lo7V
-----END PGP SIGNATURE-----

Great. Thank you for the info. It was the # signs that I needed.

I will use the ! from now on instead of the dot.

Thanks again for your help.

The use of the ! operator in fact only applies to when a control is
referenced as a parameter by a query, e.g. Forms!MyForm!MyControl. In
your case you are referring to a control on the current form to build
a string expression as the SQL statement, so you should use the dot
operator, e.g. Me.MyControl.

The reason why you were getting what appeared to be 'time values' is
due to the way in which the date/time data type is implemented in
Access, as a 64 bit floating point number as an offset from 30
December 1899 00:00:00 with the integer part representing the days and
the fractional part the times of day. By omitting the # date
delimiter characters the value in the control was being treated as an
arithmetical expression. The expression 04/01/2009 evaluates to
0.00199104031856645, which is the date/time value 30 December 1899
00:02:52. You can see this by entering the following in the debug
window:

? Format(04/01/2009, "dd mmmm yyyy hh:nn:ss")

It was actually this date/time value which was inserted, there in fact
being no such thing in Access as a time value or date value per se,
only date/time values.

Another point to be aware of is that even if you enclose the date with
the # date delimiter characters this is internationally ambiguous. It
will work with systems set to US short date format, but if I were to
execute your query here, using UK short date format, the value would
be #01/04/2009# i.e. 4 January, not 1 April. To 'internationalize' an
application its good practice to use an unambiguous format such as the
ISO standard date notation of YYYY-MM-DD, so the expression would be
built like so:

QueryToSubmitData = _
"INSERT INTO Transactions (GiverId, DTReceiverId, Amount) " & _
"SELECT " & Me.NameCombo & _
", #" & Format(Me.[Date],"yyyy-mm-dd") & "#, " & _
Me.ReceiverName & ", " & Me.Pips & _
" FROM Employee_List " & _
"WHERE Employee_List.ID = 1"

Note that as the BoundColumn property of NameCombo is presumably 1 you
don't have to refer to the column by its index as Column(0) will be
the value of the control. The reason for the difference in the
numbers for the BoundColumn and Column properties BTW is that a value
of zero for the former refers to the control's RowIndex value not to
the first column, which unlike the Column property is referenced as 1.

Note also that you do not need to explicitly refer to a control's
Value property as it’s the default property so can be excluded.

Finally, if you ever do include date parameters in a query always
declare them to avoid the possibility of their being misinterpreted as
arithmetical values, e.g.

PARAMETERS
Forms!MyForm!txtStart DATETIME,
Forms!MyForm!txtEnd DATETIME;
SELECT *
FROM MyTable
WHERE MyDate BETWEEN
Forms!MyForm!txtStart AND Forms!MyForm!txtEnd;

Ken Sheridan
Stafford, England

On May 1, 5:02 pm, R Tanner wrote:
On Apr 30, 6:13 pm, MGFoster wrote:



R Tanner wrote:
Hi,

I have a field in a table in an access 2007 database. The field is
set to data type date/time and format medium date. I have a form that
has a textbox set to date/time. The user can select a date from here
with a date picker. When they hit the submit button on the form, the
following query runs and inserts the data into my table.

QueryToSubmitData = "INSERT INTO Transactions (GiverId, DT,
ReceiverId, Amount) SELECT " & Me.NameCombo.Column(0) & _
", " & Me.Date.Value & ", " &
Me.ReceiverName.Value & ", " & Me.Pips.Value & " FROM Employee_List
WHERE " & _
"Employee_List.ID = 1;"

The problem is that the Me.Date.Value that is being inserted is only
inserting the time value into the table, despite the fact that the
user may have the following date selected '04/01/2009'. Any ideas why
this is the case? I have been playing around with it, but haven't
been able to figure out what the problem is.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you put a Breakpoint on the VBA code to see if the variable
QueryToSubmitData has the correct date data? You may also want to
delimit the date value with # delimiters. Ex:

... ", #" & Me.Date.Value & "#, " ...

BTW, "Date" is a VBA built-in function [Date()]. It would be better to
re-name the control to a different name, like "txtDate." Also, you
don't need to include the .Value property when referencing a Control,
because .Value is the default property - the property that is
automatically read when the Control is referenced. Also, MS recommends
that the bang ! be used when referencing Controls, rather than the dot.

Me!txtDate

HTH,
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfo+jIechKqOuFEgEQLRZwCdFsIv+bcnH20PMgNcL32Qsd ElJVcAoOGs
tWWjih5SzeGAIS6PFTRy2+Bf
=lo7V
-----END PGP SIGNATURE-----

Great. Thank you for the info. It was the # signs that I needed.

I will use the ! from now on instead of the dot.

Thanks again for your help.