INSERT SQL

[Valentine]

This is my first attempt at an INSERT SQL statement:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO [Leave] ([Control Number])" _
               & "values ('" & Me.txtControl.Value & "')" _
               & "WHERE (((Leave.[Start Date] = me.txtStart.value) AND ((Leave.[End Date])= me.txtEnd.value))"
    DoCmd.SetWarnings True

I have users input the days off they want start and end dates. Then an admin approves the days and adds a control number. I am trying to add the control number to the table but only if it matches the dates selected.

I am getting an error "Query input must contain at least one table or query. the table's name is Leave I don't understand what I'm missing

 

[theDBguy]

Try assigning the SQL statement into a String variable and then examine what you're asking Access to do using Debug.Print.

 

[GinaWhipp]

Hmm, where's the Select which be on the second line, i.e.

"INSERT INTO ...
"SELECT ...

 

[Gasman]

You also have no space between the statements on each line.
As stated, put into a string variable, debug.print it and post back here with the output, if you still cannot get it to work.
Might be worth googling the syntax as well.

 

[Gasman]

Hmm, where's the Select which be on the second line, i.e.

"INSERT INTO ...
"SELECT ...

I thought you could use a Select or simply Values?

 

[plog]

I am trying to add the control number to the table but only if it matches the dates selected.

Sounds like you don't want an INSERT at all but an UPDATE. INSERT adds new records, UPDATE makes edits to existing records.

I am trying to add the control number to the table but only if it matches the dates selected.

That sounds like it might be wrong. Sounds like you want to add a control number to just 1 record at any time. But if you have 2 records with the same dates they both would be updated with that control number. I really think instead of dates you use the table's primary key to identify records to update.

 

[The_Doc_Man]

OK, two errors that leap out. Both have been mentioned, but I will add my spin to them just so you see it from a different viewpoint.

Access SQL, like ANY OTHER SQL, uses the space character as a delimiter between elements of the statement. So if you assign the SQL you wrote to a string for previewing, exactly as you wrote it, you would see that some significant spaces are missing. SQL cannot parse the statement because it would be missing some critical elements due to being unable to recognize them.

The INSERT action allows you to use a WHERE clause - but if you don't have a SELECT statement, it has to take the values you included in the VALUES clause. But that value didn't have dates associated with it. So the WHERE clause inserts no records because nothing matches the criteria of the WHERE clause you supplied. But then, your words do not correspond to the SQL anyway. To change the contents of existing records, you use an UPDATE action.

 

[Valentine]

after what y'all have said I did change my statement to an update which is more in line with what i want to do yet I am getting datatype mismatch error now.

    Dim dbCurr As DAO.Database
    
    Set dbCurr = CurrentDb()
    dbCurr.Execute "UPDATE Leave " _
                 & "SET [Control Number] = '" & Me.txtControl.Value & "' " _
                 & "WHERE (((Leave.[Start Date]) = '" & Me.txtStart.Value & "') AND ((Leave.[End Date]) = '" & Me.txtEnd.Value & "'));"

I have the txtControl field set to number as well as the table set to number, both date fields are set to date. I have the form as data entry and none of the text boxes are bound because i dont want a second entry into the table.

 

[Gasman]

String are enclosed with ' or """ if a single quote could be in the string.
Dates with #
Numbers are not enclosed by anything.

Again, put it all into a string variable and debug.print it first, until you get it correct.

 

[Valentine]

I don't understand how to use debug.print. I have just been OTJ trained and never told that. I tried to do what i googled and nothing happened.

    Dim dbCurr As DAO.Database
    strControl As String
    
    Set dbCurr = CurrentDb()
    strControl = "UPDATE Leave " _
                 & "SET [Control Number] = Me.txtControl.Value " _
                 & "WHERE (((Leave.[Start Date]) = '# & Me.txtStart.Value & #') AND ((Leave.[End Date]) = '# & Me.txtEnd.Value & #'));"
    Debug.Print strControl
'    dbCurr.Execute "UPDATE Leave " _
'                 & "SET [Control Number] = Me.txtControl.Value " _
'                 & "WHERE (((Leave.[Start Date]) = '# & Me.txtStart.Value & #') AND ((Leave.[End Date]) = '# & Me.txtEnd.Value & #'));"

 

[Gasman]

Go to the immediate window ( ctrl & g) and the output of the debug print will be output to there.
You can also test code like this in there.
I forgot to say, dates must be in mm/dd/yyyy or yyyymmdd format.

 

[Gasman]

Use " to enclose the literal strings. Do not use ' with dates or numbers.
In town on my phone, so cannot type a lot.

 

[Valentine]

Wow thank you this is amazing!!! I was able to go step by step and see the output of each variable, the debug method I was taught was to put watches on the variables and place a stop in the line and see what your variables are filled with. So I got the variables to do pull in the proper info but the table isn't being updated.

    Dim dbCurr As DAO.Database
'    Dim strControl As String
'
    Set dbCurr = CurrentDb()
'    strControl = "UPDATE Leave " _
'                 & "SET [Control Number] = '" & Me.txtControl.Value & "' " _
'                 & "WHERE (((Leave.[Start Date]) = " & Me.txtStart.Value & " AND (Leave.[End Date]) = " & Me.txtEnd.Value & ");"
'    Debug.Print strControl
    dbCurr.Execute "UPDATE Leave " _
                 & "SET [Control Number] = ' Me.txtControl.Value '" _
                 & "WHERE ((Leave.[Start Date]) = " & Me.txtStart.Value & " AND (Leave.[End Date]) = " & Me.txtEnd.Value & ");"

 

