deletion query

[ethan.geerdes]

There are string literals and string variables. When forming a string out of strings where one stops and the other starts they are joined (concatenated) with the ampersand & (sometimes +)

strSQL = "Update Fleet_Advisory_Messages" & _
" Set Status = 'Assigned' , " & _
" [Date Assigned] = #" & Date & "# ," & _
" [Assigned To] = '" & Me.Combo10 & "'" & _
" Where [FAM Number] = '" & Me.Combo14 & "';"

There's plenty of information on this to read if you just google "MS Access concatenation" Lot of videos too.

Thank you. I get it now. it's because it's a string made of strings.

So I took this information and added it to another form for completion but it clears the date assigned. I know databases only do what we tell them to do so I changed the code a little bit.

 strSQL = "Update Fleet_Advisory_Messages" & _
" Set Status = 'Complete' , " & _
" [Date Complete] = #" & Date & "# ," & _
" Where [FAM Number] = '" & Me.Combo0 & "';"

So, why would it be clearing out the date assigned? is it because it's named strSQL like the other one and therefore "overwriting" the record?

 

[sneuberg]

Using the same name for a string in a different form or for that matter different procedure wouldn't cause something like this. It's surprising to me that this is not giving you a syntax error as the screen shot of your table shows the Date Complete as Text and not a date.

I can't tell why this is happening. If you upload your database I'll try to figure it out.

 

[ethan.geerdes]

I have uploaded my db.

 

[sneuberg]

I can't see how that was clearing the Date Assigned. The code had two problems. First it didn't have a CurrentDb.Execute strSQL statement so it wasn't doing anything. Second when I put that in it was giving me a syntax error because you had an unneeded comma after Date. I suggest putting in Debug.Print statement like I have below when you have syntax problems. You can see the SQL in the immediate window as the compiler sees it and it makes it easier to see these types of problems. Of course take them out when you are done debugging.

Private Sub Command14_Click()
Dim strSQL As String
  
strSQL = "Update Fleet_Advisory_Messages" & _
" Set Status = 'Complete' , " & _
" [Date Complete] = #" & Date & "# " & _
" Where [FAM Number] = '" & Me.Combo0 & "';"
Debug.Print strSQL

CurrentDb.Execute strSQL


End Sub