prob with SQL statement

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

Can anyone tell me what's wrong the the following sql
statement?

strSQL = "UPDATE PDATracking SET
PDATracking.PriceQuoteEffectiveDateEnd = [Forms]!
[frmUpdateDates]![NewDate] WHERE
(((PDATracking.PriceQuoteEffectiveDateEnd) Between [Forms]!
[frmUpdateDates]![DateStart] And [Forms]![frmUpdateDates]!
[DateEnd]) AND ((PDATracking.Brand=[Forms]!
[frmUpdateDates]![cmdBrand])));"

Thank you

Cindy
 
Can anyone tell me what's wrong the the following sql
statement?

strSQL = "UPDATE PDATracking SET
PDATracking.PriceQuoteEffectiveDateEnd = [Forms]!
[frmUpdateDates]![NewDate] WHERE
(((PDATracking.PriceQuoteEffectiveDateEnd) Between [Forms]!
[frmUpdateDates]![DateStart] And [Forms]![frmUpdateDates]!
[DateEnd]) AND ((PDATracking.Brand=[Forms]!
[frmUpdateDates]![cmdBrand])));"

Thank you

Cindy
Click to expand...

Does this help?

strSQL = "UPDATE PDATracking SET
PDATracking.PriceQuoteEffectiveDateEnd = #" & [Forms]!
[frmUpdateDates]![NewDate] & "# WHERE
(((PDATracking.PriceQuoteEffectiveDateEnd) Between #" & [Forms]!
[frmUpdateDates]![DateStart] & "# And #" & [Forms]![frmUpdateDates]!
[DateEnd]) & "# AND ((PDATracking.Brand= " & [Forms]!
[frmUpdateDates]![cmdBrand]) & "));"

You may have to modify the last part of the where clause as I have no
idea as the whether PDATracking.Brand is a Number or a Text Datatype
field.
I've written it as Number datatype.
Change the syntax (if it is text) to:

& "# AND ((PDATracking.Brand= '" &
[Forms]![frmUpdateDates]![cmdBrand]) & "'));"
 
When you create SQL statements on the fly using values from
controls or variables you must not put the controls or
variables inside the quote marks. The statement should be
something like

strSQL = "UPDATE PDATracking SET
PDATracking.PriceQuoteEffectiveDateEnd = " & [Forms]!
[frmUpdateDates]![NewDate] & " WHERE
(((PDATracking.PriceQuoteEffectiveDateEnd) Between #" &
Format([Forms]![frmUpdateDates]![DateStart], "mm/dd/yyyy")
& "# And #" & Format([Forms]![frmUpdateDates]![DateEnd]),
"mm/dd/yyyy") "# AND ((PDATracking.Brand = " & [Forms]!
[frmUpdateDates]![cmdBrand]) & "));"

Note that the Date parameters need to be enclosed in #.
For clarity, I have formatted the dates to mm/dd/yyyy
format as that is the required format for SQL. If
PDSTracking.Brand is a text field, you will need to include
' around the value taken from the form.

The above is untested so you may have to play around with it.

Hope This Helps
Gerald Stanley MCSD
 
Gerald Stanley said:
When you create SQL statements on the fly using values from
controls or variables you must not put the controls or
variables inside the quote marks.
Click to expand...

Just a note: the embedded form/control references *would* work if Cindy
were using DoCmd.RunSQL to execute the statement, rather than the DAO
Execute method. I still prefer the Execute method, because I don't have
to turn off warnings to use it, though as you say it does require that
the form/control references be replaced by the actual values.
 
Back
Top