deletion query

[ethan.geerdes]

ok. standby. The other thing that just happened was I realized I still had the Now () in there and changed it to Date() and and the following error popped up.

"Compile Error: Expected Line number or Label or Statement or end of statement" and it has the & highlighted in the beginning of the following line.

& " [Date Assigned] = #" & Date() & "#"

 

[ethan.geerdes]

it was the beginning & that was highlighted.

 

[ethan.geerdes]

I have attached a screen shot of my form. it's pretty simple. 2 Combo Boxes, The first one has the text boxes auto fill with the information about the message, the next combo box is where you select the person I'm going to assign it to and the last text box is for the current date. Then the Submit button is where it needs to update the records.

 

[jdraw]

Nothing attached.

 

[ethan.geerdes]

I noticed that. I don't have the bandwidth right now. sorry.

 

[sneuberg]

Please post all the code in the event you are using.

Certainly you have enough bandwidth to do this. This might help us too.

 

[ethan.geerdes]

So I found out that if the value is text in my table, then I need to have it in a pair of quotes or double quotes and the date needs to go inside a pair of #. What does the & do though?

 

[ethan.geerdes]

So this is a zipped screenshot of my form.

 

[sneuberg]

What does the & do though?

It concatenates. Google "MS Access concatenation". You'll find lots of info.

 

[sneuberg]

So this is a zipped screenshot of my form.

What would help is a screen shot of the table in design view and the full event code for the form.

 

[ethan.geerdes]

It concatenates. Google "MS Access concatenation". You'll find lots of info.

So I was reading one of the guides that you guys lead me to, the ampersand links the lines of code together so that in access it reads it as one line. the following code is what I have come up with. And I have attached my table in design view.

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

 

[Galaxiom]

So I was reading one of the guides that you guys lead me to, the ampersand links the lines of code together so that in access it reads it as one line.

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

Incorrect. The ampersand concatenates two string together.

What you have described and shown in the code is the "Line Continuation". It is a space followed by an underscore. It can be used a maximum of 22 times for a line of code.

 

[Galaxiom]

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

The bold line will result in [Assigned To] being populated with the literal string "Me.Combo10". (Except that the condition would return no records because of a similar mistake in the Where condition.)

Assuming [Assigned To] and [FAM Number] are text fields, you would need:

 [Assigned To] = '" & Me.Combo10 & "'" & _
 " Where [FAM Number] = '" & Me.Combo14 & "';"

The final line continuation is not required. You would get away with it being there only if the next line was blank.

 

[ethan.geerdes]

Well, that is why I am here to learn from you guys. I understood it as linking the lines of code. I am now searching google for MS Access line concatenation.

 

[Galaxiom]

BTW I highly recommend the following format for Line Continuation.

 strSQL = "Update Fleet_Advisory_Messages" _
        & " Set Status = 'Assigned'," _
        & " [Date Assigned] = #" & Date() & "#" & _
        & " etc

Placing the concat at the beginning of the line makes it a lot clearer to ensure it is present.

Also note how the date line is structure. Your's was incorrect.

I assume you are on US regional dates (mm/dd/yyyy) or there would be more trouble.

 

[sneuberg]

Try

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

 

[ethan.geerdes]

Try

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

First off thank you. That fixed it; but why did you concatenate the date and put it inside quotes? I saw that the pound sign had to be there for the date format but I don't know why the other two had to be inside there.

 

[Galaxiom]

First off thank you. That fixed it; but why did you concatenate the date and put it inside quotes?

The date isn't in quotes. The two pairs of quotes are around the rest of the line.

[COLOR=red]"[/COLOR] [Date Assigned] = #[COLOR=red]"[/COLOR] & Date & [COLOR=darkorchid]"[/COLOR]# ,[COLOR=darkorchid]"[/COLOR] & _

 

[ethan.geerdes]

The date isn't in quotes. The two pairs of quotes are around the rest of the line.

[COLOR=red]"[/COLOR] [Date Assigned] = #[COLOR=red]"[/COLOR] & Date & [COLOR=darkorchid]"[/COLOR]# ,[COLOR=darkorchid]"[/COLOR] & _

Okay, your right, it is double quotes sorry, I got excited that it worked but now I want to know why. I see that you have highlighted different sets of double quotes can you break down the syntax for me? I just want to understand.

 

[sneuberg]

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.