SQL Statement

[Coldsteel]

Hi all, I am in the process of learning SQL and I am trying to automate a update query in SQL. I keep getting a invaild date error on my statment.
Any help would be greatly appreciated
Here is what I have so far:

UPDATE tbl_referral INNER JOIN [Product Specialist Payouts Table] ON tbl_referral.[Product 1 Referred] = [Product Specialist Payouts Table].[Product 1 Referred]

SET tbl_referral.[PS Payout] = [Product Specialist Payouts Table].Payout

WHERE (((tbl_referral.STATUS)="CLOSED - FUNDED") AND ((tbl_referral.[DATE REFERRED])=#DateSerial(Year(Date()),Month(Date()),1) And (tbl_referral.[DATE REFERRED])=Now()))#;

Thanks

 

[HiTechCoach]

Hi all, I am in the process of learning SQL and I am trying to automate a update query in SQL. I keep getting a invaild date error on my statment.
Any help would be greatly appreciated
Here is what I have so far:

UPDATE tbl_referral INNER JOIN [Product Specialist Payouts Table] ON tbl_referral.[Product 1 Referred] = [Product Specialist Payouts Table].[Product 1 Referred]

SET tbl_referral.[PS Payout] = [Product Specialist Payouts Table].Payout

WHERE (((tbl_referral.STATUS)="CLOSED - FUNDED") AND ((tbl_referral.[DATE REFERRED])=#DateSerial(Year(Date()),Month(Date()),1) And (tbl_referral.[DATE REFERRED])=Now()))#;

Thanks

dateSearil() already returns a date data type. You use the # to wraopt a text string so that it will be converted to a date data type. You do not use both together.


Try this syntax:

WHERE (((tbl_referral.STATUS)="CLOSED - FUNDED") AND ((tbl_referral.[DATE REFERRED])= DateSerial(Year(Date()),Month(Date()),1) And (tbl_referral.[DATE REFERRED])=Now()));

According to your logic: you are looking of the [DATE REFERRED] that is at exactly midnight on the first day of the month when it actually happens. Your SQL will work at exactly midnight when the month changes if run at exactly midnight on the PC's clock.

What are you trying to do?

 

[HiTechCoach]

After reading you post a few more time, maybe you are looking fro all the referrals for the current month.

Does [DATE REFERRED] also include the time?

If not, Try:

tbl_referral.[DATE REFERRED] between DateSerial(Year(Date()),Month(Date()),1) and Date()

 

[Coldsteel]

Thanks for the help.
Yeah i am trying to update only the current month info and [DATE REFERRED] does not include the time. I tried the update but I am not getting anything in the update query, I think I missed something.

WHERE (((tbl_referral.STATUS)="CLOSED - FUNDED") AND ((tbl_referral.[DATE REFERRED]) Between DateSerial(Year(Date()),Month(Date()),1) And Date()));

 

[DCrake]

My approach would be to declare two date type variables prior to contrusting the sql

Dim DateLower As Date
Dim DateUpper As Date

DateUpper = Date()
DateLower = CDate("01/" & Month(Date()) & "/" & Year(Date()))

Then in your query sql


Code Snippet

AND [DATE REFERRED] Between #" & DateLower & "# And #" & DateUpper & "#"

I have included as link to a document on using public variable that may be of interest to you.


Link

David

 

[Coldsteel]

Sorry for the late reply, You are a genius it works beautifully!!!

Thanks, ALL