[Gasman]

What is the point in putting everything into a string variable, and then not using it in the Execute statement?
You are not enclosing your dates correctly and possibly not in the correct format.
That output you can copy and paste back here so we can check the syntax.

 

[Valentine]

ok so here is the code:

Private Sub cmdControl_Click()

    Dim dbCurr As DAO.Database
    Dim strControl As String

    Set dbCurr = CurrentDb()
    strControl = "UPDATE Leave " _
                 & "SET [Control Number] = " & Me.txtControl.Value & " " _
                 & "WHERE (((Leave.[Start Date]) = " & Me.txtStart.Value & " AND (Leave.[End Date]) = " & Me.txtEnd.Value & ");"
    Debug.Print strControl
'    dbCurr.Execute "UPDATE Leave " _
'                 & "SET [Control Number] = '" & Me.txtControl.Value & "' " _
'                 & "WHERE ((Leave.[Start Date]) = " & Me.txtStart.Value & " AND (Leave.[End Date]) = " & Me.txtEnd.Value & ");"

End Sub

my output is:
UPDATE Leave SET [Control Number] = GM 123458 WHERE (((Leave.[Start Date]) = 4/17/2022 AND (Leave.[End Date]) = 4/23/2022);

 

[The_Doc_Man]

OK, look at that string. You have three erroneous bits of syntax. I can't use the code tags AND color highlights, so.. no code tags.

UPDATE Leave SET [Control Number] = GM 123458 WHERE (((Leave.[Start Date]) = 4/17/2022 AND (Leave.[End Date]) = 4/23/2022);

Take a look at my brief discussion above about delimiters. Your control number is a value AND contains a delimiter so you need to enclose the desired value in quotes. SET [Control Number] = 'GM 123458' should work better. Then, your two dates are "naked" in that they have what could be seen as a legit mathematical statement involving double division. You need to also "encapsulate" them - but for dates, the correct character for date data types is the octothorpe (#, a.k.a. pound-sign). So ... WHERE (((Leave.[Start Date]) = #4/17/2022# AND ... would work for you.

How do you do that?

    strControl = "UPDATE Leave " _
                 & "SET [Control Number] = '" & Me.txtControl.Value & "' " _
                 & "WHERE (((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"

I'm using the apostrophe as the quoting character inside that quoted string that you are building because the string as a whole includes double-quote marks. There are other approaches, but that is the simplest.

 

[Gasman]

Pust this code in a module, then use it to format your dates.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

eg

& format(Me.txtStart,strcJetDate)

and use on the other dates.
Then post back what you have, if you have not already got it from The_Doc_Man's post.

 

[Valentine]

oh ok, so everything has to be inside some form of bracketing. I thought when I saw the variable print out as exactly what I wanted I thought it was working. I see what you are saying about the date looks like division so the # on either side make it a date. Thank you for the description of WHY i put those there. Now it is working as intended with the #'s that I was missing and the ' as well.

 

[MarkK]

My 2c is that running SQL in VBA is simpler, safer, more readable, and easier to maintain, if you use a temporary DAO.QueryDef object. Consider code like...

Sub TempQDFSample()
    
    Const SQL As String = _
        "INSERT INTO MyTable " & _
            "( SomeDate, StringField ) " & _
        "SELECT Date1, prmComment " & _
        "FROM OtherTable " & _
        "WHERE StartDate = prmD1 AND EndDate prmD2 "
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("prmComment") = Me.txtComment
        .Parameters("prmD1") = Me.tbDateStart
        .Parameters("prmD2") = Me.tbDateEnd
        .Execute dbFailOnError
    End With

End Sub

- String parameters are handled for you so your text data can contain apostrophes and double quotes. Names like O'Malley, and data like 8' - 2" x 4" are handled seamlessly, and exactly as entered by your user.
- Date parameters are handled for you so you don't have to worry about formats. Simply assign a valid date to a date parameter. Done.

 

[GPGeorge]

Spoiler: etc

Running queries using .Excecute is generally better than using built in Access methods but that doesn't mean that you need to embed the SQL string.

QBE is still the simplest way to create queries, especially for people who can't write SQL without a manual open beside them. I've been writing SQL since the 70's when I was working with DB2 (IBM's RDBMS) I used to dream of a tool to create SQL strings some way other than by typing them out character by character. The QBE was one of the things that made me fall in love with Access because it took away a large part of the tedium of writing SQL.

If you are working entirely in SQL Server then you can use the tools in SQL Server which provide intellisense to help you to write the queries. if you are working in VBA, you are SOL and have to type everything by hand or find similar queries and cut and paste. Not my idea of fun.

QBE has a couple of problems. One that really irks people who know how to write SQL (even me) is that it reformats your SQL statement and if your criteria is complex enough, almost turns it into gobbeldy gook. There is a solution though. and that is to NEVER switch the query to QBE view before saving it if you want YOUR formatting to stick. It is during the save that Access rewrites the query to make it easier for itself to render it in QBE view. When I have really complex criteria, I also use a safety play and store the SQL string in a table so I can recover it intact if I need to.

So probably 90% of what I need to do, I can do using QBE (assuming I don't care what the SQL string looks like - and why would I). For complex criteria, I switch to SQL view. I almost never use subselects since Access does not optimize them well. I use nested queries instead. They also happen to be easier to test. Plus QBE can't render them so you are stuck looking at them garbled by Access unless you embed them in VBA OR use the trick I described earlier.

MS has been promising for at least 20 years to give us an updated QBE. Maybe next year in Jerusalem.

If you can believe the Access Road Map, you don't have to wait a full year from now.