vb/sql statment (i think)

[lukekelly]

Hi all, I'm by no means an expert in access but I'm trying to expand my knowledge by creating a call logging database. an exchange inbox is linked into the database. This will be where the support calls come from, i have setup a form with a button, the idea being to review the mails in the inbox and then to add them to a waiting list. I decided to it like this as sometimes there are unwanted mails in the inbox. So the idea being when you go through the most recent records you can click a button which will copy the record into another table which will open a "ticket".

Simple enough i know but stuck on the pull the record code for the button. I have tried an append query but to be honest, although i have used query builder in the past, it has only been for reporting so my funtion knowledge of querey append is not too bright so i decided to use code builder for the event and use an sql statement as follows:

strSQL = "INSERT INTO Tickets " _
  & "([Importance], [Priority], [Message Size], [Has Attachments], [Received], [To], [From], [Sender Name], [Subject], [body]) " _
  & "VALUES ('" & Me.Importance & "'" _
  & ", " & Me.Priority & "'" _
  & ", " & Me.Message_Size & "'" _
  & ", " & Me.Has_Attachments & "'" _
  & ", " & Me.Received & "'" _
  & ", " & Me.To & "'" _
  & ", " & Me.From & "'" _
  & ", " & Me.Sender_Name & "'" _
  & ", " & Me.Subject & "'" _
  & ", " & Me.Body & "'" _
  & ")"
DoCmd.RunSQL strSQL

unfortunatly im getting an error when running it: Run-time error '3075' Syntax error (missing operator) in query expression...

Could someone possibly shed some light on this? any help or pointers greatly appreciated

many thanks

luke

 

[jdraw]

Hi all, I'm by no means an expert in access but I'm trying to expand my knowledge by creating a call logging database. an exchange inbox is linked into the database. This will be where the support calls come from, i have setup a form with a button, the idea being to review the mails in the inbox and then to add them to a waiting list. I decided to it like this as sometimes there are unwanted mails in the inbox. So the idea being when you go through the most recent records you can click a button which will copy the record into another table which will open a "ticket".

Simple enough i know but stuck on the pull the record code for the button. I have tried an append query but to be honest, although i have used query builder in the past, it has only been for reporting so my funtion knowledge of querey append is not too bright so i decided to use code builder for the event and use an sql statement as follows:

strSQL = "INSERT INTO Tickets " _
  & "([Importance], [Priority], [Message Size], [Has Attachments], [Received], [To], [From], [Sender Name], [Subject], [body]) " _
  & "VALUES ('" & Me.Importance & "'" _
  & ", " & Me.Priority & "'" _
  & ", " & Me.Message_Size & "'" _
  & ", " & Me.Has_Attachments & "'" _
  & ", " & Me.Received & "'" _
  & ", " & Me.To & "'" _
  & ", " & Me.From & "'" _
  & ", " & Me.Sender_Name & "'" _
  & ", " & Me.Subject & "'" _
  & ", " & Me.Body & "'" _
  & ")"
DoCmd.RunSQL strSQL

unfortunatly im getting an error when running it: Run-time error '3075' Syntax error (missing operator) in query expression...

Could someone possibly shed some light on this? any help or pointers greatly appreciated

many thanks

luke

Try

strSQL = "INSERT INTO Tickets " _
  & "([Importance], [Priority], [Message Size], [Has Attachments], [Received], [To], [From], [Sender Name], [Subject], [body]) " _
  & "VALUES ('" & Me.Importance & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Priority & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Message_Size & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Has_Attachments & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Received & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.To & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.From & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Sender_Name & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Subject & "'" _
  & ",[COLOR="Red"]'[/COLOR]" & Me.Body & "'" _
  & ")"
DoCmd.RunSQL strSQL

 

[lukekelly]

Thank you very much for you reply jdraw, it worked a treat; although im still unsure as to why it needed the single quote but thanks for your help

 

[jdraw]

Thank you very much for you reply jdraw, it worked a treat; although im still unsure as to why it needed the single quote but thanks for your help

You need the quote because you are dealing with a text string.

If you put a debug.print statement, such as

Debug.print strSQL you will see that the rendered sql will have quotes around the string values.

It is similar to
Dim xyz as string

xyz = "This is a string" or 'This is a string'

You need quotes on either end of the value.

It's easier to use single quote to surround the value when you're coding the embedded SQL in vba. You could use double quote ", but you have to double the double quote...
For more info see post #3 here
http://bytes.com/topic/access/answers/211263-double-quotes-data

 

[lukekelly]

thank you for explaining! A+ help