468,790 Members | 1,834 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,790 developers. It's quick & easy.

Cannot pass data to sql delete criteria

2 2Bits
Hi Everyone
Im struggling to pass a form value to a public sub sqlstring, when I reference the form directly, it works...

Expand|Select|Wrap|Line Numbers
  1. Public Sub CalDeleteTopSheet(strCal As String)
  2.  
  3. Dim strSqlDelete As String
  4.  
  5. strSqlDelete = "DELETE " & strCal & ".Summary" & vbCrLf & _
  6. "FROM " & strCal & vbCrLf & _
  7. "WHERE (((" & strCal & ".Summary)= [Forms]![NewTopSheet]![txtCustomerID] ));"
  8.  
  9. End Sub
  10.  
When I substitute the form reference [Forms]![NewTopSheet]![txtCustomerID] for strID I get a datatype mismatch error...

Expand|Select|Wrap|Line Numbers
  1. Public Sub CalDeleteTopSheet(strCal As String, strID As String)
  2.  
  3. Dim strSqlDelete As String
  4.  
  5. strSqlDelete = "DELETE " & strCal & ".Summary" & vbCrLf & _
  6. "FROM " & strCal & vbCrLf & _
  7. "WHERE (((" & strCal & ".Summary)= " & strID & "));"
  8.  
  9. End Sub
  10.  
I checked the datatype of both strID and strCal they're both strings
The table field thats being referenced for deletion strCal.Summary is in long text, do I need to convert strID to long text, not sure how to do it.

Thank you so much in advance

Cheers Eddie
Jun 15 '21 #1

✓ answered by isladogs

You need to use single quotes around the strID variable
Expand|Select|Wrap|Line Numbers
  1. . . .  ='" & strID &"'));"

4 3017
isladogs
277 Expert 256MB
You need to use single quotes around the strID variable
Expand|Select|Wrap|Line Numbers
  1. . . .  ='" & strID &"'));"
Jun 15 '21 #2
NeoPa
32,132 Expert Mod 16PB
It looks like you may benefit from reading Quotes (') and Double-Quotes (") - Where and When to use them.

Essentially IslaDogs is on the money, though you need to understand the difference between the handling of literal values within SQL and the handling of references. The form reference - is a reference of course. The value however, is a literal value and needs to be handled differently in order for the SQL engine to properly understand what it is you're asking it to do.
Jun 15 '21 #3
Eddie2020
2 2Bits
Argh... Thank you Isla, and thank Neo for the link, I will take best practices on!
Cheers Eddie
Jun 15 '21 #4
isladogs
277 Expert 256MB
No problem. You're welcome
Jun 16 '21 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Vijay | last post: by
reply views Thread by Holger Marzen | last post: by
2 posts views Thread by Anil Pundhir | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.