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
-----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.
HTH,
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSfo+jIechKqOuFEgEQLRZwCdFsIv+bcnH20PMgNcL32QsdElJVcAoOGs
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.
