Punctuation Problem 2

[jpl458]

Have a small loop:

For z = 1 To 40
udq = "UPDATE BallsAdd SET BallsAdd.[Count] = For z = 1 To 40
udq = "UPDATE BallsAdd SET BallsAdd.[Count] = rsdbo.Fields(1).Value "
udq = udq + "WHERE (((BallsAdd.ID)=5))"
DoCmd.SetWarnings False
DoCmd.RunSQL udq
DoCmd.SetWarnings True
rsdbo.MoveNext
Next z"
There is an error with "." (!) or"() in rsdbo.Fields(1).Value.

I can't find it

The recordset rsdbo has all the data in it, and I will be changing the where clause when I get past this

Thanks in advance

jpl

 

[PHV]

udq = "UPDATE BallsAdd SET [Count]=" & rsdbo.Fields(1).Value & " WHERE ID=5"

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

 

[jpl458]

I got it to wprk with:

udq = "UPDATE BallsAdd SET BallsAdd.[Count] = 'rsdbo.Fields(1).Value' "
udq = udq + "WHERE (((BallsAdd.ID)= 'Right(rsdbo.Fields(0).Value, 2')))"

Adding ' ' around 'rsdbo.Fields(1).Value'

But now in the where clause "WHERE (((BallsAdd.ID)= 'Right(rsdbo.Fields(0).Value, 2')))" I get datatype mismatch

The field BallsAdd.ID is an autonumber (Long Int) and 'Right(rsdbo.Fields(0).Value, 2' is text. But I can't seem to convert the text to Integer. I've tried CLng('Right(rsdbo.Fields(0).Value, 2') and stll get type mismatch. tried CInt as well.

udq = "UPDATE BallsAdd SET BallsAdd.[Count] = 'rsdbo.Fields(1).Value' "
udq = udq + "WHERE (((BallsAdd.ID)= Clng('Right(rsdbo.Fields(0).Value, 2'))))

Thanks for the quick response

jpl

 

[Andrzejek]

What does it say when you do:

DoCmd.SetWarnings False[red]
Debug.Print udq[/red]
DoCmd.RunSQL udq

Have fun.

---- Andy

 

[jpl458]

The debug produces

UPDATE BallsAdd SET BallsAdd.[Count] = 'rsdbo.Fields(1).Value' WHERE (((BallsAdd.ID)= CLng('Right(rsdbo.Fields(0).Value, 2')))

jpl

 

[Andrzejek]

Thst's the 'beauty' of Access VBA...

Try this:

udq = "UPDATE BallsAdd SET BallsAdd.[Count] = " & rsdbo.Fields(1).Value
udq = udq + " WHERE BallsAdd.ID = " & Clng(Right(rsdbo.Fields(0).Value, 2))
Debug.Print udq

Have fun.

---- Andy

 

[PHV]

What about this ?
udq = "UPDATE BallsAdd SET [Count]=" & rsdbo.Fields(1).Value & " WHERE ID=" & rsdbo.Fields(0).Value

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

 

[jpl458]

That works! But I don't know why. The field in the table (ID) is a number (Long Int) and the field in the recordset Right(rsdbo.Fields(0).Value, 2) is text.
Also I built the original query in the QBE Grid, but your is much simpler. Is there a good souce to read to get more familiar with building these expressions?
Thanks PHV, really helped.

 

[Andrzejek]

So which one 'works'?

Mine with:[tt]
[blue] Clng(Right([/blue]rsdbo.Fields(0).Value, 2[blue]))[/blue]
[/tt]

or PHV's[tt]
rsdbo.Fields(0).Value
[/tt]

That all depends what you have in this field [rsdbo.Fields(0).Value] and what you are trying to retrieve from it.

Have fun.

---- Andy

 

[Andrzejek]

Is there a good souce to read to get more familiar with building these expressions?"

You can get these queries from Access, and modifications to include variables are not that difficult.

Let's say you have some variables:[tt]
intID = 123
strFrstName = "John"
strDOB = "5/5/2005"[/tt]

And a simple SQL with hard-coded values:
[tt]
strSQL = "Select * From SomeTable "
strSQL = strSQL & " Where ID = [blue]123[/blue] "
strSQL = strSQL & " And FName = '[blue]John[/blue]' "
strSQL = strSQL & " And DOB = #[blue]5/5/2005[/blue]#"

Debug.Print strSQL
[/tt]
You do know that all strings in VBA start and end with [red]"[/red] (double-quotes) and concantination is done by [red]&[/red] sign.
In SQL, numbers are simple, strings start and end with ' (single quote), and dates start and end with # sign.

Now you want to replace all [blue]BLUE[/blue] hard-coded values in your SQL with variables. Make sure all non-blue code stays untouched:
[tt]
strSQL = "Select * From SomeTable "
strSQL = strSQL & " Where ID = [red]" &[/red] [blue]intID[/blue] [red]& "[/red] "
strSQL = strSQL & " And FName = '[red]" &[/red] [blue]strFrstName[/blue] & [red]"[/red]' "
strSQL = strSQL & " And DOB = #[red]" &[/red] [blue]strDOB[/blue] [red]& "[/red]#"

Debug.Print strSQL
[/tt]

I included the statement [tt]Debug.Print[/tt] because that's your friend, you can see what you send to your DB, and you can see any mistakes you may make.

Have fun.

---- Andy

 

[strongm]

>dates start and end with #

And in VBA ...

msgbox #dec 1 1975#

 

[jpl458]

Thanks Andy. That helps a lot

jpl458