By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

how to write sql query in VBA

P: 62
I have form on which there are three text boxes first is start date, second is end date and third is type
I am trying to write sql query in VBA to fetch data which is sum of inspection_Planned between two date(start date and end date)and then of particular type.
I have written following code to do it
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim rs As Recordset
  3.  
  4. Set db = CurrentDb
  5.  
  6. strSQL = "SELECT SUM(Inspection_Planned) From tblauditplanned WHERE ([Inspection_Palnned_Dte] Between " & Me.txtddjansd & " And " & Me.txtddjaned & ") AND [Inspection_Type] = ' & cmbsarepinsptype & ' "
  7. Set rs = db.OpenRecordset(strSQL)
  8. txtsarepjan1 = rs.Fields(0)
  9.  
i am having this code on button and on click of that button query does not produce result neither gives error
plz help
thanks
Dec 30 '13 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,366
I'm surprised you don't get any errors. I see at least 3.

1) Date literals need to be surrounded by pound (#) symbols.
2) I'm pretty sure this is spelled incorrectly: Inspection_Palnned_Dte. Either that or it was originally spelled incorrectly in the table.
3) Your cmbsarepinsptype is within the string and so has become a literal and is not returning the actual value of the combobox.
Dec 30 '13 #2

NeoPa
Expert Mod 15k+
P: 31,494
See Literal DateTimes and Their Delimiters (#).

A good way to debug SQL is to take the resulting SQL from the string and try it in a QueryDef. QueryDefs have three views :
  1. Design view - which shows you the design in a grid.
  2. Datasheet view - which shows you the results. For an action query it shows you what will be affected.
  3. SQL view - which shows you (and allows you to modify directly) the SQL of the query.
You need to paste the SQL resulting from your code into the SQL view of a new query to see what it does. If it fails it generally gives more helpful messages than simply opening a recordset based on it from your code.
Dec 30 '13 #3

Post your reply

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