add date formatted fieldname to access table creation?

[PinoyDev]

I am creating a table dynamically of which fields are date. I have a running code below but I want to remove those doble quotes in both sides.. If I remove the chr(34) on both ends, I get this error saving "invalid field defination". My code and table screen shots below:

'create temporary profit & loss table which fields includes the months date
Dim qrystr, tbl_profit_loss As String

Dim lastmonthdates As Date
Dim runningdate As Date

Randomize
qrystr = "CREATE TABLE " & "tbl_profit_loss" & Minute(Now) & Round(Second(Now) * Rnd()) & " ("
qrystr = qrystr & "AccntID VARCHAR(10), AccntName VARCHAR(100), "

runningdate = DateSerial(Year(fromdate), Month(fromdate) - 1, Day(fromdate))
While runningdate < DateSerial(Year(todate), Month(todate), 0)
    runningdate = DateSerial(Year(runningdate), Month(runningdate) + 1, Day(runningdate))
    lastmonthdates = DateSerial(Year(runningdate), Month(runningdate) + 1, 0)
    qrystr = qrystr & Chr(34) & lastmonthdates & Chr(34) & " CURRENCY DEFAULT 0, "
Wend
'---------------------------------------------------------------------------------------------
qrystr = Left(qrystr, Len(qrystr) - 2)  'remove last comma in the right of the query string
qrystr = qrystr & ")"                   'close the query string with closing parenthesis

MsgBox qrystr
scnn.Execute qrystr, , adCmdText

Thank you for helping!

[djjeavons]

Hi

Enclosing the field names in square brackets [] within the sql statement will allow you to create them without the need for quote characters.

Having said that, it is not good practice to have field names contain characters such as "/". Is there not a better naming convention you can use for these field names?

[PinoyDev]

thanks djjeavons!

It works!

For naming convention, this is the only way I know since I want to generate profit and loss report in a monthly basis depending on the users date(fromdate and todate)..This temporary table will be link to datagrid as profit and loss..perhaps any better idea on this situation is highly appreciated..

Modified code:

Dim qrystr, tbl_profit_loss As String
Dim lastmonthdates As Date
Dim runningdate As Date

'create temporary profit & loss table which fields includes the months date

Randomize
qrystr = "CREATE TABLE " & "tbl_profit_loss" & Minute(Now) & Round(Second(Now) * Rnd(1000)) & " ("
qrystr = qrystr & "AccntID VARCHAR(10), AccntName VARCHAR(100), "

runningdate = DateSerial(Year(fromdate), Month(fromdate) - 1, Day(fromdate))
While runningdate < DateSerial(Year(todate), Month(todate), 0)
    runningdate = DateSerial(Year(runningdate), Month(runningdate) + 1, Day(runningdate))
    lastmonthdates = DateSerial(Year(runningdate), Month(runningdate) + 1, 0)
    qrystr = qrystr & "[" & lastmonthdates & "]" & " CURRENCY DEFAULT 0, "
Wend
'---------------------------------------------------------------------------------------------
qrystr = Left(qrystr, Len(qrystr) - 2)  'remove last comma in the right of the query string
qrystr = qrystr & ")"                   'close the query string with closing parenthesis

scnn.Execute qrystr, , adCmdText

Thank you!

[djjeavons]

Glad it worked.

With regards to naming convention, could you not use 30Jan2015 and so on.

If you do stick with your current naming convention, be sure to continue wrapping them in square brackets when you are performing SQL statements on that table.

[PinoyDev]

thanks djjeavons for that suggestions!