help me in vba sql

  • Thread starter MUHAMAMD SALIM SHAHZAD
  • Start date
M

MUHAMAMD SALIM SHAHZAD

dear sir can you help me that where i am wrong in this syntax

Private Sub btnTest_Click()
Dim sqlrenewal As String

sqlrenewal = "INSERT INTO tblCS (PolicyNo, Sistname, exp_SI, exp_Prem,
dt_Renewal )" & _
"SELECT DISTINCT" & _
"qryFireRenewal.POLICYNO, qryFireRenewal.SISTNAME,
qryFireRenewal.SI_TOTAL, qryFireRenewal.PREM_TOTAL,
qryFireRenewal.EXPIRYDATE" & _
"FROM qryFireRenewal" & _
" WHERE (((qryFireRenewal.EXPIRYDATE)>= " & _
Forms!reportFire!txtStartDate & _
" And (qryFireRenewal.EXPIRYDATE)<= " & _
Forms!reportFire!txtEndDate & _
" ));"""

DoCmd.SetWarnings False
DoCmd.RunSQL sqlrenewal

End Sub
 
R

Rick Brandt

MUHAMAMD SALIM SHAHZAD said:
dear sir can you help me that where i am wrong in this syntax

Private Sub btnTest_Click()
Dim sqlrenewal As String

sqlrenewal = "INSERT INTO tblCS (PolicyNo, Sistname, exp_SI, exp_Prem,
dt_Renewal )" & _
"SELECT DISTINCT" & _
"qryFireRenewal.POLICYNO, qryFireRenewal.SISTNAME,
qryFireRenewal.SI_TOTAL, qryFireRenewal.PREM_TOTAL,
qryFireRenewal.EXPIRYDATE" & _
"FROM qryFireRenewal" & _
" WHERE (((qryFireRenewal.EXPIRYDATE)>= " & _
Forms!reportFire!txtStartDate & _
" And (qryFireRenewal.EXPIRYDATE)<= " & _
Forms!reportFire!txtEndDate & _
" ));"""

DoCmd.SetWarnings False
DoCmd.RunSQL sqlrenewal

End Sub
Click to expand...

Dates need to be delimited with # symbols and when pulling the dates from a
form reference you might need to wrap them in the CDate() function. An
entry on a form that is obviously a date to a human user doesn't always get
interpreted as such by Access.
 
V

Van T. Dinh

.... and don't forget spaces when you use concatenation to
construct an SQL String. At present, you have left out
required spaces like:

....DISTINCTqryFireRenewall...

Access / JET won't recognise the keyword DISTINCT in this
case.

HTH
Van T. Dinh
MVP (Access)
 
Top