Hi,

I have a combo box cboDate with the value "01/01/12" on a form. I
click a button on the same form to run a query to insert records into
a table. I want to replace the #01/01/12# value in vSQL with the date
value in cboDate? How do I do this?

Button Code:

Private Sub cmdBuildMembersTemp_Click()

'Append all the records in qryTemp5 to tblMembersTemp

Dim qry As QueryDef
Dim vSQL As String

'First Delete all records in tblMembersTemp
Set qry = CurrentDb.CreateQueryDef("", "DELETE * FROM
tblMembersTemp")
qry.Execute

vSQL = "INSERT INTO tblMembersTemp ( lngMembershipNumber,
CurrentDate, FirstName, LastName " & _
"SELECT qryTemp5.lngMembershipNumber,
Format(#01/01/12#,""mmmm d, yyyy"") AS CurrentDate,
qryTemp5.FirstName, qryTemp5.LastName " & _
"FROM qryTemp5;"

Set qry = CurrentDb.CreateQueryDef("", vSQL)
qry.Execute
qry.Close
Set qry = Nothing

end Sub


Thanks, appreciate your help.

Barry

"SELECT qryTemp5.lngMembershipNumber, Format(#" & cboDate & "#,"'mmmm d, yyyy") ÁS CurrentDate, qryTemp5.FirstName, qryTemp5.LastName " & _
"FROM qryTemp5;"

Peter

Sorry it did not work. The exact expression I used was:
(#"" & cboDate & ""#,""mmmm d, yyyy"")
In order to get a single quote you must put two in. Anyway the exact
error was:
Syntax error in date in query expression 'Format(#" & cboDate & "#,
"mmmn d, yyyy")'.

On Jan 20, 5:29*pm, XPS350 <xps... (AT) gmail (DOT) com> wrote:

Barry Edmund Wright <barry.edmund.wright (AT) witstoronto (DOT) ca> wrote in
news:72fa5219-e831-4746-abde-af0ae17e1eca (AT) m11g2000yqe (DOT) googlegroups.co
m:

The table stores the date as a double precision number, with the
whole portion representing the number of days since 1899/12/31 (e.g.
Jan 1 1900 is day 1) and the fractional part gives the time (noon is
..5, 6pm is .75). Therefore, attempting to format the date will cause
problems.

You also need to make vba evaluate the contents and write the result
to the string,

Try
vSQL = "INSERT INTO tblMembersTemp " & _
"(lngMembershipNumber, CurrentDate, FirstName, LastName) " & _
"SELECT qryTemp5.lngMembershipNumber, " & _
"#" & me.cbodate & "# AS CurrentDate, " & _
"qryTemp5.FirstName, qryTemp5.LastName " & _
FROM qryTemp5;"



--
Bob Q.
PA is y I've altered my address.

You don't need the # characters around the combo box reference:

'Format(" & cboDate & ",""mmmn d, yyyy"")

"Barry Edmund Wright" wrote in message
news:a4254eb7-4d3c-48d0-9b29-863d716e3b02 (AT) e27g2000vbu (DOT) googlegroups.com...

Sorry it did not work. The exact expression I used was:
(#"" & cboDate & ""#,""mmmm d, yyyy"")
In order to get a single quote you must put two in. Anyway the exact
error was:
Syntax error in date in query expression 'Format(#" & cboDate & "#,
"mmmn d, yyyy")'.

On Jan 20, 5:29 pm, XPS350 <xps... (AT) gmail (DOT) com> wrote:

Thanks, I realized from your advice and Doug's code that the vSQL
wanted a string so I created a variable vDate as string and populated
it with the string value of the formated date in the combo box then
used that in the vSQL.

The code below worked - don't forget the single quote on both sides of
the vdate:
'" & vDate & "'

Dim vDate As String

vDate = CStr(Format(CDate(Me.cboDate), "mmmm d, yyyy"))

* vSQL = "INSERT INTO tblMembersTemp " & _
* "(lngMembershipNumber, CurrentDate, FirstName, LastName) " & _
* "SELECT qryTemp5.lngMembershipNumber, '" & _
* & vDate & "' *AS CurrentDate, " & _
* "qryTemp5.FirstName, qryTemp5.LastName " & _
* FROM qryTemp5;"


On Jan 20, 6:28*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:

Barry Edmund Wright <barry.edmund.wright (AT) witstoronto (DOT) ca> wrote in
news:4260a4e3-39f9-4623-a190-a91c34ccd209 (AT) do4g2000vbb (DOT) googlegroups.co
m:


I'm happy you got it working...As to your single quotes, that tells
me that your target field, CurrentDate, is not a date field at all,
but is really a text field in the table. If it were a date, the
delimiters I used, the # marks, would be the appropriate ones.

Bob


--
Bob Q.
PA is y I've altered my address.