INSERT SQL (1 Viewer)

[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?

 

[Pat Hartman]

The INSERT query has two different formats. Insert ONE record from a set of values OR Insert multiple records using a select statement against a table or query.

If you don't know how to write SQL statements, it is best to start with the QBE. Once you get the framework, you can either add parameters and use the saved querydef or you can switch to SQL view and copy the SQL string into VBA and populate the variables that way.

 

[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.

 

[Pat Hartman]

Two additional things.
1. It is poor practice to use embedded spaces or special characters in your object names. You should also avoid like the plague using the names of properties or functions as column names - ESPECIALLY bad are "Name" and "Date". They will cause strange errors in certain cases when used a column names. Access warns you not to use them when you are creating a table but amazingly people just blow by the warning. Names should contain only the letters a-z, A-Z, the numbers 0-9 and the underscore. I prefer CamelCase as the name style but others prefer the_underscore. I only use the underscore for special ephansis because I really hate having to shift to type the underscore.
2. Access uses an excessive number of parentheses when you use the QBE to build queries. It does that to help itself in the conversion between string and graphic display modes. That doesn't mean that if you use embedded SQL that you need to mimic the foibles of the QBE.
i.e.
& "WHERE (((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"
should be
& "WHERE Leave.[Start Date] = #" & Me.txtStart & "# AND Leave.[End Date] = #" & Me.txtEnd & "#;"

There is nothing in the expression that needs to be enclosed in parentheses. I also removed the ".Value" property since it isn't necessary. The square brackets are forced because you are using poorly formed data names. Otherwise the string would be:
& "WHERE Leave.StartDate = #" & Me.txtStart & "# AND Leave.EndDate = #" & Me.txtEnd & "#;"