how to write sql query in VBA

[rahul2310]

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_Plan ned between two date(start date and end date)and then of particular type.
I have written following code to do it

Dim strSQL As String
Dim rs As Recordset

Set db = CurrentDb

strSQL = "SELECT SUM(Inspection_Planned) From tblauditplanned WHERE ([Inspection_Palnned_Dte] Between " & Me.txtddjansd & " And " & Me.txtddjaned & ") AND [Inspection_Type] = ' & cmbsarepinsptype & ' "
Set rs = db.OpenRecordset(strSQL)
txtsarepjan1 = rs.Fields(0)

i am having this code on button and on click of that button query does not produce result neither gives error
plz help
thanks

[Rabbit]

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_Paln ned_Dte. Either that or it was originally spelled incorrectly in the table.
3) Your cmbsarepinsptyp e is within the string and so has become a literal and is not returning the actual value of the combobox.

[NeoPa]

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.