Need Help with VBA/SQL Syntax - Please

[mattyhousecat]

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

 

[Joe4]

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.

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!

 

[mattyhousecat]

thanks for your advice!!! I will give that process ago

 

[Micron]

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]

Micron it was a number not text, it worked asoon as I removed the quotes, thats twice you have saved me

 

[Micron]

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.