help me in vba sql

  • Thread starter Thread starter MUHAMAMD SALIM SHAHZAD
  • Start date 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
 
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.
 
.... 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)
 
Back
Top