Update query: wizard sql to vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have code that I can't get to work so I had the query wizard write it
for me in sql and now I'm trying to convert it to code.

Here's the wizard sql:
UPDATE tblAuctionDonors SET tblAuctionDonors.DateSent = Date()
WHERE (((tblAuctionDonors.AskAgain)=Yes) AND ((tblAuctionDonors.DateSent) Is
Null)) OR (((tblAuctionDonors.AskAgain)=Yes) AND
((tblAuctionDonors.DateSent)<DateSerial(Year(Date()),10,1)));

This sql correctly updates the table.

Here's what I'm trying to convert it to:

strWhere = "(([tblAuctionDonors.AskAgain]=Yes) AND
([tblAuctionDonors.DateSent] Is Null)) OR (([tblAuctionDonors.AskAgain]=Yes)
AND ([tblAuctionDonors.DateSent]<DateSerial(Year(Date()),10,1)))"

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent=Date() where
" & strWhere

This code does nothing and I'm not sure why. Any suggestions? My sql and
coding "skills" are equally poor. I appreciate your help.
 
1. If the Wizard code works, why take the risk of reformatting it? Just
assign all of it to your strSQL variable. Cut and Paste is the bane of
programmers! <g>
2. You do realize that the where condition simplifies to the following:

AskAgain = Yes And (DateSent is null OR
DateSent < DateSerial(Year(Date()), 10, 1))

(Formatted that way to make it readable in the reader.) This is much clearer
and easier to understand.

Good Luck!
 
Thanks for the tips! Turns out that my msgbox was messing things up. But
thanks for helping to streamline my where statement. I appreciate it.


Chaim said:
1. If the Wizard code works, why take the risk of reformatting it? Just
assign all of it to your strSQL variable. Cut and Paste is the bane of
programmers! <g>
2. You do realize that the where condition simplifies to the following:

AskAgain = Yes And (DateSent is null OR
DateSent < DateSerial(Year(Date()), 10, 1))

(Formatted that way to make it readable in the reader.) This is much clearer
and easier to understand.

Good Luck!
--
Chaim


Stephanie said:
Hi. I have code that I can't get to work so I had the query wizard write it
for me in sql and now I'm trying to convert it to code.

Here's the wizard sql:
UPDATE tblAuctionDonors SET tblAuctionDonors.DateSent = Date()
WHERE (((tblAuctionDonors.AskAgain)=Yes) AND ((tblAuctionDonors.DateSent) Is
Null)) OR (((tblAuctionDonors.AskAgain)=Yes) AND
((tblAuctionDonors.DateSent)<DateSerial(Year(Date()),10,1)));

This sql correctly updates the table.

Here's what I'm trying to convert it to:

strWhere = "(([tblAuctionDonors.AskAgain]=Yes) AND
([tblAuctionDonors.DateSent] Is Null)) OR (([tblAuctionDonors.AskAgain]=Yes)
AND ([tblAuctionDonors.DateSent]<DateSerial(Year(Date()),10,1)))"

strSQL = "Update tblAuctionDonors Set tblAuctionDonors.DateSent=Date() where
" & strWhere

This code does nothing and I'm not sure why. Any suggestions? My sql and
coding "skills" are equally poor. I appreciate your help.
Click to expand...
Click to expand...
 
Back
Top