Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
The following code works and creates text dates such as 1/12/2004. In the loop below, I want a SQL statement to input that date into a new table, "NewDates"
NewDates has 2 fields, ID (autonumber) and TestDate (Date/Time)

The statement I want to use is:
INSERT INTO NewDates (TestDate)

Any help would be much appreciated.

VB
Private Sub RedrawTxtRelativeList()
Dim hdclsOutput As clsNewDate
Dim iLoop As Integer

    txtRelativeList.Value = ""
    For iLoop = 1 To 100
        Set hdclsOutput = New clsNewDate
        Set hdclsOutput = hdclsDateOfDeath.Copy

        hdclsOutput.RelativeStep iLoop, ComboCalcType
        txtRelativeList.Value = txtRelativeList.Value & hdclsOutput.GDate & vbCrLf

'{
'ADD CODE HERE TO LOAD DATE INTO "NewDates" TABLE using SQL INPUT STATEMENT
'}

        Set hdclsOutput = Nothing

    Next iLoop
End Sub

Private Sub cmdCalcNewDate_Click()
    RedrawTxtRelativeList
End Sub


What I have tried:

VB
Private Sub RedrawTxtRelativeList()
Dim hdclsOutput As clsNewDate
Dim iLoop As Integer

'My Additions
Dim strSQL As String
Dim TestDate as Date


    txtRelativeList.Value = ""
    For iLoop = 1 To 100
        Set hdclsOutput = New clsNewDate
        Set hdclsOutput = hdclsDateOfDeath.Copy

        hdclsOutput.RelativeStep iLoop, ComboCalcType
        txtRelativeList.Value = txtRelativeList.Value & hdclsOutput.GDate & vbCrLf

'My additions
        Set TestDate  = txtRelativeList.Value
        strSQL = "INSERT" & "INTO" & "tblNewDates" & "VALUES" & "(TestDate)"

        Set hdclsOutput = Nothing

    Next iLoop
End Sub

Private Sub cmdCalcNewDate_Click()
    RedrawTxtRelativeList
End Sub


UPDATED by NELEK:

[Non Solution 2 copied here]
Thanks for your response (solution 1). I've put spaces into the SQL statement. The debugger stops at
VB
Set TestDate  = txtRelativeList.Value
I've tried this without the "Set" with the same results. I'm trying to understand what you mean by needing a reference to a database.

[Non Solution 4 copied here]
I still get stopped at the following statement:
VB
TestDate  = Format(txtRelativeList.Value, "yyyy-MM-dd")

I get the feeling that the error is related to the fact that while txtRelativeList.Value is a string, it is not putting TestDate into a date format.
Posted
Updated 12-Dec-20 1:19am
v2
Comments
Jo_vb.net 9-Dec-20 17:32pm    
strSQL = "INSERT" & "INTO" & "tblNewDates" & "VALUES" & "(TestDate)"
string has no blanks!

Result is:
strSQL = "INSERTINTOtblNewDatesVALUES(TestDate)"
CHill60 10-Dec-20 6:52am    
Re your comment "I'm trying to understand what you mean by needing a reference to a database." - How are you connecting to the database? You are doing nothing with the SQL command.
Also - you should not concatenate strings to construct SQL commands like that - you need to avoid SQL Injection | OWASP[^]
CHill60 13-Dec-20 5:00am    
What is the value in txtRelativeList.Value and what is the error message reported?

You need a "reference" to a database, among other things.

INSERT INTO statement (Microsoft Access SQL) | Microsoft Docs[^]
 
Share this answer
 
Comments
Nelek 12-Dec-20 7:20am    
OP posted a non solution to answer you / leave you a comment. I have updated the question adding his text.

Just in case you want to read it and update your solution
Your SQL is wrong. Try something like
VB
strSQL = "INSERT INTO tblNewDates (TestDate) values ('" & TestDate & "')"
(Although note my comment above about SQL Injection[^] )

Now to the value of TestDate.
Firstly, do not use Set with strings.
Secondly, ensure that whatever is in that variable is a date, in the correct format. It is always advisable to Use Unambiguous Date formats[^] so that could be something like
VB
TestDate  = Format(txtRelativeList.Value, "yyyy-MM-dd")
If you wanted to be sure to avoid SQL Injection you could even use
VB
TestDate  = Format(CDate(txtRelativeList.Value), "yyyy-MM-dd")
as that will throw a Run-time error '13': Type mismatch exception if txtRelativeList.Value is not an actual date

Edit: I forgot to tell you how to execute the SQL - i.e. the "reference to the database" bit

You could use
VB
CurrentDb.Execute strSQL, dbFailOnError
although for linked tables I sometimes also use
VB
CurrentProject.Connection.Execute strSQL
 
Share this answer
 
v3
Comments
Nelek 12-Dec-20 7:21am    
OP posted a non solution to answer you / leave you a comment. I have updated the question adding his text.

Just in case you want to read it and update your solution
CHill60 13-Dec-20 4:57am    
Thanks, I'll take another look
I still get stopped at the following statement:
TestDate  = Format(txtRelativeList.Value, "yyyy-MM-dd")

I get the feeling that the error is related to the fact that while txtRelativeList.Value is a string, it is not putting TestDate into a date format.
 
Share this answer
 
Comments
Richard MacCutchan 11-Dec-20 14:08pm    
Please do not post further questions as Solutions. Use the Improve question link above and add the details there. Posting as Solutions suggest to people that your problem has been resolved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900