Integer variable in query 2

[jpl458]

Have following code and can't get it to recognize integer variable (in blue). It presents perfectly in the immediate window. The query is all on one line in the ap. BayCounts.[ID] and intBay are both long integers. The query runs if I replace the variable with a literal number, like 6.

Private Sub Fill50btn_Click()
Dim updateBaySQL As Variant 'For update Query
Dim BayNm As Variant 'Value Passed from BayForm
BayNm = Me.Controls("ShowBay").Value
Dim intBay As Integer
intBay = CLng(Right(BayNm, 2))

updateBaySQL = "UPDATE BayCounts SET BayCounts.StCount = 600, BayCounts.[Count] = 600, BayCounts.AddDate = Date(), BayCounts.AddTime = Time() WHERE BayCounts.ID = intBay"
DoCmd.SetWarnings False
DoCmd.RunSQL updateBaySQL
DoCmd.SetWarnings True

End Sub


Thanks

jpl

 

[SkipVought]

hi,

updateBaySQL = "UPDATE BayCounts SET BayCounts.StCount = 600, BayCounts.[Count] = 600, BayCounts.AddDate = Date(), BayCounts.AddTime = Time() WHERE BayCounts.ID =[b]" &  intBay[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Replace this:
WHERE BayCounts.ID = intBay"
with this:
WHERE BayCounts.ID=" & intBay

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Because the query is text and the integer variable is not.

jpl

 

[SkipVought]

A variable regardless if it is text or not. In actuality the integer is converted to text prior to concatenation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

It presents perfectly in the immediate window
Really ?
I think it showed "... WHERE BayCounts.ID = intBay" instead of "... WHERE BayCounts.ID = theValueOfintBay"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

jpl,
You can always help yourself by doing this:

updateBaySQL = "UPDATE BayCounts SET BayCounts.StCount = 600, BayCounts.[Count] = 600, BayCounts.AddDate = Date(), BayCounts.AddTime = Time() WHERE BayCounts.ID = intBay"
DoCmd.SetWarnings False[red]
Debug.Print updateBaySQL[/red][green] 'show me my SQL[/green]
DoCmd.RunSQL updateBaySQL
DoCmd.SetWarnings True

and see what you have in Immediate Window

Have fun.

---- Andy