Need Help with VBA/SQL Syntax - Please

mattyhousecat

New Member
Joined
Nov 9, 2017
Messages
11
SQL_tblEstReqs_Remove = "DELETE * FROM tblEstReqs WHERE (tblEstReqs.EstNo = '" & tempEstNo & "') AND (tblEstReqs.StartDateAEMIS = #" & tempstartAEMIS & "# ) AND (tblEstReqs.EndDateAEMIS = #" & tempendAEMIS & "#);"

I think there is a better way i could do this, im not very good with VBA + SQL :(
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,939
Office Version
  1. 365
Platform
  1. Windows
Here is how I usually like to do these sort of things.

First, manually create an example of the query I want to build using the normal Query Builder. When I have it doing what I want, change it to SQL View and copy and paste the code out to Word (or a Text file). This is the exact code I want to build in VBA/SQL, so this is my guide.

Next, I try to build the VBA/SQL code I need to do this, like you have above.

Then, I put a MsgBox in my VBA code to return that code I built to a Message Box, i.e.
Code:
MsgBox [COLOR=#333333]SQL_tblEstReqs_Remove[/COLOR]

Then I run my code to that point, and compare what the Message Box returns to the example code I pasted in Word.
Look for differences and correct them, until I have the code looking the same. When it does, my code should be good!
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,958
Office Version
  1. 365
Platform
  1. Windows
I think there is a better way i could do this
FWIW, it looks fine to me ;)
As long as you correctly delimit (e.g. tempEstNo must be text because you wrap it in single quotes) and concatenate, this is how many examples would look. Yours is rather short, actually. If I had one other pointer to offer, you may find that it's harder to deal with field values that contain apostrophes (e.g. O'Conner) in vba and you can't always avoid them. So a simpler approach can also be to run a query that references fields from a form as sql isn't as tempermental with apostrophes as vba constructed sql is. If you can't use a query, or don't want to have to deal with the possibility that a form field could be empty, then surround your problem field reference with """ on each side.
 

mattyhousecat

New Member
Joined
Nov 9, 2017
Messages
11
Micron it was a number not text, it worked asoon as I removed the quotes, thats twice you have saved me
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,958
Office Version
  1. 365
Platform
  1. Windows
Glad to hear I was able to help! Equally surprised, since your post said nothing about you having a problem. Thought you were just looking for a better way to write what you had.

While joe4's advice is certainly valid, I don't think following it would have solved it for you since once you have a working sql that you need to express in vba, you'd have to use date delimiters and such, which means you would have (probably?) inserted the quotes anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,610
Messages
5,548,983
Members
410,887
Latest member
sjohn627
Top