Using combobox to update a field (again)

  • Thread starter Thread starter NHiorns
  • Start date Start date
N

NHiorns

I was given help over the last couple of weeks with the
following script to update a boolean field using a from
and to selection (using a combobox). This now works, with
many thanks to SteveS and Marshall Barton.

However, the customer now wants to do the same on another
database, but on this occasion the StartNum and EndNum
field is now alpha-numeric and get the following error;

Syntax error (missing operator) in query expression ....

Original script below. Using Access 2000.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim tmp As Integer
Dim strSQL As String

'StartNum and EndNum must be numeric
If Not IsNumeric(StartNum) Then
StartNum = ""
StartNum.SetFocus
MsgBox "Enter a number between 1 and 8000"
Exit Sub
End If
If Not IsNumeric(EndNum) Then
EndNum = ""
EndNum.SetFocus
MsgBox "Enter a number between 1 and 8000"
Exit Sub
End If

' EndNum must be greater than StartNum
If EndNum < StartNum Then
tmp = EndNum
EndNum = StartNum
StartNum = tmp
End If

'set all records to false
'comment out the next line if you don't want to clear
previous selections
CurrentDb.Execute "UPDATE Table2 SET Table2.Selected =
False;"

'now set record that are between Start and End to true
strSQL = "UPDATE Labels SET Labels.Selected = -1 "
strSQL = strSQL & " WHERE Labels.ID >= " & [Forms]!
[Label]![StartNum]
strSQL = strSQL & " And Labels.ID <= " & [Forms]!
[Label]![EndNum] & ";"


'now do it!!
CurrentDb.Execute strSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
 
NHiorns said:
I was given help over the last couple of weeks with the
following script to update a boolean field using a from
and to selection (using a combobox). This now works, with
many thanks to SteveS and Marshall Barton.

However, the customer now wants to do the same on another
database, but on this occasion the StartNum and EndNum
field is now alpha-numeric and get the following error;

Syntax error (missing operator) in query expression ....

Original script below. Using Access 2000. [snip]
'now set record that are between Start and End to true
strSQL = "UPDATE Labels SET Labels.Selected = -1 "
strSQL = strSQL & " WHERE Labels.ID >= " & [Forms]!
[Label]![StartNum]
strSQL = strSQL & " And Labels.ID <= " & [Forms]!
[Label]![EndNum] & ";"
Click to expand...
[snip]

When searching a Text field, you must enclose the value in
quotes:

strSQL = strSQL & " WHERE Labels.ID >= """ &
[Forms]![Label]![StartNum] & """"
strSQL = strSQL & " And Labels.ID <= """ &
[Forms]![Label]![EndNum] & """;"

The trick to understanding all those quotes is that when you
want a quote in the result string, you have to use two
quotes inside the quotes around the whole string. After the
concatenation takes place, the above will result in

WHERE Labels.ID >= "abc" And Labels.ID <= "pqr"
 
Back
Top