SQL Where syntax help

[nmapeso]

HEllo Again, WOuld like to thank everyone who have answer some of my problem int access previously. But right now I' again stuck with this syntax on SQL Where(Criteria).

Im trying to update a database maintblBankruptcy DateEsent to Date() when DateESent is Null and Relationship = a textBox[Division] entered by user and DateRecieved = Between a textBoxstartDate[Date1] and a textBoxEndDate[EndDate]
Don't get any error. Some database are updated base on the where conditions and some are not. I guessing my syntax is wrong.

Dim SQL As String

SQL = "UPDATE maintblBankruptcy SET DateESent = Date()" & _
"WHERE DateESent is Null AND [Relationship] = '" & Me![Division] & "' [RecievedDate] Between #" & Me![Date1] & "# and #" & Me![EndDate] & "#"
DoCmd.SetWarnings (False)
DoCmd.RunSQL SQL

 

[stix4t2]

you are missing an AND between Division and Recieved Date,

WHERE DateESent is Null AND [Relationship] = '" & Me![Division] & "' AND [RecievedDate] Between #" & Me![Date1] & "# and #" & Me![EndDate] & "#

 

[nmapeso]

Hi stix4t2,

I try the your suggestion but for some reason it update All DateESent to Date() even if the RecievedDate between 01/01/2006 to 31/01/2006.

Ex. (DD/MM/YYYY) (DD/MM/YYYY)
Relatsionship RecievedDate DateEsent
Agents 02/01/2006 03/03/2006
Agents 10/01/2006 03/03/2006
Agents 15/02/2006 03/03/2006

Any suggestions?

 

[nmapeso]

Hi stix4t2,

I try the your suggestion but for some reason it update All DateESent to Date() even if the RecievedDate between 01/01/2006 to 31/01/2006.

Ex. (DD/MM/YYYY) (DD/MM/YYYY)
Relatsionship RecievedDate DateEsent
Agents 02/01/2006 03/03/2006
Agents 10/01/2006 03/03/2006
Agents 15/02/2006 03/03/2006 this one should not been update.

Any suggestions?

 

[PHV]

WHERE DateESent is Null AND Relationship='" & Me!Division & "' AND RecievedDate Between #" & Format(Me!Date1, "yyyy-mm-dd") & "# And #" & Format(Me!EndDate, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

You said no syntax error but, do you need a space before WHERE?
SQL = "UPDATE maintblBankruptcy SET DateESent = Date()" & _
" WHERE DateESent ...

try brackets around your "And" criteria?