Need to use DTPicker value in VBA code 1

[pcsmurf]

On my form I have a DTPicker control. I also have a command button which when picked should use the value of the DTPicker control in a query.

The code of the command button is:

Dim db As Database, rst As Recordset, qdf As QueryDef
Dim sSQL As String
Dim cOutFilename, cPriceDate As String

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("&quot

cPriceDate = Format(dtCurrent.Value, "mm/dd/yy&quot

cOutFilename = "D_" & cPriceDate

sSQL = "SELECT rsDataStreamA.ComGrp, rsDataStreamA.SubGrp, rsDataStreamA.RawCom, rsDataStreamA.ComType, rsDataStreamA.Description, rsDataStreamA.PrintDescrip1, rsDataStreamA.PrintDescrip2, rsPrices.ContractNo, rsPrices.Contract, rsPrices.AsAt, rsPrices.SettSpot, rsPrices.Prev, rsPrices.High, rsPrices.Low "
sSQL = sSQL & "INTO " & cOutFilename & " "
sSQL = sSQL & "FROM rsDataStreamA INNER JOIN rsPrices ON rsDataStreamA.ComID = rsPrices.ComID "
sSQL = sSQL & "WHERE (((rsPrices.AsAt) = #" & cPriceDate & "#)) "
sSQL = sSQL & "ORDER BY rsDataStreamA.tblCommodityGroups.Ord, rsDataStreamA.tblSubGroups.Ord, rsDataStreamA.tblRawCommodities.Ord, rsDataStreamA.qry_commodities.Ord;"

qdf.SQL = sSQL
qdf.Execute

This should result in a new table called D_ followed by the date of the DTPicker. However trying to run it I get

run time error '3067'
Query Input must contain at least one table or query.

Changing the line
cOutFilename = "D_" & cPriceDate
to
cOutFilename = "D_270203"

works fine. So it seems the value of cOutFilename is not being set as a string.

Any suggestions ?

 

[combo]

The only difference is value of cOutFilename, with full code is D_27/02/03, explicit value is D_270203. Slash can be reserved word (and is in explioer), so building string without it can remove error.

 

[pcsmurf]

Simple when you know how!

Thanks

 

[combo]

pcsmurf,
try
[tt]cOutFilename = "D_" & Format(dtCurrent.Value, "mmddyy&quot[/tt]

combo

 

[pcsmurf]

Yes, I've done that and everything now works as expected.